Case when not return more than one value

Posted on

Question :

I try this, but the SwitchCase can not return more than one value.

WHERE CP.NumIntAut IN (
    CASE WHEN @NumIntAut IS NOT NULL
        THEN (@NumIntAut)
        ELSE (SELECT NumIntAut FROM @TAB_NumIntAut) END)

How can I do?

Answer :

I think you are looking to do something like this

WHERE CP.NumIntAut IN (
                       SELECT  CASE WHEN @NumIntAut IS NOT NULL
                                    THEN @NumIntAut
                                    ELSE NumIntAut END
                                    FROM @TAB_NumIntAut )

Assuming that I understand your question correctly. But it is worth noting that it will return the value @NumIntAut for every record in the @TAB_NumIntAut table if it not NULL. Which may or may not be what you are looking for.

DECLARE @NumIntAut INT = 123 --filter by only one NumIntAut...
DECLARE @CodUsu INT = 789 --or more than one NumIntAut from User
DECLARE @TAB_NumIntAut TABLE (NumIntAut INT)

INSERT @TAB_NumIntAut SELECT [...]
INSERT @TAB_NumIntAut SELECT @NumIntAut

[...]

WHERE CP.NumIntAut IN (SELECT NumIntAut FROM @TAB_NumIntAut)

The code below will pull back only records matching values in @NumIntAut or field NumIntAut in table variable @TAB_NumIntAut. @NumIntAut will be evaluated first then NumIntAut if the variable is NULL.

    INNER JOIN 
        (
            SELECT ISNULL(@NumIntAut,NumIntAut) NumIntAut
            FROM @TAB_NumIntAut 
        ) NA ON NA.NumIntAut = CP.NumIntAut

Leave a Reply

Your email address will not be published. Required fields are marked *