Table Value Parameter in SQL Server 2008

Introduction

One of the fantastic new features of SQL Server 2008 is the Table value parameter. In previous versions of SQL Server, there wasn’t a native way to pass a table to a Stored Procedure or functions. The usual workaround was to pass a large varchar or the XML type and parse through it. Now, in SQL Server 2008, Table parameters are available.

Many a times, we need to utilize a single Stored Procedure to update multiple database tables with one-to-many relationships. In such occasions, we end up concatenating large strings, and inside the procedure, end up parsing the string to get records. With SQL Server 2008, it is very simple, and we no more need to write tons of lines of code to implement such requirements.

Facts about Table Value Parameters

Here are a few facts that you must know before starting to use the Table Value Parameter:

1. Helps address the need to pass an “array” of elements to a Stored Procedure / function.
2. Table-valued parameters are used to send multiple rows of data to a Transact-SQL statement or a routine, such as a Stored Procedure or function, without creating a temporary table or many parameters.
3. Overcomes both security and performance issues in a dynamic SQL approach. Internally treated like a table variable.
4. Scope is batch.
5. Not affected by rollback (beyond the atomic statement scope).
6. No histograms/distribution statistics.
7. When parameter value not provided, defaults to empty table.

Example

CREATE TYPE dbo.OrderIDs AS TABLE 
( 
  pos INT NOT NULL PRIMARY KEY,
  orderid INT NOT NULL UNIQUE
)

DECLARE @T AS dbo.OrderIDs;

INSERT INTO @T(pos, orderid) VALUES(1, 10248)
INSERT INTO @T(pos, orderid) VALUES(2, 10250)
INSERT INTO @T(pos, orderid) VALUES(3, 10249);

SELECT * FROM @T;

In the above code snippet, we create a Table User Defined Type. In SQL Server 2008, we can define Table UDTs (this is a new feature in SQL Server 2008). Then, create a variable using the newly created UDT and insert a few records into the variable and query the same.

CREATE PROC dbo.sp_GetOrders
(@T AS dbo.tbl_Order READONLY)
AS
SELECT O.OrderID, O.OrderDate, O.CustomerID
FROM dbo.tbl_Order AS O JOIN @T AS T ON 
    O.OrderID = T. OrderID
ORDER BY T. RecordID;
GO

DECLARE @MyOrderIDs AS dbo.tbl_Order;
INSERT INTO @MyOrderIDs(pos, orderid) VALUES(1, 10248)
INSERT INTO @MyOrderIDs(pos, orderid) VALUES(2, 10250)
INSERT INTO @MyOrderIDs(pos, orderid) VALUES(3, 10249);

EXEC dbo.sp_GetOrders @T = @MyOrderIDs;

The above code snippet shows how to use the Table UDT as a parameter in SQL Stored Procedure.

Conclusion

So now, with the Table UDT and the ability to pass the table value parameter to Stored Procedures and functions, we need lesser code and also get performance benefits.

Hope you enjoyed this article. Happy programming!!!