Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

SQL Server 2008: Table-valued parameters

A new feature of SQL Server 2008 is Table-valued parameters (TVP).  This feature will allow Developers and DBAs to pass tables as parameters to stored procedures.  You cannot pass a variable table or temp table, you can only pass a Table Type, which is an alias data type or a user-defined type.  So how do you use it?  The first step is to create  a Table Type.  See the following script:

 

USE AdventureWorks

GO

IF EXISTS (SELECT * FROM sys.types WHERE name = 'CountryCodes' AND schema_id = SCHEMA_ID('Sales'))

DROP TYPE Sales.CountryCodes

GO

CREATE TYPE Sales.CountryCodes

AS TABLE

(

CountryRegionCode nvarchar(3)

)

go

 

The next step is to create a Stored Procedure that will include a variable of the aforementioned Table Type. The following Stored Procedure uses the AdventureWorks2008 database to select Sales by Date and Country Region Code, using the TVP to limit the result to specified Country Region Codes:

IF(OBJECT_ID('Sales.GetSalesByDateAndRegion')) IS NOT NULL

DROP PROC Sales.GetSalesByDateAndRegion

GO

CREATE PROC Sales.GetSalesByDateAndRegion

@Month varchar(20),

@Year int,

@CountryRegions Sales.CountryCodes readonly

AS

SET NOCOUNT OFF

SELECT

cr.Name,

SUM(TotalDue) TotalDue

FROM Sales.SalesOrderHeader sod

INNER JOIN Sales.SalesTerritory st

ON sod.TerritoryID = st.TerritoryID

INNER JOIN Person.CountryRegion cr

ON st.CountryRegionCode = cr.CountryRegionCode

INNER JOIN @CountryRegions c

ON cr.CountryRegionCode = c.CountryRegionCode

WHERE

DATENAME(MONTH,sod.OrderDate) = @Month AND

YEAR(sod.OrderDate) = @Year

GROUP BY

cr.Name,

DATENAME(MONTH,sod.OrderDate),

YEAR(sod.OrderDate)

SET NOCOUNT ON

GO

 

 

In the variable declaration of in the above stored procedure, the last variable is declared as the Table Type (Sales.CountryCodes) created in the first script.  Then the Table-valued Parameter (TVP) is used in the last JOIN of the query to limit the result to the items or Country Region Codes contained with the TVP.  On thing to be aware of is that the Table Type is read only.  The contents of the table cannot be modified.  Now that all of the formalities are out of the way, how do you use this in T-SQL?  The following example is a script of how to call a stored procedure that has a TVP as a parameter:

DECLARE

@Month int,

@Year int,

@CountryRegions Sales.CountryCodes

SELECT

@Month = 4,

@Year = 2002

INSERT INTO @CountryRegions

VALUES('US'), ('CA')

EXEC Sales.GetSalesByDateAndRegion

@Month,

@Year,

@CountryRegions

 

As you can see in the above script, you will populate the TVP the same way that any other table is populated.  Once it is populated it can be passed to a stored procedure. 

Talk to you soon,

Patrick LeBlanc, Founder www.tsqlscripts.com and www.sqllunch.com

Comments

Posted by GeriReshef on 24 November 2009

Sorry but too much bugs in the code:

c.CountryRegionCode doesn't exist,

@EndDate is a datetime while @Year is an Int..

Posted by Patrick LeBlanc on 24 November 2009

Sorry posted the wrong code.  Reposting now.

Posted by Patrick LeBlanc on 24 November 2009

GeriReshef I fixed the code.  Sorry about that.

Posted by GeriReshef on 24 November 2009

Thank you: now it works fine,

and I've learnt something new!

:-)

Posted by Anonymous on 25 November 2009

Pingback from  Accendis » SQL Server 2008 Table-Valued Parameters

Leave a Comment

Please register or log in to leave a comment.