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