Click here to Skip to main content
15,867,488 members
Articles / Database Development / SQL Server / SQL Server 2008

Table Value Parameter in SQL Server 2008

Rate me:
Please Sign up or sign in to vote.
4.75/5 (7 votes)
10 Jun 2009CPOL2 min read 69.9K   20   5
MS SQL Server 2008 new feature, Table Value Parameter.

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

SQL
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.

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



Comments and Discussions

 
GeneralHave a look at this article as well. you may like it. Pin
iamonweb11-Jul-13 0:26
iamonweb11-Jul-13 0:26 
Questiontvp is possible to work with while loop Pin
prassin5-Mar-13 22:44
prassin5-Mar-13 22:44 
hi bru!!

i have a requirement that i need to assign while loop on my TVP..is that possible.. if it okay then please reply to this question... seeking your replay ,,,


Thanks and regards

Prassin
QuestionTable-Value-Parameter-in-SQL-Server-2008 Pin
Eiphyo903-Feb-12 22:50
Eiphyo903-Feb-12 22:50 
GeneralVery Helpful Pin
SaswatiS26-Aug-09 17:39
SaswatiS26-Aug-09 17:39 
GeneralRe: Very Helpful Pin
Robin_Roy26-Aug-09 21:13
Robin_Roy26-Aug-09 21:13 

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

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