Click here to Skip to main content
Click here to Skip to main content

Table Value Parameter in SQL Server 2008

By , 10 Jun 2009
Rate this:
Please Sign up or sign in to vote.

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!!!

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

About the Author

Robin_Roy
Other Brilliance Information Sdn Bhd
Malaysia Malaysia
Working as a Senior Consultant with Brilliance MSC, Malaysia.
Love to evaluate new technologies and implement the same.
Believe in sharing knowledge.

Comments and Discussions

 
GeneralHave a look at this article as well. you may like it. Pinmemberiamonweb11-Jul-13 0:26 
Questiontvp is possible to work with while loop Pinmemberprassin5-Mar-13 22:44 
QuestionTable-Value-Parameter-in-SQL-Server-2008 PinmemberEiphyo903-Feb-12 22:50 
GeneralVery Helpful PinmemberSaswatiS26-Aug-09 17:39 
GeneralRe: Very Helpful PinmemberRobin_Roy26-Aug-09 21:13 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Mobile
Web01 | 2.8.140421.2 | Last Updated 11 Jun 2009
Article Copyright 2009 by Robin_Roy
Everything else Copyright © CodeProject, 1999-2014
Terms of Use
Layout: fixed | fluid