SSRS Report: get parameter data value and store it into variable in dataset

Question

I have multi-valued parameter in my Report named @Animal which has ('Cat', 'Dog', 'Mouse').

inside dataset i need to get 'Cat', Dog', 'Mouse' and store it into @AnimalName table variable.

"Hard-Coded" way would be:

DECLARE @AnimalName TABLE (Name nvarchar (10))
INSERT INTO @AnimalName SELECT ('Cat');
INSERT INTO @AnimalName SELECT ('Dog');
INSERT INTO @AnimalName SELECT ('Mouse');

I know that I can use @Animal directly inside my dataset, the reason I'm doing this is because I'm trying to improve my report's performance. Many multi-valued parameters will make the report runs forever.

Does any one know how(the syntax) to get @Animal data values and stored it into a table variables @AnimalName inside dataset?

Thanks heaps!

Trubs Trubs · Accepted Answer · 2015-06-03T23:40:48

Pass the comma delimited string into your stored procedure and in your stored proc use a table valued function to convert you multi-valued parameter into a table.

CREATE PROC GetAllAnimals 
                      @AnimalList nvarchar(max)
AS

DECLARE @Animals TABLE (Animal nvarchar(10))
INSERT INTO @Animals SELECT * FROM dbo.fnGetValueListFromMultiSelect(@AnimalList)

and then use the @Animals table to inner join in your query

Functions declared below.

For Integer (or ID) values

CREATE FUNCTION [dbo].[fnGetIdListFromMultiSelect](@String nvarchar(MAX))
RETURNS @Results TABLE ([Id] int)
AS
    BEGIN
    DECLARE @Delimiter CHAR(1) 
    DECLARE @INDEX INT
    DECLARE @SLICE nvarchar(4000)

    IF @String IS NULL RETURN   
    SET @Delimiter = ','
    SET @INDEX = 1  

    WHILE @INDEX !=0
        BEGIN 
         -- GET THE INDEX OF THE FIRST OCCURENCE OF THE SPLIT CHARACTER
         SELECT @INDEX = CHARINDEX(@Delimiter,@STRING)
         -- NOW PUSH EVERYTHING TO THE LEFT OF IT INTO THE SLICE VARIABLE
         IF @INDEX !=0
         BEGIN
          SELECT @SLICE = LEFT(@STRING,@INDEX - 1)
          -- CHOP THE ITEM REMOVED OFF THE MAIN STRING
          SELECT @STRING = RIGHT(@STRING,LEN(@STRING) - @INDEX)
         END
         ELSE
          SELECT @SLICE = @STRING                   
            -- PUT THE ITEM INTO THE RESULTS SET
            INSERT INTO @Results([Id]) VALUES(CAST(@SLICE AS INT))          
         -- BREAK OUT IF WE ARE DONE
         IF LEN(@STRING) = 0 BREAK
    END
    RETURN
END

For string values

CREATE FUNCTION [dbo].[fnGetValueListFromMultiSelect](@String nvarchar(MAX))
RETURNS @Results TABLE ([Item] nvarchar(128) Primary Key)
AS
    BEGIN
    DECLARE @Delimiter CHAR(1) 
    DECLARE @INDEX INT
    DECLARE @SLICE nvarchar(4000)

    SET @Delimiter = ','
    SET @INDEX = 1

    WHILE @INDEX !=0
        BEGIN 
         -- GET THE INDEX OF THE FIRST OCCURENCE OF THE SPLIT CHARACTER
         SELECT @INDEX = CHARINDEX(@Delimiter,@STRING)
         -- NOW PUSH EVERYTHING TO THE LEFT OF IT INTO THE SLICE VARIABLE
         IF @INDEX !=0
         BEGIN
          SELECT @SLICE = LEFT(@STRING,@INDEX - 1)
          -- CHOP THE ITEM REMOVED OFF THE MAIN STRING
          SELECT @STRING = RIGHT(@STRING,LEN(@STRING) - @INDEX)
         END
         ELSE
          SELECT @SLICE = @STRING
         -- PUT THE ITEM INTO THE RESULTS SET
         INSERT INTO @Results([Item]) VALUES(@SLICE)

         -- BREAK OUT IF WE ARE DONE
         IF LEN(@STRING) = 0 BREAK
    END
    RETURN
END

SSRS Report: get parameter data value and store it into variable in dataset

1 Answers