TSQL error when CASE-WHEN returns subquery

Question

Is there a way to nest CASE-WHEN statements in an IN() statement such that one of the WHEN or ELSE returns a subquery. To me, it should not be an issue, but somehow I am getting error:

"Subquery returned more than 1 value."

IN() is supposed to handle more than one values!

Here is a small example to reproduce the error:

-- tblA will be searched for values
Declare @tblA  table (i int)
insert @tblA
    select 1
    union select 2
    union select 3

--tblB: its values will be searched in tblA
Declare @tblB  table (i int)
insert @tblB
    select 2
    union select 3
    union select 1

--@c used by the CASE statement to match
declare @c varchar(50)
set @c = 'Match'

select *
from @tblA
where i IN ( -- IN statement should accept subquery in it
    case @c
        when 'Dont Match' then 2 --If it had matched, then the single value 2 would have been returned and TSQL would be happy
        else (
            select i from @tblB --TSQL not happy and causing error when more than one values returned from the subquery
        )
    end
)

Instead of using `IN (...)` why don't you simply use a standard `JOIN` clause? More efficient, and it works! — Hannah Vernon, Sep 19 '13 at 04:51
@MaxVernon, I agree, but this is a very simple example of something I am doing that wont allow for JOIN statement. Thanks anyways. — Aamir, Sep 19 '13 at 04:57
If you want a solution to your problem you should post the actual code. — Hannah Vernon, Sep 19 '13 at 05:00

score 2 · Accepted Answer · answered Sep 19 '13 at 08:04

2

try

select *
from @tblA A
WHERE (@c = 'Dont Match' AND i = 2) OR 
(@c <> 'Dont Match' AND EXISTS (SELECT * FROM @tblB WHERE i = A.i)

answered Sep 19 '13 at 08:04

Steve Ford

7,433
19
40

Steve, thanks for the advice. I ended up doing something similar to what you have suggested and seems like a next best solution. The code is not cleaner, but it works. It would have been nice if the CASE-WHEN statement had worked. Thanks! – Aamir Sep 20 '13 at 00:09

Appyks · Answer 2 · 2013-09-19T06:22:59.090

1

Your problem is NOT with the IN clause but with the CASE.
CASE cant handle multiple values.
Valid

DECLARE @TBLA  TABLE (I INT)
INSERT INTO @TBLA
        ( [I] )
SELECT 0

SELECT [Result]=CASE WHEN 1=2 THEN 
    (SELECT I FROM @TBLA T) 
ELSE 
    (SELECT I FROM @TBLA T) 
END

Invalid since the table variable contains two records.

DECLARE @TBLA  TABLE (I INT)
INSERT INTO @TBLA
        ( [I] )
SELECT 0
UNION 
SELECT 1


SELECT [Result]=CASE WHEN 1=2 THEN 
    (SELECT I FROM @TBLA T) 
ELSE 
    (SELECT I FROM @TBLA T) 
END

edited Sep 19 '13 at 06:22

answered Sep 19 '13 at 06:03

Appyks

496
3
12

1

I Agree. I hope they fix it in future versions so that CASE-WHEN could return subqueries depending on the context its being used. – Aamir Sep 20 '13 at 00:14

score 0 · Answer 3 · answered Sep 19 '13 at 04:54

0

IN() is supposed to handle more than one values!

Yes, it actually is. You can see it if you modify your query this way

select *
from @tblA
where i IN ( select i from @tblB )

This query will be executed without errors. The error was produced by CASE statement for it can't get more than 1 value in THEN or ELSE clauses.

answered Sep 19 '13 at 04:54

Igor Borisenko

3,806
3
34
49

Igor, there is a reason for using the case-when in the IN() clause. Otherwise, I wouldn't have done it. Thanks though. – Aamir Sep 19 '13 at 05:00
@Aamir I can bet that there are several solutions for your problem. And the one you chose is one of the most expensive. – Igor Borisenko Sep 19 '13 at 05:04

TSQL error when CASE-WHEN returns subquery

3 Answers3