- Community Pick
When we have to pass multiple rows of data to SQL Server, the developers either have to send one row at a time or come up with other workarounds to meet requirements like using XML to pass data, which is complex and tedious to use. There is a SQLBulkCopy object available in .NET to send multiple rows of data to SQL Server at once, but this still cannot be passed to a stored procedure. SQL Server 2008 provides a new feature called "Table-Valued Parameters". This provides us the ability to easily pass a table to a stored procedure from T-SQL code or from an application as a parameter.
Table-Valued Parameters
SQL Server 2008 Provides a New Feature Called Table-Valued Parameters
This provides us to easily pass a table to a stored procedure from T-SQL code or from an application as a parameter.
We first need to create a user defined type
Database Node > Programmability > Types > User-Defined Table Types
Script to create a User-Defined Table type
Using the Variable as a Parameter
The benefit of User-Defined Table Type is that it can be passed to a stored procedure. Below is an example of using the newly created type with stored procedure using the user defined table type in Stored Procedure
In order to use the user defined type user must have execute permission on the type. if user doesn't have execute permission on the same,it can be granted using below statement: