Hard code SSRS multi value parameter for testing

Question

I'm trying to test a SQL query in SQL Server Management Studio that normally requires a multivalue parameter from the SSRS report its a part of.

I'm not sure to how hard code a multi value parameter in management studio. The report was created by a vendor, I'm just trying to make it runnable for testing outside of SSRS.

For example the parameter in SSRS is a collection of numbers that the user selects - ie "3100, 3102, 3105" would be the selections for the multivalue parameter called @object_code

I've got something like this - but it's not working.

 Declare @Object_Code varchar(100)
 Set @object_Code = ('3100','3102','3105')

 ....really long vendor written query I don't thoroughly understand...

 IN(@object_code) 

Answer 1

You have to use String-Split function to separate comma separated values. For example-

Declare @Object_Code varchar(100)
Set @Object_Code = '3100,3102,3105'

....really long vendor written query I dont thoroughly understand...

--T.object_code IN (@object_code)
Inner Join dbo.Split(@Object_Code, ',') as S On S.data = T.object_code

Search your database first for any string-split function. If you want to create string-split function then follow this - T-SQL split string

Answer 2

If you use SQL Server 2016 you might want to check out the function STRING_SPLIT.

If you use a lower version of SQL Server and you can't or don't want to create a separate function, the following could be an alternative:

declare @object_code varchar(100);
set @object_code = '3100,3102,3105';

select
    ltrim(rtrim(x.par.value('.[1]','varchar(max)'))) as object_code
from (
    select convert(xml,'<params><param>' + replace(@object_code,',', '</param><param>') + '</param></params>') as c
) tbl
cross apply
    c.nodes('/params/param') x(par);

Answer 3

Everybody seems to be getting hung up on splitting a string that doesn't have to be a string. We're just trouble shooting a query here and need a way to feed it values. It's not important how SSRS does it, just that we can reproduce the result.

Declare @Object_Code table (params varchar(20));

INSERT @object_Code 
VALUES ('3100'),('3102'),('3105')

  ....really long vendor written query I don't thoroughly understand...

IN (SELECT params FROM @object_code) 

Then spend some quality time getting to know the query.