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
