CODEWITHSUNDEEP
sqlsql-serversql-server-2008t-sql
Tassadaque
Tassadaque

Reputation: 8199

Case Issue in SQL statement with IN statement

I ve following simple case statement

 CASE WHEN @SPOID is not null THEN 
            (176)
       ELSE
            (SELECT designationID from setupdesignations where reportto = 99)
       END

When else clause of this statement execute it gives me the following error

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

I am using this statement in the IN clause I was expecting multiple result to be returned in else case but it is giving me the above error. If I remove the case statement and execute the else part in query then i get the expected result

Upvotes: 3

Views: 295

Answers (4)

user359040
user359040

Reputation:

Try changing your in clause to:

... in 
(SELECT designationID 
 from setupdesignations 
 where reportto = 99 and @SPOID is not null
 UNION
 SELECT 176 where @SPOID is null)

Upvotes: 0

Kev Ritchie
Kev Ritchie

Reputation: 1647

You could try using a dynamic query to achieve you end goal. The following link might point you in the right direction - check the last post: http://forums.devx.com/showthread.php?t=150247

Upvotes: 1

aF.
aF.

Reputation: 66697

Although you are using IN clause, the CASE statement only allows one row as return statement.

You'll have to figure another way to do it.

Upvotes: 2

Alex K.
Alex K.

Reputation: 175766

You can't use a subselect as an expression within a CASE match, it expects a single value. Instead how about;

where 
  (@SPOID is not null and infield = 176)
or
  (@SPOID is null and infield in (SELECT designationID from setupdesignations where reportto = 99))

Upvotes: 3

Related Questions