User Defined Table Types And Table Valued Parameters

In this article you will learn about User Defined Table Types and Table-Valued Parameters. The concepts of User-Defined Table Types (UDTTs) and Table-Valued Parameters (TVPs) were introduced in SQL Server 2008.

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
  1. CREATE TYPE UT_Employee AS TABLE  
  2. (  
  3. Emp_Id int NOT NULL,  
  4. EmployeeName nvarchar(MAX),  
  5. EmpSalary varchar(50),  
  6. StateId varchar(50),  
  7. CityId varchar(50)  
  8. )  
code

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

User-Defined Tables

Now we declare a stored procedure that has a parameter of table type.
  1. CREATE PROCEDURE USP_Insert_Employee_Infi(@Employee_Details [UT_Employee])  
  2. AS  
  3. BEGIN  
  4.   
  5. INSERT INTO dbo.Employee  
  6. (  
  7. Emp_Id,  
  8. EmployeeName,  
  9. EmpSalary,  
  10. StateId,  
  11. CityId  
  12. )  
  13. SELECT * FROM @Employee_Details  
  14. END  
Output

output

 
When we try to execute the 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 the 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.
  1. ALTER PROCEDURE USP_Delete_Employee_Info(@Employee_Details [UT_Employee1] ReadOnly)  
  2. AS  
  3. BEGIN  
  4.   
  5. DELETE FROM dbo.Employee WHERE dbo.Employee.Emp_Id IN(SELECT Tab.Emp_Id FROM @Employee_Details Tab);  
  6.   
  7. 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 can'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.

Read more articles on SQL Server: