Click here to Skip to main content
Click here to Skip to main content
Go to top

Split parameter string from comma seperator in SQL IN clause

, 4 Nov 2008
Rate this:
Please Sign up or sign in to vote.
Many time we face problem in SQL Query when performing operation with IN clause to check values seperated by ,(commas) in a parameter.

Introduction

Many time we faced a problem in SQL Query when performing operation with IN clause to check values seperated by commas in a parameter.

like the following

SELECT * FROM TblJobs WHERE iCategoryID IN (’1,2,3,4,5′)

it gives error like

Msg 8114, Level 16, State 5, Line 1
Error converting data type varchar to int.

To overcome this problem here I have written one function that will resolve this issue.

Function Creation:

First create this function in your database.

            
IF EXISTS(SELECT * FROM sysobjects WHERE ID = OBJECT_ID(’UF_CSVToTable’))
 DROP FUNCTION UF_CSVToTable
GO

CREATE FUNCTION UF_CSVToTable
(
 @psCSString VARCHAR(8000)
)
RETURNS @otTemp TABLE(sID VARCHAR(20))
AS
BEGIN
 DECLARE @sTemp VARCHAR(10)

 WHILE LEN(@psCSString) > 0
 BEGIN
  SET @sTemp = LEFT(@psCSString, ISNULL(NULLIF(CHARINDEX(',', @psCSString) - 1, -1),
                    LEN(@psCSString)))
  SET @psCSString = SUBSTRING(@psCSString,ISNULL(NULLIF(CHARINDEX(',', @psCSString), 0),
                               LEN(@psCSString)) + 1, LEN(@psCSString))
  INSERT INTO @otTemp VALUES (@sTemp)
 END

RETURN
END
Go

Pass a string with comma seperated values in this function. Function will return table with one column and multiple rows as record separated with string value.

Now how to use this function:

Now implement this function into SQL Query or Procedure.

CREATE PROCEDURE TEMP_SP_RETRIVE_JOBS
 @sCategoryID VARCHAR(5000)
AS
BEGIN
 SELECT *
 FROM
  TblJobs
 WHERE
  iCategoryID IN (SELECT * FROM UF_CSVToTable(@sCategoryID))
END
GO

Parameter @sCategoryID has values like '1,2,3,4,55,159,86,95'. So this string we are passing into the function as a parameter. And this function will return this value as a table and SQL Server event process will check each value with IN clause.

We have used this function in IN Clause by passing parameter @sCategoryID as string variable with value as string value separated by comma sign(,). See the following query.

SELECT * FROM tblJobs WHERE iCategoryID IN (
   select * from UF_CSVToTable(’1,2,3,4,5,6,7,15,55,59,86,95′)
)

License

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

Share

About the Author

Vimal Panara
Technical Lead IndiaNIC Infotech Ltd
India India
No Biography provided

Comments and Discussions

 
GeneralMy vote of 5 PinmemberRicha Sharma from Chandigarh24-Feb-11 22:12 

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 | Mobile
Web02 | 2.8.140926.1 | Last Updated 4 Nov 2008
Article Copyright 2008 by Vimal Panara
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid