CODEWITHSUNDEEP
sqlsql-serverreporting-servicesssrs-2008
BobNoobGuy
BobNoobGuy

Reputation: 1645

SSRS 2011 SQL where with case when

I have a boolean parameter in ssrs.

How do I write the query to achive the folowing:

boolean parameter @isMultiple (value is true or false)

the SQL Query is:

Select employee from employeeTable
WHERE 
CASE @isMultiple 
WHEN 'True' then employeeID in(@EmployeeIDSelected)
WHEN 'False' then EmployeeID=@EmployeeID
END

Thank you

Upvotes: 0

Views: 133

Answers (2)

Moe Sisko
Moe Sisko

Reputation: 12005

If you still want to use CASE, you could do something like this:

Select employee from employeeTable
WHERE 1 = 
(CASE @isMultiple 
WHEN 'True' then (CASE WHEN employeeID in(@EmployeeIDSelected) THEN 1 END)
WHEN 'False' then (CASE WHEN EmployeeID=@EmployeeID THEN 1 END)
END)

You might want to check query plans before deciding if it was worthwhile, though.

Upvotes: 0

user2989408
user2989408

Reputation: 3137

You cannot use CASE in WHERE but you can use OR. Try something like

SELECT employee
FROM employeeTable
WHERE (@isMultiple = 'True' AND employeeID IN (@EmployeeIDSelected))
   OR (@isMultiple = 'False' AND EmployeeID = @EmployeeID)

Upvotes: 3

Related Questions