0

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)
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
user2714083
  • 405
  • 1
  • 4
  • 13

3 Answers3

1

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.

Eric Brandt
  • 7,886
  • 3
  • 18
  • 35
0

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

DatabaseCoder
  • 2,004
  • 2
  • 12
  • 22
0

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);
Jeroen
  • 1
  • 2