If we pass multiple ProductSubCategoryID values to the stored procedure, Reporting Services converts the multivalued parameter into a string of values and we get the error message Msg 8114, Level 16, State 1, Procedure Test_MVP, Line 0 Error converting data type varchar to int. This error indicates that the parameter in the stored procedure is expecting an integer value for ProductSub-Category. Because we passed in a string— '2,3,4,5'—the execution fails.
To allow a string value for a multivalued parameter list coming from a Reporting Services report—in this case, the original Product Line Sales report, we need to alter the stored procedure so that the data type of @ProductSubCategory is variable-length character, or varchar, because we don't know precisely how long the string will be. We'll set a limit of 100 total characters, varchar(100), which will be an ample length for testing the @ProductSubCategory parameter. To alter the stored procedure, we modify the first and third lines of the code in Listing 1 so that the first five lines of code appear as follows:
ALTER PROCEDURE [DBO].[Test_MVP]
@ProductCategory Int ,
@ProductSubCategory varchar(100),
@StartDate datetime, @EndDate datetime
When I executed the altered stored procedure to test it, I received a new error message. Predictably, previewing the Product Line Sales SP report displayed the same error, which Figure 5 shows. It was about this time that I almost gave up on multivalued parameters, but I know that there's typically a way around a stubborn child. I did some research and learned that I could create a user-defined function (UDF) to parse the comma-delimited list of values returned from the original Product Line Sales report and introduce them to the Product Line Sales SP report.
A UDF Does the Trick
You can successfully introduce a multivalued parameter list into a stored procedure in several ways. For example, you can store the SQL statement that drives a report in a variable, concatenate the parameter list to the variable, then execute the constructed statement through the built-in procedure sp_executesql. Another possibility is to load the parsed values of the comma-delimited list of individual parameters into a temporary table or table variable, then join the original query to this temporary table by using a statement such as
INNER JOIN #mytempPSC ON
ProductSubCategoryID =
#mytempPSC.ProductSubCategoryID
The temporary table would contain only the user-selected list of values from the report and would therefore return the desired results.
Because I tend to shy away from anything that resembles dynamic SQL (as the first example does) and because I'd most likely need to put temporary table logic to parse the delimited string directly in the stored procedure, I chose to incorporate a UDF to store and execute the parsing logic. Using a UDF also lets me call the parsing logic from any stored procedure that requires string parsing.The type of UDF I chose to create is a table-valued function, which like a temporary table, stores values as rows in a table, essentially turning the string into the required array. Listing 2 shows the code for the UDF that I used, called fn_MVParam.
The function uses several of its own parameters to store and parse multivalued strings coming from the original Product Line Sales report's parameter.The function accepts two input values, a string value and a delimiter character, which by default is a comma (,). The delimiter parameter lets us extend the versatility of the function by giving us the ability to pass in any delimiter that we need.
The fn_MVParam function uses the CHARINDEX function to search for the existence of the delimiter. If it finds the delimiter, fn_MVParam knows that the string has multiple values. It then uses the LEFT function to find and store the first parameter value from the string. The fn_MVParam function gets all the characters up to but not including the delimiter and inserts this value into a table that has one column called Param.
Next, fn_MVParam uses the RIGHT function to remove the already extracted parameter in the string. For example, if we have a string value of '1,2,3,4,5', the first iteration of the WHILE loop extracts the 1, then sets the value of the string to '2,3,4,5'. The fn_MVParam function procedes until the LEN function determines that the length of the string is 0.
The end result is a row list of multiple parameter values that we can use in the WHERE clause of our stored procedure. We can test the execution of the fn_MVParam function in Management Studio by passing in a comma-delimited list, as Figure 6 shows.
Finally, we must alter the stored procedure that drives the Product Line Sales SP report one more time so that it uses the new fn_MVParam function.We use the function in the WHERE clause as a subquery to be evaluated for the ProductSubCategory field, as follows:
WHERE (PC.ProductCategoryID =
@ProductCategory) AND
(PS.ProductSubCategoryID IN
(SELECT Param FROM fn_MVParam
(@ProductSubCategory,',')))
AND (SOH.OrderDate > @StartDate)
AND (SOH.OrderDate < @EndDate)
Web Listing 1 (http://www.sqlmag.com, InstantDoc ID 47788) shows the final version of theTest_MVP stored procedure with the two changes we've made.
Figure 7 shows the Product Line Sales SP report successfully run by using the Test_MVP stored procedure and its fn_MVParam UDF. It's worth mentioning that the Product Line Sales and Product Line Sales SP reports use two Visual Basic .NET functions—Join() and Split()—to work with the multivalued parameter as an array.You can see the Join() function at work in Figure 7. It creates the report header information for product categories and subcategories based on the parameter selection.
Adding multivalued parameters as a Reporting Services feature was absolutely a great idea. Many report designers—those who didn't want to write their own parameter selection front end—were formerly relegated to an all-or-nothing approach when setting criteria for a report by using parameters. Multivalued parameters let us set exactly the criteria we want. For those of us who used stored procedures to develop the bulk of reports in SQL Server 2000 Reporting Services, string-parsing workarounds are available that let us migrate our reports to make use of multivalued parameters. Thankfully, SQL Server has UDFs that we can use to create a solution, not just a workaround, to using arrays in T-SQL.
Rodney Landrum (rodneyl@healthware.com) is director of research and development for Pro Business Systems, a software development company in Pensacola, Florida that produces a SQL Server?based application for the healthcare industry. He is an MCSE and a Citrix Certified Administrator.