IN clause from variable

Question

I have the following code which allows using a variable within an IN clause:

    DECLARE @Ids varchar(50);
    SET @Ids = '24,25';

    SELECT * 
    FROM table
    WHERE ','+@Ids+',' LIKE '%,'+CONVERT(VARCHAR(50),id_field)+',%';

Since @Ids is an optional field which can be null, how would I modify the code such that it incorporates

    (@Ids IS NULL OR id_field = @Ids)

score 1 · Answer 1 · answered Sep 11 '18 at 18:12

Pretty much what you already have (parenthesis added for readability)

WHERE (@Ids IS NULL) OR (','+@Ids+',' LIKE '%,'+CONVERT(VARCHAR(50),id_field)+',%')

On a side note do realize that a query like this circumvents any index on id_field that might exist and will do a table scan to find matches.

IN clause from variable

1 Answers1