CASE statement with IN in WHERE clause

Question

I'm trying to create the following WHERE clause:

AND CASE @SomePRarmeter
WHEN 'this' THEN
  user_id IN (SELECT * FROM dbo.func_Id1(@User))
WHEN 'that' THEN
  user_id IN (SELECT user_id from dbo.func_Ids2(@OrgsForReporter)
END

But I'm getting an error: Incorrect syntax near the keyword 'IN' (in the first condition) , although separately both of those conditions work. What would be the correct way to make such a statement work?

Thanks!

Solution 1

Try

AND (
  (@SomePRarmeter = 'this' AND user_id IN (SELECT * FROM dbo.func_Id1(@User)))
  OR
  (@SomePRarmeter = 'that' AND user_id IN user_id IN (SELECT user_id from dbo.func_Ids2(@OrgsForReporter)))
)

Solution 2

You are doing select * in a subquery. You need to return only one column:

(SELECT * FROM dbo.func_Id1(@User))

to this:

(SELECT YOUR_USER_ID_COLUMN FROM dbo.func_Id1(@User))

Solution 3

A case statement must result in a value, not an expression. So this won't work:

select case when 1=1 then 1 in (1,2,3) end

But this will work;

select case when 1=1 then 1 end

The value can be the result of a subquery. So one solution would be to rewrite the where clause like:

CASE @SomePRarmeter
WHEN 'this' THEN
  (SELECT count() FROM dbo.func_Id1(@User) f where f.user_id = t.user_id))
WHEN 'that' THEN
  (SELECT count() from dbo.func_Ids2(@OrgsForReporter) f where f.user_id = t.user_id))
END > 1

Now it returns the number of matching rows. You can then filter with case ... end > 1.