Using comma separated value parameter strings in SQL IN clauses

• Download CSVDemo.SQL - 0.6 KB

Introduction 

The SQL IN clause is very useful, since it allows you to specify exactly which values you want to return.

For this tip,  let's assume we have a database with this table:

CREATE TABLE [dbo].[CSVDemo](
	[Id] [int] NOT NULL,
	[Descr] [varchar](50) NOT NULL
) ON [PRIMARY]
GO

And this data:

Id	Descr
1	The first row
2	The second row
3	Another row
4	The final row

We can specify the rows we want to very easily:

SELECT Id, Desc FROM CSVDemo WHERE Id IN (1, 3)

And we get the data we expect:

Id	Descr
1	The first row
3	Another row

But what if we try to give it a parameter or other variable string list?

DECLARE @LIST VARCHAR(200)
SET @LIST = '1,3'
SELECT Id, Descr FROM CSVDemo WHERE Id IN (@LIST)

Answer:

Msg 245, Level 16, State 1, Line 3
Conversion failed when converting the varchar value '1,3' to data type int.

Which is SQL-speak for "you can't do that!"

So what can we do? 

We can't do it, because SQL has no concept of Lists, or array or other useful data structures - it only knows about tables (and table based information) so it converts the string list into a table structure when it compiles the command - and it can't compile a variable string, so it complains and you get annoyed. Or at least, I do.

What we have to do is convert the comma separated values into a table first. My initial version was inline, and rather messy, so I re-worked it to a user function and made it a bit more general purpose. 

USE [Testing] GO

/****** Object:  UserDefinedFunction [dbo].[CSVToTable]    Script Date: 04/28/2013 10:45:17 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE FUNCTION [dbo].[CSVToTable] (@InStr VARCHAR(MAX))
RETURNS @TempTab TABLE
   (id int not null)
AS
BEGIN
    ;-- Ensure input ends with comma
	SET @InStr = REPLACE(@InStr + ',', ',,', ',')
	DECLARE @SP INT
DECLARE @VALUE VARCHAR(1000)
WHILE PATINDEX('%,%', @INSTR ) <> 0 
BEGIN
   SELECT  @SP = PATINDEX('%,%',@INSTR)
   SELECT  @VALUE = LEFT(@INSTR , @SP - 1)
   SELECT  @INSTR = STUFF(@INSTR, 1, @SP, '')
   INSERT INTO @TempTab(id) VALUES (@VALUE)
END
	RETURN
END
GO

This creates a user function that takes a comma separated value string and converts it into a table that SQL does understand - just pass it the sting, and it works it all out. It's pretty obvious how it works, the only complexity is the REPLACE part which ensures the string is terminated with a single comma by appending one, and removing all double commas from the string. Without this, while loop becomes harder to process, as the final number might or might not have a terminating comma and that would have to be dealt with separately.

Using the code

Simple:

DECLARE @LIST VARCHAR(200)
SET @LIST = '1,3'
SELECT Id, Descr FROM CSVDemo WHERE Id IN (SELECT * FROM dbo.CSVToTable(@LIST))

History

• 2013 Apr 28: First version.