CASE statement with IN in WHERE clause

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 keywo开发者_开发知识库rd '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!

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)))
)

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))

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.

I'd break this out:

IF 'this'
    SELECT 
    ...
    WHERE user_id IN (SELECT * FROM dbo.func_Id1(@User))
ELSE IF 'that'
    SELECT
    ...
    WHERE user_id IN (SELECT user_id from dbo.func_Ids2(@OrgsForReporter)) 

CASE ... END returns an expression, not a piece of literal SQL code. Rather than:

AND CASE foo WHEN bar THEN bla=ble END -- Wrong

... you have to use this:

AND bla = CASE foo WHEN bar THEN ble END -- Right

In your case, you can do something on this line:

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