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 FilesMicrosoft SQL Server100ToolsSamples. 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.

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)GOCREATE PROCEDURE [dbo].[uspGetSalesByTerritory2008] (@CalendarYear int, @Territory TerritoryType READONLY)ASBEGINSET NOCOUNT ON;SELECT ST.SalesTerritoryGroup AS TerritoryGroup, D.FullDateAlternateKey AS [Date],  SUM(FRS.SalesAmount) AS ResellerSalesAmountFROM 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.TerritoryKeyWHERE (D.CalendarYear = @CalendarYear)GROUP BY ST.SalesTerritoryGroup, D.FullDateAlternateKeyEND

The CREATE TYPE statement creates the TerritoryType TVP. The TerritoryType table defines two columns. The TerritoryKey column will store the territory identifier. The TerritoryName column is for the territory name. Strictly speaking, the TerritoryName column isn't needed, but I've included it for reference and it’s always a best practice to document as you go, which is what I did by including the TerritoryName column in the TVP.

The CREATE PROCEDURE statement creates the uspGetSalesByTerritory2008 stored procedure, which takes the @CalendarYear and @TerritoryType TVPs as arguments. You'll notice that you don't need any code for parsing the parameter values because they're readily available within the TVP. To restrict the query data, you can simply join the SalesTerritory table to the TerritoryType TVP as you would join a regular table.

Designing the Report Query

Once the stored procedure is in place, you can design a report query to use it. Reporting Services doesn't support TVPs natively, but you can use an expression-based query to generate statements to load the stored procedure parameters and execute the stored procedure. Listing 2 shows what the statements should look like if the user selects year 2004 and North America and Pacific territories.

DECLARE @CalendarYear int = 2004DECLARE @Territory TerritoryTypeinsert into @Territory values (1, 'North America')insert into @Territory values (9, 'Pacific')EXECUTE [dbo].[uspGetSalesByTerritory2008] @CalendarYear, @Territory

Using this syntax, I added the GetQuery function shown in Listing 3 to the Code tab in the report properties.

Public Function GetQuery(ByVal calendarYear As Integer, ByVal values As Object(), ByVal labels As String()) As String    Dim sb As New System.Text.StringBuilder()     sb.AppendLine([String].Format("DECLARE @CalendarYear int = {0}", calendarYear))     sb.AppendLine("DECLARE @Territory TerritoryType")     For i As Integer = 0 To values.Length - 1     sb.AppendLine([String].Format("insert into @Territory values ({0}, '{1}')", values(i), labels(i)))     Next     sb.AppendLine("EXECUTE [dbo].[uspGetSalesByTerritory2008] @CalendarYear,@Territory")     Return sb.ToString() End Function

Next, I changed the report data set to use the following expression-based query:

=Code.GetQuery (Parameters!CalendarYear.Value, Parameters!Territory.Value, Parameters!Territory.Label)

At run time, this expression calls the GetQuery function and passes the parameter values. The selected values of the Territory parameter will be passed as an object array and the labels will be passed as a string array. GetQuery uses a StringBuilder object to construct the required T-SQL statements. First, it declares the CalendarYear parameter. Next, it loops through the input values of the Territory parameter and generates T-SQL code to insert the selected territories into the TerritoryType TVP. Finally, it appends a statement to execute the stored procedure and pass the parameters. Because the GetQuery method only performs string manipulation, the default .NET Code Access Security Execution permission is sufficient for the GetQuery method to execute successfully.

Related Reading: