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