Passing a multi-value parameter into a variable in SSRS without a stored procedure

Question

I do not have access to sp's, but I have created a dynamic query that is going to accept a multi-valued parameter, pass it along to a variable, which is then going to be used in the query. Short example of my query.

DECLARE @Parameter2 varchar(200)
SET @Parameter2 = @Parameter1

SELECT personID from foo where filename IN (@Parameter2)

I have a report Parameter for @Parameter1 that will allow multiple values, which will be coming from another query from a dataset. I can pass a single filename from @Parameter1 to @Parameter2 with no issue, but when selecting multiple ones, I get the "invalid syntax at ...',' because the parameters are passed like this 'filename1,filename2,filename3'.

How to I parse these multiple parameters from @Parameter1 to @Parameter2 so it can be used in the query without a stored procedure? I have tried looking in different topics here and splitting the @Parameter1 by "," and joining the variable in the dataset properties, but I am still getting either the invalid syntax error or declaring a scalar variable error.

I don't have much experience in SQL Server, but it seems difficult to split the strings after they have been selected in the preview, without the strings passing through an sp first.

John Cappelletti John Cappelletti · Accepted Answer · 2017-01-27T17:42:10

Since you can do DYNAMIC SQL, consider the following

DECLARE @Parameter1 varchar(200) = 'filename1,filename2,filename'

DECLARE @Parameter2 varchar(200) = ''''+Replace(@Parameter1,',',''',''')+''''

Declare @SQL varchar(max) = 'SELECT personID from foo where filename IN ('+@Parameter2+')'
Exec(@SQL)

The Generated SQL Looks like This:

SELECT personID from foo where filename IN ('filename1','filename2','filename')

Passing a multi-value parameter into a variable in SSRS without a stored procedure

1 Answers