CODEWITHSUNDEEP
sqlt-sqlwhere-clause
Masha
Masha

Reputation: 327

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

Upvotes: 2

Views: 22315

Answers (5)

Álvaro González
Álvaro González

Reputation: 146410

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

Upvotes: 0

Joe Stefanelli
Joe Stefanelli

Reputation: 135799

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

Upvotes: 0

jthg
jthg

Reputation: 2850

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

Upvotes: 12

Andomar
Andomar

Reputation: 238078

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.

Upvotes: 1

kemiller2002
kemiller2002

Reputation: 115450

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

Upvotes: 2

Related Questions