Getting "Subquery returned more than 1 value error message " while using Case statement inside Where Not In

sql-server, sql-server-2008

Solution

SELECT * FROM cte1 AS c
WHERE NOT EXISTS
(
  SELECT 1 FROM #temp AS t1 
  WHERE t1.nai_grant_number = CASE @AutoRenewalChk 
    WHEN 1 THEN c.GrantNumber END
);

Here is why NOT IN will not work here. Try these two queries. Would you expect the same result in each case?

SELECT x FROM (SELECT x = 1) AS x WHERE x NOT IN 
    (SELECT y = 2);

SELECT x FROM (SELECT x = 1) AS x WHERE x NOT IN 
    (SELECT y = 2 UNION ALL SELECT NULL);

Adding a possible NULL here (which Dan P suggested as an ELSE in his answer, or if #temp can contain even a single row where nai_grant_number is NULL) completely changes the semantics of NOT IN. Therefore just about any time you are thinking about writing a NOT IN query, you should re-think it as a NOT EXISTS (or LEFT OUTER JOIN, or other struct).

Problem

If I do ``` SELECT * FROM cte1 c WHERE c.GrantNumber NOT IN ( SELECT t1.nai_grant_number FROM #temp t1 ) ``` This works fine. But if I do ``` SELECT * FROM cte1 c WHERE c.GrantNumber NOT IN ( CASE WHEN @AutoRenewalChk = 1 THEN ( SELECT t1.nai_grant_number FROM #temp t1 ) END ) ``` getting error Msg 512, Level 16, State 1, Line 33 Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression. What is the reason? Cannot we use a case statement like the above? Thanks

Original source