Passing Multivalued Parameters in SQL Server 2008

In the real world, you rarely need to know about only a portion of a set of data. If you want to look at data from a more complex angle, you'll need to use multivalued parameters.

This article demonstrates an approach for integrating multivalued parameters with SQL Server stored procedures. It takes advantage of table-valued parameters (TVPs), a new feature in SQL Server 2008. You can download the code by clicking the link at the top of this article. In the .zip file, you'll find a SQL Server 2008 folder that contains the sample code. You'll also need to download and install the AdventureWorksDW2008 database.

To get the database visit Microsoft's download site. Click the link to download the SQL2008.AdventureWorks_DW_BI_v2008 database based on the targeted hardware platform. By default, the Windows Installer package will place the AdventureWorksDW2008 backup file (AdventureWorksDW2008.bak) in the Program Files\Microsoft SQL Server\100\Tools\Samples. Use SQL Server Management Studio to restore the backup file to create the AdventureWorksDW2008 database.

Parsing Parameter Values
You can define report parameters to filter data, connect related reports, and vary report presentation in reporting Services. Since SQL Server 2005, SQL Server Reporting Services (SSRS) has supported multivalued parameters to let the end user select multiple input parameters or values for reports. For example, the user could choose both United States and United Kingdom to generate a report that shows data from both countries. When a report parameter is configured as a multivalued parameter, Reporting Services automatically generates a comma-delimited string of the input values and passes the string to the report query. To work with a multivalued parameter in the report query, you must append an IN operator to the query WHERE clause of the SELECT statement. This works just fine when you use a freeform SQL statement in your report data set.

But what if you need to pass multiple values to a stored procedure? In the SQL Server 2005 release of Reporting Services, you needed to parse the comma-delimited string in the stored procedure to extract the parameter values. Comma-delimited parameter strings are difficult to implement and debug, but one of the SQL Server 2008's new features is table-valued parameters (TVPs). TVPs provide a built-in mechanism to send multiple rows of data to a stored procedure as a single parameter. This eliminates the text parsing code and simplifies working with multivalued parameters in stored procedures.

The Sales by Territory 2008 report in Figure 1 demonstrates how you can use the TVP feature to send a multivalued parameter to a stored procedure. This report uses the Reporting Services 2008 chart region, which has been greatly enhanced in this release. One of the new chart features illustrated is plotting empty points—the red crosses for the Pacific territory indicate that there's no data for this territory before July 2003.

Click to expand.

As a prerequisite for using TVPs, you need to create a user-defined table type. The DDL.SQL script in the SQL Server 2008 folder, shown in Listing 1, includes the TerritoryType user-defined table data type and the uspGetSalesByTerritory2008 stored procedure.

CREATE TYPE [dbo].[TerritoryType] AS TABLE (
	[TerritoryKey] [int] NOT NULL,
	[TerritoryName] [nvarchar](50) NOT NULL
)
GO
CREATE PROCEDURE [dbo].[uspGetSalesByTerritory2008] (
	@CalendarYear int, 
	@Territory TerritoryType READONLY
)
AS
BEGIN
	SET NOCOUNT ON;
	SELECT ST.SalesTerritoryGroup AS TerritoryGroup, D.FullDateAlternateKey AS [Date],
  	SUM(FRS.SalesAmount) AS ResellerSalesAmount
	FROM DimDate AS D INNER JOIN FactResellerSales AS FRS ON D.DateKey = FRS.OrderDateKey
  INNER JOIN DimSalesTerritory AS ST ON FRS.SalesTerritoryKey = ST.SalesTerritoryKey
  INNER JOIN @Territory AS T ON ST.SalesTerritoryKey = T.TerritoryKey
	WHERE (D.CalendarYear = @CalendarYear)
	GROUP BY ST.SalesTerritoryGroup, D.FullDateAlternateKey
END