Click here to Skip to main content
11,706,142 members (53,927 online)
Click here to Skip to main content

Table Value Parameter in SQL Server 2008

, 10 Jun 2009 CPOL 53.1K 21
Rate this:
Please Sign up or sign in to vote.
MS SQL Server 2008 new feature, Table Value Parameter.


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.



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);


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)
SELECT O.OrderID, O.OrderDate, O.CustomerID
FROM dbo.tbl_Order AS O JOIN @T AS T ON 
    O.OrderID = T. OrderID

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.


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


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


About the Author

You may also be interested in...

Comments and Discussions

GeneralHave a look at this article as well. you may like it. Pin
iamonweb11-Jul-13 0:26
memberiamonweb11-Jul-13 0:26 
Questiontvp is possible to work with while loop Pin
prassin5-Mar-13 22:44
memberprassin5-Mar-13 22:44 
QuestionTable-Value-Parameter-in-SQL-Server-2008 Pin
Eiphyo903-Feb-12 22:50
memberEiphyo903-Feb-12 22:50 
GeneralVery Helpful Pin
SaswatiS26-Aug-09 17:39
memberSaswatiS26-Aug-09 17:39 
GeneralRe: Very Helpful Pin
Robin_Roy26-Aug-09 21:13
memberRobin_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 | Terms of Use | Mobile
Web03 | 2.8.150819.1 | Last Updated 11 Jun 2009
Article Copyright 2009 by Robin_Roy
Everything else Copyright © CodeProject, 1999-2015
Layout: fixed | fluid