CODEWITHSUNDEEP
sql-serversql-server-2008t-sqlssrs-2008
Amarundo
Amarundo

Reputation: 2397

How to effectively pass a multi-choice SSRS 2008 parameter to a stored procedure

I have a report in SSRS 2008 R2 / BIDS. One of the parameters is a multi-select text for, let's say, names, which I get the values from a stored procedure. The user selects one or more of these values.

I then call another stored procedure to get the data for the report, and I pass the parameter with the names selected as a parameter to the stored procedure, @p_names.

In that stored procedure, I select the records WHERE CHARINDEX(fName,@p_names) > 0. In other words, where I find that the first name for the record is in the parameter string.

The obvious problem is that, if for example, the values are:

And the user selects

  • Joe
  • Mary
  • Mary Joe
  • Edward
  • Cindy

Then my CHARINDEX selection will return Joe, Mary, Mary Joe and Edward, when the used only wanted Mary Joe and Edward.

  • Mary Joe
  • Edward

How should I pass the multichoice parameter to the stored procedure and how should I structure my WHERE clause?

Thanks.

Upvotes: 1

Views: 3376

Answers (2)

liebs19
liebs19

Reputation: 549

This should work for exact matches without having to split:

WHERE CHARINDEX(',' + fName + ',',',' + @p_names + ',') > 0

However, if you have large lists it may have better performance using the split method.

Upvotes: 1

M.Ali
M.Ali

Reputation: 69494

Right Amarundo there is no easy solution for this , when you allow Multiple values to be passed to your Report Parameter, It actually passes a Comma Deliminated string of them values to your DataSet Query/Stored Procedure.

To Make it work you will need two thing

1) Create a Function which accepts a Comma Deliminated string and split them.

2) Modify you Store Procedure and add this function in there in a way that passed parameter is passed to the function inside that store procedure and that function split the values before passing it onto your store Procedure .

Create this function 1st

Function Definition

CREATE FUNCTION [dbo].[FnSplit]
(
@List nvarchar(2000),
@SplitOn nvarchar(5)
)
RETURNS @RtnValue table (Id int identity(1,1), Value nvarchar(100))
AS
BEGIN

WHILE(Charindex(@SplitOn,@List)>0)

  BEGIN


        INSERT INTO @RtnValue (value)

        SELECT VALUE = ltrim(rtrim(Substring(@List,1,Charindex(@SplitOn,@List)-1)))

        SET @List = SUBSTRING(@List,Charindex(@SplitOn,@List)+len(@SplitOn),len(@List))

  END


INSERT INTO @RtnValue (Value)

SELECT VALUE = ltrim(rtrim(@List))

RETURN

END

Modify you strored Procedure something like this

Stored Procedure

CREATE Procedure usp_Multipleparameter (@Param1 VARCHAR(1000)= NULL)
AS
BEGIN

        SELECT * FROM TableName
        where ColumnNAME  IN (SELECT Value FROM dbo.FnSplit(@Param1,','))

END
GO

Upvotes: 2

Related Questions