Introduction

A few days ago, I did a presentation for a Virtual Chapter meeting where I discussed SQL Server Data Access Layers. The user obtained his or her data by submitting an argument to a parameter. In our case, there were three of these parameters, thus requiring 3 different arguments.
During the presentation I received the same question, numerous times, ‘what happens if I wish to specify parameters 1 and 3, and yet see all of the available values for parameter 2?’
As a follow-up and as promised (in the first portion of this saga), here is the solution that I utilized for a multi-selects scenario.

Scenario:

We have a car dealership which has a number of cars, makes and each car may have different number of doors.
The car table may be seen in the following screen dump:

The question  becomes: Assuming I wish to make any query as flexible as possible, so that I may use the make, NoOfDoors, and colour as parameters (to any report), how do I structure a generalized query to cater for all scenarios.

Some folks may be looking for green cars only.
Others: Chevys with 4 doors, regardless of colour.
Others: Only 4 door cars regardless of make and colour.

I believe that the austute reader picks up on the challenge.
The code behind this type of challenge may be seen below:

use multiselect
go

--alter procedure Getdata
--(
--@Makes varchar(2000),
--@Colour varchar(2000),
--@Doors varchar(2000)
--)
--as
declare @Makes as varchar(2000)
declare @Colour as varchar(2000)
declare @Door as varchar(2000)
set @makes = 'Chevy,Ford,Honda,Toyota,ford1'
set @Colour = 'Green,Yellow,Red'
set @Door = '2,4,5'
declare @Makes2 as varchar(2000)
declare @Colour2 as varchar(2000)
declare @Door2 as varchar(2000)
declare @make   as table (Make varchar(50))
declare @colours as table (Colour varchar(50))
declare @doorss  as table (Doors varchar(50))
declare @delimiter   varchar(1)
set @delimiter = ','
--Make
BEGIN
WHILE CHARINDEX(@Delimiter,@Makes,0) <> 0
Begin
set  @makes2 =RTRIM(LTRIM(SUBSTRING(@Makes,1,CHARINDEX(@Delimiter,@Makes,0)-1))) --From 1 to position of ',' less 1
set  @Makes=RTRIM(LTRIM(SUBSTRING(@makes,CHARINDEX(@delimiter,@Makes,0)+LEN(@delimiter),LEN(@makes))))
--Above
--From postion of comma ',ford,chevy...' to the end of string
IF LEN(@makes2) > 0
INSERT INTO @make SELECT @Makes2 --Place the substring into table variable
END
-----------------------------------------------------------
IF LEN(@Makes) > 0
INSERT INTO @Make SELECT @Makes  -- Flush the remaining buffer into the table variable.
--Colour
WHILE CHARINDEX(@Delimiter,@Colour,0) <> 0
Begin
set  @Colour2 =RTRIM(LTRIM(SUBSTRING(@Colour,1,CHARINDEX(@Delimiter,@Colour,0)-1))) --From 1 to position of ',' less 1
set @Colour=RTRIM(LTRIM(SUBSTRING(@colour,CHARINDEX(@delimiter,@Colour,0)+LEN(@delimiter),LEN(@colour))))
--Above
--From postion of comma ',ford,chevy...' to the end of string
IF LEN(@Colour2) > 0
INSERT INTO @Colours SELECT @Colour2 --Place the substring into table variable
END
-----------------------------------------------------------
IF LEN(@Colour) > 0
INSERT INTO @Colours SELECT @Colour  -- Flush the remaining buffer into the table variable.
--Doors
WHILE CHARINDEX(@Delimiter,@Door,0) <> 0
Begin
set  @Door2 =RTRIM(LTRIM(SUBSTRING(@Door,1,CHARINDEX(@Delimiter,@Door,0)-1)))
set @Door=RTRIM(LTRIM(SUBSTRING(@Door,CHARINDEX(@delimiter,@Door,0)+LEN(@delimiter),LEN(@Door))))
--Above
--From postion of comma ',ford,chevy...' to the end of string
IF LEN(@Door2) > 0
INSERT INTO @Doorss SELECT @Door2 --Place the substring into table variable
END
-----------------------------------------------------------
IF LEN(@Door) > 0
INSERT INTO @Doorss SELECT @Door  -- Flush the remaining buffer into the table variable.
END
select * from cars where make in
(select * from @make)
and colour in (select colour from @Colours)
and NoOfDoors in (select Doors from @Doorss)

Based upon this query and the data within our database table, the query above produces the following data:

Utilizing this technique within the Reporting Services world

In the discussion which follows, I set up a small SQL Server 2012 Reporting Services project for you to see how this technique may be applied within a production environment. The finished project may be seen below:

Note that three parameters that must be completed in order to populate the matrix.

To start off we shall create four datasets.  Three of which will hold distinct makes, colours and number of doors. The fourth dataset will be used for the result set. The ‘make’ dataset is constructed as shown below:

Make:

The Colour and NoOfDoors datasets are constructed in a similar manner.

We now create the three parameters to be used to pull our data to the main data matrix. The ‘make(s)’ parameter is shown below: Note that I have ‘checked’ ‘ Allow Multiple Values’.

The NoOfDoors and Colour parameters are defined in a similar manner.
Thus Make pulls its distinct values from the Make(s) dataset.
Colour pulls its distinct values from the colour dataset.
NoOfDoors pulls its distinct values from the NoOfDoors dataset.

Setting up the fourth dataset: Inventory

The fourth dataset and the most important dataset is set up with the query/ stored procedure that we saw earlier within this article.

NOTE:  This time around we shall be utilizing the stored procedure to extract the data. The code for the stored procedure may be found above. Note that we pass 3 string arguments to the three stored procedure parameters.

About the stored procedure

The astute reader will note that the strings passed to the stored procedure are comma delimited. This necessitates splitting the string, removing the commas and as we progress from left to right (in parsing the string), place the pertinent value(s) into a table variable.
e.g. ‘chevy,ford, honda’
This is achieved by use of a WHILE loop, removing the left most value with each iteration (using CHARINDEX() ) and placing this value into the table variable. This continues until we have parsed the whole string. (See the code above).
By the time we reach the end of each string, chance are that there is a last remaining item within the string buffer. We need to add this to the table variable as well.

Running our report

When the report is run, we note that the three parameter drop down boxes now contains the distinct values for make(s), colour and number of doors (See the screen dump below).
MULTI SELECT has now been enabled and should you chose this option, then all of the items that YOU chose from the dropdown box are placed into a comma delimited string, (which is the argument passed to the stored procedure).

Make (has been completed and is shown as a part of the screen dump immediately below)

No Of Doors is shown below

..and colour

The Final Report

All models, two and five doors that are either beige, blue or light grey are shown below.

Conclusion

Reporting, utilizing flexible parameters which are set at run time is easily implemented.  This gives our end users the ultimate flexibility and eliminates the need to have varied flavours of the same report.
Should you wish a copy of the database and code that I used for this article, please contact me at steve.simon@sqlpass.org
Happy programming!