SQL where clause from concatenated string

Question

I have a string which contains a comma delimited list of id's which I want to use in a where clause.

How can I achieve this? Essentially I have:

set @ids = '5,8,14,5324,'

SELECT * FROM theTable WHERE Id IN (@ids)

but @ids is a varchar and Id is an int.

Any ideas much appreciated.

Answer 1

Take a look here for a dynamic sql explanation http://www.sommarskog.se/dynamic_sql.html

Answer 2

You'll have to use dynamic SQL with your current approach. Ensure your @ids has the trailing comma trimmed.

DECLARE @DynSQL NVARCHAR(2000)

SELECT @DynSQL=  'SELECT * FROM theTable WHERE Id IN ('  + @ids + ')'

EXEC  sp_executesql @DynSQL

Remember that dynamic SQL runs in its own scope. The simple select will work fine.

Answer 3

Another option instead of dynamic SQL would be to parse @ids into a #TempTable (one example of a parsing function is here) and then join against it.

select *
    from theTable t1
        inner join #TempTable t2
            on t1.Id = t2.Id

or you can bypass the #TempTable completely (the following code assumes the parsing function from the link above)

select *
    from theTable t1
        inner join dbo.fnParseStringTSQL(@ids, ',') t2
            on t1.Id = cast(t2.string as int)

Answer 4

Or you can use a stored proc with a table variable as the input variable and then join to that table to get your results.