I have always wanted to be able to pass table variables to stored procedures. If a variable is able to be declared, it should have the functionality to be passed as necessary.
I was thrilled to learn that SQL Server 2008 offers this functionality. Here are instructions on how to pass table variables (and the data in them) into stored procedures and functions.
Why pass tables as parameters?
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.
How to pass table parameters
I need to set up my SalesHistory table, which holds my product sales. The following script will create the table in the database of your choice:
IF OBJECT_ID('SalesHistory')>0 DROP TABLE SalesHistory; GO CREATE TABLE [dbo].[SalesHistory] ( [SaleID] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY, [Product] [varchar](10) NULL, [SaleDate] [datetime] NULL, [SalePrice] [money] NULL ) GO
The first 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 SalesHistoryTableType table type definition:
CREATE TYPE SalesHistoryTableType AS TABLE ( [Product] [varchar](10) NULL, [SaleDate] [datetime] NULL, [SalePrice] [money] NULL ) GO
If you want to view other types of table type definitions in your system, you can execute the following query, which looks in the system catalog:
SELECT * FROM sys.table_types
I want to 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 SalesHistoryTableType, and loads the SalesHistory with the records in the table parameter with a value of ‘BigScreen’ in the Product column.
CREATE PROCEDURE usp_InsertBigScreenProducts ( @TableVariable SalesHistoryTableType READONLY ) AS BEGIN INSERT INTO SalesHistory ( Product, SaleDate, SalePrice ) SELECT Product, SaleDate, SalePrice FROM @TableVariable WHERE Product = 'BigScreen' END GO
You can use the table variable being passed in as any other table for querying data.
Limitations to passing table parameters
You must use the READONLY clause when passing in the table valued variable into the procedure. Data in the table variable cannot be modified — you can use the data in the table for any other operation. Also, you cannot use table variables as OUTPUT parameters — you can only use table variables as input parameters.
Putting my new table variable type to use
First, I must declare a variable of type SalesHistoryTableType. I don’t need to define the structure of the table again because it was defined when I created the type.
DECLARE @DataTable AS SalesHistoryTableType
The following script adds 1,000 records into my @DataTable table variable:
DECLARE @i SMALLINT SET @i = 1 WHILE (@i <=1000) BEGIN INSERT INTO @DataTable(Product, SaleDate, SalePrice) VALUES ('Computer', DATEADD(mm, @i, '3/11/1919'), DATEPART(ms, GETDATE()) + (@i + 57)) INSERT INTO @DataTable(Product, SaleDate, SalePrice) VALUES('BigScreen', DATEADD(mm, @i, '3/11/1927'), DATEPART(ms, GETDATE()) + (@i + 13)) INSERT INTO @DataTable(Product, SaleDate, SalePrice) VALUES('PoolTable', DATEADD(mm, @i, '3/11/1908'), DATEPART(ms, GETDATE()) + (@i + 29)) SET @i = @i + 1 END
Once I have data loaded into my table variable, I can pass the structure to my stored procedure. (Make sure you add the data to the table variable and pass the table to the procedure all in the same batch. Table variables go out of scope as soon as the procedure or batch returns.)
Note: When table variables are passed as parameters, the table is materialized in the tempdb system database rather than passing the entire data set in memory; this makes handling large amounts of data rather efficient. All server side passing of table variable parameters are passed by reference, using the reference as a pointer to the table in the tempdb.
EXECUTE usp_InsertBigScreenProducts @TableVariable = @DataTable
To see if my procedure performed the way I expect, I’ll run this query to see if the records were inserted into the SalesHistory table:
SELECT * FROM SalesHistory
SQL Server 2008’s table parameter feature is a huge step forward in terms of development and potentially performance. The benefits to this feature are that it can: reduce server round trips, use table constraints, and extend the functionality of programming on the database engine.
There are some limitations to keep in mind, such as not being able to alter the data in the parameter and not being able to use the variable as output. Despite these minor drawbacks, I definitely recommend exploring the table parameter feature when SQL Server 2008 comes out.
Tim Chapman is a SQL Server database administrator who works for a bank in Louisville, KY, and has more than 7 years of IT experience. He is also Microsoft certified in SQL Server 2000 and SQL Server 2005. If you would like to contact Tim, please e-mail him at firstname.lastname@example.org.
Get SQL tips in your inbox
TechRepublic’s free SQL Server newsletter, delivered each Tuesday, contains hands-on tips that will help you become more adept with this powerful relational database management system. Automatically subscribe today!