How do you use the CREATE TYPE statement to create a table type for table-valued parameters?
SamPetr
Posted by SamPetr
Last Updated: June 22, 2024
SQL TableType
In SQL Server, you can use the CREATE TYPE statement to define a user-defined table type, which can then be used for table-valued parameters in stored procedures and functions. Here's how to create a table type step by step:
Step 1: Define the Table Type
You'll typically define a table type using the CREATE TYPE statement followed by the type name and the structure of the table, including the columns and their data types. Here’s the syntax: 
CREATE TYPE TypeName AS TABLE
(
    Column1 DataType1 [NULL | NOT NULL],
    Column2 DataType2 [NULL | NOT NULL],
    -- You can add more columns as needed
);
Example:
Let's say you want to create a table type called EmployeeTableType that consists of three columns: EmployeeID (an integer), EmployeeName (a string), and HireDate (a date). Here’s how you would do that: 
CREATE TYPE EmployeeTableType AS TABLE
(
    EmployeeID INT NOT NULL,
    EmployeeName NVARCHAR(100) NOT NULL,
    HireDate DATE NULL
);
Step 2: Use the Table Type in a Stored Procedure
Once you have created the table type, you can use it as a parameter in a stored procedure. Below is an example of a stored procedure that takes a parameter of the type EmployeeTableType. 
CREATE PROCEDURE InsertEmployees
    @Employees EmployeeTableType READONLY
AS
BEGIN
    -- Example insert from the table-valued parameter into a target table
    INSERT INTO Employee (EmployeeID, EmployeeName, HireDate)
    SELECT EmployeeID, EmployeeName, HireDate 
    FROM @Employees;
END;
Step 3: Execute the Stored Procedure with the Table-Valued Parameter
To call the stored procedure with the table-valued parameter, you need to declare a variable of the defined table type, insert data into it, and then pass it to the stored procedure. Here’s an example: 
DECLARE @MyEmployees EmployeeTableType;

INSERT INTO @MyEmployees (EmployeeID, EmployeeName, HireDate)
VALUES (1, 'John Doe', '2023-01-01'),
       (2, 'Jane Smith', '2023-01-02');

EXEC InsertEmployees @MyEmployees;
Summary
1. Use CREATE TYPE to define a new table type. 2. Create a stored procedure that accepts the table type as a parameter. 3. Declare a variable of the newly created table type, insert data into it, and call the stored procedure. This is how you can effectively use the CREATE TYPE statement to create a table type for table-valued parameters in SQL Server.
Default Image