T-SQL ERROR - Subquery returned more than 1 value

Question

I am getting the following error in SQL Server:

Msg 512, Level 16, State 1, Line 18 Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , > >, >= or when the subquery is used as an expression.

My code is as follows:

DECLARE
@personNumber varchar(20),
@itemNumber varchar(20)


SET @personNumber = 'null'
SET @itemNumber = 'null'

SELECT      
    OU.UserID
    ,OU.Name
    ,IGWQ.itemNumber
    ,IG.itemName
    ,IGWQ.QuantityOnHand
FROM dbo.Table1 IGWQ 
INNER JOIN Table2 OU        ON  IGWQ.UserId = OU.UserId
INNER JOIN Table3 IG        ON  IGWQ.itemNumber = IG.itemNumber
WHERE IGWQ.userid IN (CASE  WHEN @personNumber = 'null' 
                            THEN (  SELECT DISTINCT 
                                        UserID 
                                    FROM Table2 WITH(NOLOCK) 
                                    WHERE [Role] = '01')
                            ELSE @personNumber
                            END)
AND IGWQ.itemNumber IN (CASE    WHEN @itemNumber = 'null'
                                THEN (  SELECT DISTINCT 
                                            itemNumber 
                                        FROM dbo.Table1 WITH(NOLOCK))
                                ELSE @itemNumber
                                END)

Can anyone suggest a solution to this problem? I thought using 'IN' would have fixed the issue.

Answer 1

WHERE (IGWQ.userid = @personNumber OR 
       @personNumber = 'NULL' and
       EXISTS (SELECT *
                 FROM Table2 t WITH (NOLOCK)
                WHERE t.[Role] = '01' AND t.UserID = IGWQ.userid)
  )
  AND (IGWQ.itemNumber = @itemNumber OR
       @itemNumber = 'NULL' and 
       EXISTS (SELECT *
                 FROM dbo.Table1 t WITH (NOLOCK)
                WHERE t.itemNumber = IGWQ.itemNumber)
  )

Notes:

1. CASE returns a scalar, it cannot return a subquery (i.e. set).
2. WITH (NOLOCK) is deprecated. change the connection isolation to READ UNCOMMITTED or SNAPSHOT instead.

Answer 2

use this to fix the rest:

WHERE ((@personNumber <> 'null' AND @personNumber = IGWQ.userid)
OR (@personNumber = 'null' AND IGWQ.userid IN (  SELECT  UserID 
                                    FROM Table2 WITH(NOLOCK) 
                                    WHERE [Role] = '01')
   ))

You don't need the DISTINCT as the IN statement only handles distinct values.