Search content within the blog

Wednesday, January 4, 2012

Passing table valued parameters in SQL Server 2008

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

Considerations

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.

No comments:

Post a Comment