You might have noticed that the category parameter at this point only allows us to select one category at a time. We may want to select two or more Categories.
Let's set the Category parameter to accept multiple values by selecting Allow multiple values in its properties:
The next thing we need to do is create a table-valued function that returns an array of categories. What the function does is simply collect the selected categories from our report and pass it to our stored procedure. We'll do two more things to complete our multivalued parameter.
Note
Table-valued functions are user-defined functions that returns Table datatype. For more information about Table-valued functions, please go to this link http://technet.microsoft.com/en-us/library/ms191165(v=sql.105).aspx.
First, let's create the table-valued function and name it MultiValueParamSplit. Please find the script named MultiValueParamSplit_Ch01.sql in the code folder for this chapter.
Next, let's modify our stored procedure dbo.uspSalesDetails to make use of our function like this:
Select … From… Where Category IN (Select [Category] From dbo.MultiValueParamSplit(@Category, ','))
Tip
Downloading the example code
You can download the example code files for all Packt books you have purchased from your account at http://www.packtpub.com. If you purchased this book elsewhere, you can visit http://www.packtpub.com/support and register to have the files e-mailed directly to you.
Now, our report should allow us to select multiple categories to pass on our Category parameter:
