Using a CSV with an IN sub-select

By Bill Graziano on 13 October 2002 | Tags: SELECT , User Defined Functions

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:

Create Function dbo.CsvToInt ( @Array varchar(1000)) 
returns @IntTable table 
	(IntValue int)

	declare @separator char(1)
	set @separator = ','

	declare @separator_position int 
	declare @array_value varchar(1000) 
	set @array = @array + ','
	while patindex('%,%' , @array) <> 0 
	  select @separator_position =  patindex('%,%' , @array)
	  select @array_value = left(@array, @separator_position - 1)
		Insert @IntTable
		Values (Cast(@array_value as int))

	  select @array = stuff(@array, 1, @separator_position, '')


One of the great things about user-defined functions is that they can be used in a FROM clause. We can use this function like this:

Select *
from dbo.CsvToInt('1,5,11')

which returns


And going back to the original question we can write this code:

Declare @TheCSV varchar(100)
set @TheCSV = '1, 2 ,9, 17'

select P.ProductID, ProductName
from dbo.Products P
where p.ProductID in (Select IntValue 
		from dbo.CsvToInt(@TheCSV) )

This statement returns

ProductID   ProductName                              
----------- ---------------------------------------- 
1           Chai
2           Chang
9           Mishi Kobe Niku
17          Alice Mutton

This isn't exactly the format that was requested but it's close enough. Actually a JOIN is faster than an IN with a sub-select. That code looks like this:

select P.ProductID, ProductName
from dbo.Products P
JOIN dbo.CsvToInt(@TheCSV) CSV 
ON CSV.IntValue = P.ProductID

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.

Related Articles

Joining to the Next Sequential Row (2 April 2008)

Writing Outer Joins in T-SQL (11 February 2008)

How to Use GROUP BY with Distinct Aggregates and Derived tables (31 July 2007)

How to Use GROUP BY in SQL Server (30 July 2007)

Returning Complex Data from User-Defined Functions with CROSS APPLY (11 June 2007)

SQL Server 2005: Using OVER() with Aggregate Functions (21 May 2007)

Server Side Paging using SQL Server 2005 (4 January 2007)

Using XQuery, New Large DataTypes, and More (9 May 2006)

Other Recent Forum Posts

NULL IN List (6h)

When Dynamic Connection Fails, Package Succeeds but Job Fails (10h)

Backup failure with Linked Server to another MS SQL unavailable (12h)

Need Help to transpose the result in SQL (12h)

Views in restored MS Dynamics database return zero lines (15h)

DROP Table #TempTable (20h)

Unpivot (1d)

What variable type could I use in a trigger to read information from a text field (1d)

- Advertisement -