Click here to Skip to main content
12,630,410 members (28,073 online)
Click here to Skip to main content
Add your own
alternative version

Tagged as

Stats

302.7K views
191 downloads
19 bookmarked
Posted

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?

You may also be interested in...

Pro
Pro

Comments and Discussions

 
QuestionAnother option Pin
Pittsburger19-Dec-15 19:41
professionalPittsburger19-Dec-15 19:41 
QuestionUsage of Left function in your code Pin
ganesh.dks7-Jun-14 6:36
memberganesh.dks7-Jun-14 6:36 
AnswerRe: Usage of Left function in your code Pin
OriginalGriff7-Jun-14 6:47
protectorOriginalGriff7-Jun-14 6:47 
GeneralRe: Usage of Left function in your code Pin
ganesh.dks7-Jun-14 10:35
memberganesh.dks7-Jun-14 10:35 
Questionvery nice article. Have one question. Pin
bhumika_singh3-Jun-14 23:22
memberbhumika_singh3-Jun-14 23:22 
QuestionUseful, but a question remains... Pin
FrankEBailey14-Apr-14 9:26
memberFrankEBailey14-Apr-14 9:26 
AnswerRe: Useful, but a question remains... Pin
OriginalGriff15-Apr-14 1:48
protectorOriginalGriff15-Apr-14 1:48 
GeneralThanks for the code Pin
Reneq26-Apr-14 19:25
memberReneq26-Apr-14 19:25 
QuestionThank you. This article really helped. Pin
Uma MS24-Mar-14 1:38
memberUma MS24-Mar-14 1:38 
Generalreally helps me.. Pin
anfil18-Jan-14 1:40
memberanfil18-Jan-14 1:40 
GeneralRe: really helps me.. Pin
OriginalGriff18-Jan-14 1:41
protectorOriginalGriff18-Jan-14 1:41 
QuestionNice Tip Pin
Simon_Whale3-Dec-13 2:47
professionalSimon_Whale3-Dec-13 2:47 
AnswerRe: Nice Tip Pin
OriginalGriff3-Dec-13 6:49
protectorOriginalGriff3-Dec-13 6:49 
QuestionVery usee full Pin
ankit suthar23-Oct-13 21:33
memberankit suthar23-Oct-13 21:33 
QuestionMy Vote of 5 Pin
Amol_B9-Jul-13 1:15
professionalAmol_B9-Jul-13 1:15 
AnswerRe: My Vote of 5 Pin
OriginalGriff9-Jul-13 1:24
protectorOriginalGriff9-Jul-13 1:24 
GeneralRe: My Vote of 5 Pin
Amol_B9-Jul-13 1:37
professionalAmol_B9-Jul-13 1:37 
GeneralMy vote of 5 Pin
Nandakishorerao23-Jun-13 20:23
memberNandakishorerao23-Jun-13 20:23 
Suggestionone change Pin
smonpara8-May-13 1:43
membersmonpara8-May-13 1:43 
GeneralRe: one change Pin
NewAmbition9-Oct-14 0:29
professionalNewAmbition9-Oct-14 0:29 
GeneralMy vote of 5 Pin
croscwa28-Apr-13 3:49
membercroscwa28-Apr-13 3:49 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    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
Web01 | 2.8.161205.3 | Last Updated 7 May 2013
Article Copyright 2013 by OriginalGriff
Everything else Copyright © CodeProject, 1999-2016
Layout: fixed | fluid