Click here to Skip to main content
Click here to Skip to main content

Tagged as

Using comma separated value parameter strings in SQL IN clauses

, 7 May 2013 CPOL
Rate this:
Please Sign up or sign in to vote.
Passing a comma separated list of values to an SQL query is all very well, but you can't just use them in an "IN" clause. This helps.

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.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

OriginalGriff
CEO
Wales Wales
Born at an early age, he grew older. At the same time, his hair grew longer, and was tied up behind his head.
Has problems spelling the word "the".
Invented the portable cat-flap.
Currently, has not died yet. Or has he?
Follow on   Google+

Comments and Discussions

 
QuestionUsage of Left function in your code Pinmemberganesh.dks7-Jun-14 6:36 
AnswerRe: Usage of Left function in your code PinprotectorOriginalGriff7-Jun-14 6:47 
GeneralRe: Usage of Left function in your code Pinmemberganesh.dks7-Jun-14 10:35 
Questionvery nice article. Have one question. Pinmemberbhumika_singh3-Jun-14 23:22 
QuestionUseful, but a question remains... PinmemberFrankEBailey14-Apr-14 9:26 
AnswerRe: Useful, but a question remains... PinprotectorOriginalGriff15-Apr-14 1:48 
GeneralThanks for the code PinmemberReneq26-Apr-14 19:25 
QuestionThank you. This article really helped. PinmemberUma MS24-Mar-14 1:38 
Generalreally helps me.. Pinmemberanfil18-Jan-14 1:40 
GeneralRe: really helps me.. PinprotectorOriginalGriff18-Jan-14 1:41 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Terms of Use | Mobile
Web02 | 2.8.150129.1 | Last Updated 7 May 2013
Article Copyright 2013 by OriginalGriff
Everything else Copyright © CodeProject, 1999-2015
Layout: fixed | fluid