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)
AS
begin

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

	declare @separator_position int 
	declare @array_value varchar(1000) 
	
	set @array = @array + ','
	
	while patindex('%,%' , @array) <> 0 
	begin
	
	  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, '')
	end

	return
end

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

IntValue    
----------- 
1
5
11

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.