Split parameter string from comma seperator in SQL IN clause

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′)
)