SQL Subquery using IN Statment with coalesce

Question

I have the following SELECT statmentment

SELECT
       UserID
      ,UserName
      ,TradingParty
      ,Email
      ,[PrivDesc]
      ,LastLogin
      ,IsApproved

FROM
    cte_getAll allUsers
 WHERE
      allUsers.TradingParty                                            = COALESCE(@TradingParty, allUsers.TradingParty)
    AND allUsers.Username                                      = COALESCE(@Username, allUsers.Username) 
    AND allUsers.Email                                       = COALESCE(@EmailAddress, allUsers.Email)
    AND DATEADD(dd, DATEDIFF(dd, 0, allUsers.[LastLogin]), 0)   >= COALESCE(@FromDate, DATEADD(dd, DATEDIFF(dd, 0, allUsers.[LastLogin]), 0)) 
    AND DATEADD(dd, DATEDIFF(dd, 0, allUsers.[LastLogin]), 0)   <= COALESCE(  @ToDate, DATEADD(dd, DATEDIFF(dd, 0, allUsers.[LastLogin]), 0))
    AND allUsers.IsApproved                                   = COALESCE(@AccountActive, allUsers.IsApproved)
      AND allUsers.[PrivId]                                        -- IN (SELECT privId from @selectedPriv)
                                                                      IN (COALESCE((SELECT PrivID FROM @selectedPriv), allUsers.[PrivId]))

EDIT Prior to this statment i am populating the @selectedPriv temp table according to the users input. If there is not user input therefore the table will contain nothin

On the final AND statement i am trying to find all data where privId is equal to a list of privs which the user has entered. So for example the user eneters 1,2,3,4,5,6 that is stored into a temp table, from this i need to find all the rows from my original table which are associated with these priv. Here is my issue if i use the commented IN statement it works but since the user is allowed to pass in NULL i need it within a COALESCE, but when i do this i get the message

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

I havent got a clue on why this is. Any ideas that will help me fix this issue?

Answer 1

If I understand your question correctly, you have a table variable that has values that you want to select from, but if they don't have any values in the table, you don't want the restriction at all.

The reason you're getting the error you're getting is due to COALESCE() only expecting a single value to transform, but your query is returning multiple.

This may be what you're looking for:

 AND 
 (
    NOT EXISTS (SELECT * FROM @selectedPriv WHERE PrivID IS NOT NULL)
    OR
    (
            EXISTS (SELECT * FROM @selectedPriv WHERE PrivID IS NOT NULL) 
        AND allUsers.[PrivId] IN (SELECT PrivID FROM @selectedPriv)
    )
 )