在 SQL IN 子句中按逗号分隔字符串参数

引言

我们经常在 SQL 查询中使用 IN 子句检查参数中用逗号分隔的值时遇到问题。

例如以下情况

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

它会产生类似以下的错误

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

为了解决这个问题，我编写了一个函数，可以解决此问题。

函数创建

首先在您的数据库中创建此函数。

            
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

将包含逗号分隔值的字符串传递给此函数。函数将返回一个包含一列和多行的表，记录与字符串值分隔。

现在如何使用此函数

现在将此函数实现到 SQL 查询或存储过程中。

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

参数 @sCategoryID 具有类似的值 '1,2,3,4,55,159,86,95'。因此，我们将此字符串作为参数传递给函数。该函数将此值作为表返回，SQL Server 事件处理程序将使用 IN 子句检查每个值。

我们通过将参数 @sCategoryID 作为字符串变量传递，该变量的值由逗号 (,) 分隔的字符串值，在 IN 子句中使用了此函数。请参见以下查询。

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