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 AdventureWorks2008

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:

USE AdventureWorks2008

GO

 

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

No comments.

Leave a Comment

Please register or log in to leave a comment.