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 AdventureWorksGO IF EXISTS (SELECT * FROM sys.types WHERE name = 'CountryCodes' AND schema_id = SCHEMA_ID('Sales'))DROP TYPE Sales.CountryCodesGO CREATE TYPE Sales.CountryCodesAS 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 NULLDROP PROC Sales.GetSalesByDateAndRegionGO CREATE PROC Sales.GetSalesByDateAndRegion@Month varchar(20), @Year int, @CountryRegions Sales.CountryCodes readonly AS SET NOCOUNT OFFSELECT cr.Name, SUM(TotalDue) TotalDue FROM Sales.SalesOrderHeader sodINNER JOIN Sales.SalesTerritory stON sod.TerritoryID = st.TerritoryID INNER JOIN Person.CountryRegion crON st.CountryRegionCode = cr.CountryRegionCode INNER JOIN @CountryRegions cON cr.CountryRegionCode = c.CountryRegionCode WHERE DATENAME(MONTH,sod.OrderDate) = @Month AND YEAR(sod.OrderDate) = @Year GROUP BYcr.Name, DATENAME(MONTH,sod.OrderDate), YEAR(sod.OrderDate) SET NOCOUNT ONGO |
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 @CountryRegionsVALUES ('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
