Click here to Skip to main content
11,720,324 members (81,222 online)
Click here to Skip to main content

Split parameter string from comma seperator in SQL IN clause

, 4 Nov 2008 CPOL 78.9K 24
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

You may also be interested in...

Comments and Discussions

 
GeneralAnother technique for same problem [without need of any function] Pin
kamii475-Nov-08 1:46
memberkamii475-Nov-08 1:46 
GeneralRe: Another technique for same problem [without need of any function] Pin
parybaba26-Sep-10 21:57
memberparybaba26-Sep-10 21:57 
GeneralRe: Another technique for same problem [without need of any function] Pin
kamii4727-Sep-10 3:44
memberkamii4727-Sep-10 3:44 
GeneralRe: Another technique for same problem [without need of any function] Pin
Edwin Jose G28-Mar-11 20:53
memberEdwin Jose G28-Mar-11 20:53 

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.150901.1 | Last Updated 4 Nov 2008
Article Copyright 2008 by Vimal Panara
Everything else Copyright © CodeProject, 1999-2015
Layout: fixed | fluid