CODEWITHSUNDEEP
reporting-servicessql-server-2008-r2in-clause
Wayne E. Pfeffer
Wayne E. Pfeffer

Reputation: 245

I need to perform a select statement in SQL Server 2008r2 with a 'where ColumnName in (@var)'

I am writing an SSRS report that will take as part of its input a list of user names generated by the SSRS drop down box with multiselect turned on. I am attempting to test my code prior to putting in the report but it's not working. Example code of what I'm doing:

declare @userList     varchar(100);

set @userList = 'domain\user1, domain\user2, domain\user3, domain\user4';

select 
    count(column)
from 
    table
where 
    userName in (@userList)

I've also tried:

set @userList = '''domain\user1'',''domain\user2'',''domain\user3'', ''domain\user4''';

to no avail.

Any ideas?

Upvotes: 1

Views: 38

Answers (1)

JC Ford
JC Ford

Reputation: 7066

When you say you're attempting to test the SQL prior to putting it in the report, does that mean you're trying to run it in SQL Server Management Studio? If so then the simple answer is you cannot test the multivalue parameter like that. TSQL does not support it. Set your test variable to a single string value for testing with SSMS.

Upvotes: 1

Related Questions