Using a CSV with an IN sub-select

By Bill Graziano on 13 October 2002 | 22 Comments | 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.

Discuss this article: 22 Comments so far. Print this Article.

If you like this article you can sign up for our weekly newsletter. There's an opt-out link at the bottom of each newsletter so it's easy to unsubscribe at any time.

Email Address:

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

i need to group by adding the quantity (0 Replies)

comparing dates (2 Replies)

Join 2 select Statements (3 Replies)

which is small and which is large data (1 Reply)

add new columns to create script of a database (1 Reply)

Change Row Value as Column Header (1 Reply)

What are First Steps for dba in new Office? (4 Replies)

table type (3 Replies)

Subscribe to SQLTeam.com

Weekly SQL Server newsletter with articles, forum posts, and blog posts via email. Subscribers receive our white paper with performance tips for developers.

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -