reporting-servicessqlparameter

Assigning multi value parameter (values) to a local parameter in sql query

I have a situation where I need to store the multi value parameter to a local parameter in sql query. In the SSRS report builder I have created two multi value parameters @Customer and @LogisticsGroup And in my SQL query , I have to assign those values to local parameters something like this

DECLARE @Acct NVARCHAR(100) , @LgstGroup NVARCHAR(MAX)
SELECT @Acct = (@Customer) 
,@LgstGroup = (@LogisticsGroup)

But with this kind of approach I'm able to select only one value , if I select two values then the query is failing.

I tried this , but it seems like incorrect syntax.

DECLARE @Acct NVARCHAR(100) , @LgstGroup NVARCHAR(MAX)
SELECT @Acct IN (@Customer) 
,@LgstGroup IN (@LogisticsGroup)

Please help to resolve this issue. Thanks much

Solution

  • You can store delimited values in a parameter. So for example, @Customer might have a string of ID's in it like 1,2,3. The report can concatenate the multiple values into this format using the Join function.

    Now, in the SQL, one option is to use the LIKE operator to search the string. You could write something like:

    @Customer like '%,' + Column_Name + ',%'

    However, this approach is inefficient and you have to be careful of partial value matches.

    A better approach is to create a user-defined table-valued function that can split the values and treat them like a table. There are plenty of examples out there, it's a pretty simple function. Then in practice it would look like this:

    WHERE Column_Name in (select * from Split(@Customer))

    OR

    INNER JOIN Split(@Customer) ON...