User Defined Table Types And Table Valued Parameters

Article

The concepts of User-Defined Table Types (UDTTs) and Table-Valued Parameters (TVPs were introduced in SQL Server 2008. Before SQL Server 2008, it was not possible to pass a table variable in stored procedure as a parameter, after SQL Server now we can pass Table-Valued Parameter to send multiple rows of data to a stored procedure or a function without creating a temporary table or passing so many parameters.

Before passing the table variable , first we need to create a user defined table variable. So now we create a user defined table type.

We create the user defined table type for Employee table and structure of Employee table in the following way.

table
output
Create User Define Table Type:

CREATE TYPE UT_Employee AS TABLE
(
Emp_Id int NOT NULL,
EmployeeName nvarchar(MAX),
EmpSalary varchar(50),
StateId varchar(50),
CityId varchar(50)
)

Now if we check the User-Defined Tables Types we find that UT_Employee has been created.

Now we declare a stored procedure that has a parameter of table type.

Stored Procedure:

CREATE PROCEDURE USP_Insert_Employee_Infi(@Employee_Details [UT_Employee])
AS
BEGIN
INSERT INTO dbo.Employee
(
Emp_Id,
EmployeeName,
EmpSalary,
StateId,
CityId
)
SELECT * FROM @Employee_Details
END

Output:

When we try to executethe above query then we get the error of Read Only option, that means Table-valued parameters must be passed as READONLY parameters and we can’t also perform DML operations like UPDATE, DELETE, or INSERT on a table-valued parameter in the body of a routine.

code

Now we create a table type variable and pass this variable to stored procedure as user defined table type parameters.

code

In the above example we created @Tab table type variable and use the UT_Employee as reference. We inserted some values into the variable and at last we passed this variable to stored procedure that executed successfully . Now we check the data of Employee table.

values

Update Data:

In previous example we show how to insert data into table using user define table type, now we learn how to update the records using user defined table types. Firstly, we create a procedure that updates the record.

updates

Now we create a table type variable and pass this variable to stored procedure as user defined table type parameter.

code

After execution of the stored procedure, let us check the data of Employee tables.

tables

Delete Data:

Now we create a stored procedure that accepts a user defined table type and delete the data from table using this user defined table type.

Stored Procedure:

ALTER PROCEDURE USP_Delete_Employee_Info(@Employee_Details [UT_Employee1] ReadOnly)
AS
BEGIN
DELETE FROM dbo.Employee WHERE dbo.Employee.Emp_Id IN(SELECT Tab.Emp_Id FROM @Employee_Details Tab);
END

Query:

query

After successful execution of above query now we check the content of Employee table.

table

Limitation of Table Valued Parameter:

Table valued Parameter can’t be used in CLR user defined function.
SQL Server does not maintain statistics on the Table Valued Parameter Columns.
Table Valued Parameters are ReadOnly in nature, so we cam’t update, insert and delete data into Table Value Parameter.
We cannot use ALTER TABLE statements to modify the design of table-valued parameters. The only single way to change the table definition is to drop the type first and recreate the table type again.
Table Valued Parameter can’t used as OUTPUT parameter in stored procedures.

Thanks for reading the article.