Table Variables as Stored Procedure Input
To end this series on Table Variables I would like to review table variables used as input to stored procedures. This is a capability added with SQL Server 2008. It fills the gap in the SQL syntax allowing for stored procedures to accept one or more sets as input to a stored procedure.
In order to pass a table to a stored procedure as input, first you must define a user defined table type in the database. Second, you instantiate a variable of the user defined table type, and populate it with the desired data. Inside the stored procedure you can use the data passed to the stored procedure as you would any other table.
You can populate the temp table variable through ADO.Net which resolves one of the biggest issues developers have had when working with SQL: how to send a set of data as input to a stored procedure from your application.
You can also pass data between stored procedures through by passing a table parameter as input. Previous to this capability the only way to pass a table between stored procedures was through a table created in tempdb.
Temp Table variables have proven to be quite useful structures, enhancing the capabilities of TSQL and simplifying the work of the SQL Server developer.
Cheers,
Ben
$$SWYNK$$
Featured White Paper(s)
Ten Tips for Optimizing SQL Server Performance
read more)
Featured Script
dba3_sp_CalcSpace
based on CalcSpace stored procedure made by Sharon Dooley, 1999-04-11…. (read more)