Passing Multivalued Parameters in SQL Server 2008

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. Using this syntax, I added the GetQuery function shown in Listing 3 to the Code tab in the report properties.

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

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.

• SQL Server 2008 New Features
• Create a Job-Scheduling Advisor
• Managing ReportViewer Parameters
• Reporting Services and Multivalued Parameters