Using a CSV with an IN sub-select

Written by Bill Graziano on 13 October 2002

One of the most asked questions involves a bit of code like this: Select * From Products Where ProductID in (@SomeCSVString). I've always said the only way to do this was through dynamic SQL. Actually there's a better way in SQL Server 2000. It doesn't involve dynamic SQL and it's really easy to use.

We'll start with a stored procedure to convert a comma-separated value string to a table. It was in this article-ette from a few years ago. Then we'll convert it to a user-defined function. That looks like this:

And that's an easy way to pass in a CSV and return the appropriate records. This sample is hard coded to return an int value. You could easily convert it to whatever datatype you'd like. Or you could have it return a sql_variant and convert the value as needed.