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 ( CountryCode nvarchar(3) )

 

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:

USE AdventureWorks2008 GO 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 @StartDate datetime, @EndDate datetime, @CountryRegions Sales.CountryCodes SELECT @StartDate = '4/1/2002', @EndDate = '4/30/2002' INSERT INTO @CountryRegions VALUES('US'), ('CA') EXEC Sales.GetSalesByDateAndRegion @StartDate, @EndDate, @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