Using Multiple Parameters in SSRS from Stored Procedure Returns Blank

Question

I've created two datasets.

Dataset1 (from a stored procedure):

CREATE PROCEDURE [dbo].[usp_GetPerson](
@Category varchar(20))
AS

BEGIN
SELECT  FirstName, LastName, Category                   
FROM    tblPerson 
WHERE   (Category IN (@Category))

END

Dataset2:

SELECT DISTINCT Category
FROM   tblPerson

In SSRS, I've edited the parameter to allow multiple values and to pull available values from Dataset2.

I've tried filtering based on Dataset1 alone, but receive all the inputs which are repetitive (which is why I opted using dataset 2).

When I use the stored procedure, I can't seem to select multiple values. I'm only able to select single values, otherwise the report goes blank.

So I recreated Dataset1, but did not use the stored procedure. Instead I just wrote the SQL statement in the text editor, and I'm able to select the multiple values just fine.

Does anyone know why this happens and could help me fix this?

Note: I'm using stored procedures for when my SQL statements become more complex where I will be joining multiple databases. I tried doing this in SSRS, but it was much faster using stored procedures.

Thank you!

Answer 1

The issue when running from an SP using a query like this:

SELECT  FirstName, LastName, Category                   
FROM    tblPerson 
WHERE   (Category IN (@Category))

where @Category is something like 'Cat1,Cat2,Cat3'

is that the IN clause is treating @Category as a single string, i.e. one single value, not a set of multiple values, so will most likely never match anything.

This is why when you have only one value it works - something like 'Cat1' will match one or more rows correctly.

When run as an embedded query in the report itself, SSRS will essentially treat this as a piece of dynamic T-SQL, which means the string @Category gets added into the main query as written and works correctly.

So there are a couple of options for the SP.

You can run dynamic T-SQL in the SP, something like:

DECLARE @Query NVARCHAR(max) = N'SELECT  FirstName, LastName, Category
      FROM    tblPerson
      WHERE   (Category IN (' + @Category + ))'

EXEC @Query

Dynamic T-SQL is seldom ideal, so the other option is turn @Category into a set for use in the query, typically using a function to split the string and return a table, which will look something like:

SELECT  FirstName, LastName, Category                   
FROM    tblPerson 
WHERE   (Category IN (SELECT values from dbo.SplitString(@Category)))

There are any number of way to get a set returned from a delimited string.

See the SO question for many split options.

Or the definitive article from Erland Sommarskog .

Choose the one you like the best and go from there.

Answer 2

You can only use IN with static defined within your query. In this cas use =

CREATE PROCEDURE [dbo].[usp_GetPerson](
@Category varchar(20))
AS
BEGIN
   SELECT  FirstName, LastName, Category                   
   FROM    tblPerson 
   WHERE   (Category = @Category)

Answer 3

--Found this function somewhere on the internet-cant remember where

CREATE FUNCTION [dbo].[SplitString] ( @stringToSplit VARCHAR(MAX) )
RETURNS
 @returnList TABLE ([Name] [nvarchar] (500))
AS
BEGIN

DECLARE @name NVARCHAR(255)
DECLARE @pos INT

 WHILE CHARINDEX(',', @stringToSplit) > 0
 BEGIN
  SELECT @pos  = CHARINDEX(',', @stringToSplit)  
  SELECT @name = SUBSTRING(@stringToSplit, 1, @pos-1)

  INSERT INTO @returnList 
  SELECT @name

  SELECT @stringToSplit = SUBSTRING(@stringToSplit, @pos+1, LEN(@stringToSplit)-@pos)
 END

 INSERT INTO @returnList
 SELECT @stringToSplit

 RETURN
END

--SP input param from report
@CarIds varchar(100) CREATE Table #CarsTable(CarID varCHAR(10))

INSERT #CarsTable
SELECT LTRIM(Name) FROM dbo.SplitString(@carIds) 

Then for your query you'd say...

WHErE CarsOwned IN  (SELECT CarId  FROM #CarsTable)