|
Category: SQL Server
view more software Tips and Tricks
Updated: 08/27/2009 13:08 PM
Author: Shiju Mathews Status: Resolved. |
|
Over the years, I have run into hundreds of situations where it was necessary to pass a container of values rather than individual values into a stored procedure. In most programming languages, passing container data structures in and out of routines is not only common but absolutely necessary. TSQL is no different, especially since the database is where the data lives. SQL Server 2000 made this possible with the use of OPENXML, which allows you to store data into a VARCHAR data type and pass it around. This became even easier in SQL Server 2005 with the advent of the XML data type and XQuery. You still need to construct and shred the XML data before you can use it; this is functional but not simple. SQL Server 2008 introduces the ability to pass a table data type into stored procedures and functions. The table parameter feature can greatly ease the development process because you no longer need to worry about constructing and parsing XML data. The feature also allows the client-side developer to pass data tables from client-side code to the database as a simple parameter. First we need a table for holding data. Use the following script to create a Customer table. The next step in setting up the use of table valued parameters is creating a specific table type; this is necessary so that the structure of the table is defined in the database engine. This allows you to define the type of table and reuse it as needed in your procedure code. This code creates the EmployeeTableType table type definition: Then define the stored procedure that I plan on using to handle my table valued parameter. The following procedure accepts a table valued parameter, which is of the specific ‘EmployeeTableType’. How to test. Use the following script to test the stored procedure. You can use the table variable being passed in as any other table for querying data. To call the table valued Stored procedure using c# refer How to call a stored procedure with table valued parameters in c# |