如何将PHP中作为参数的逗号分隔整数列表传递给SQL Server 2008中的存储过程？

Question

Hopefully I'm going about this the right way, if not I'm more than open to learning how this could be done better.

I need to pass a comma separated list of integers (always positive integers, no decimals) to a stored procedure. The stored procedure would then use the integers in an IN operator of the WHERE clause:

WHERE [PrimaryKey] IN (1,2,4,6,212);

The front-end is PHP and connection is made via ODBC, I've tried wrapping the parameter in single quotes and filtering them out in the stored procedure before the list gets to the query but that doesn't seem to work.

The error I'm getting is:

Conversion failed when converting the varchar value '1,2,4,6,212' to data type int.

I've never done this before and research so far has yielded no positive results.

Answer 1

Firstly, let's use a SQL Function to perform the split of the delimited data:

CREATE FUNCTION dbo.Split
(
    @RowData nvarchar(2000),
    @SplitOn nvarchar(5)
)  
RETURNS @RtnValue table 
(
    Id int identity(1,1),
    Data nvarchar(100)
) 
AS  
BEGIN 
    Declare @Cnt int
    Set @Cnt = 1

    While (Charindex(@SplitOn,@RowData)>0)
    Begin
        Insert Into @RtnValue (data)
        Select 
            Data = ltrim(rtrim(Substring(@RowData,1,Charindex(@SplitOn,@RowData)-1)))

        Set @RowData = Substring(@RowData,Charindex(@SplitOn,@RowData)+1,len(@RowData))
        Set @Cnt = @Cnt + 1
    End

    Insert Into @RtnValue (data)
    Select Data = ltrim(rtrim(@RowData))

    Return
END

To use this, you would simply pass the function the delimited string as well as the delimiter, like this:

SELECT
  *
FROM
  TableName
WHERE
  ColumnName IN (SELECT Data FROM dbo.Split(@DelimitedData, ','))

If you still have issues, due to the datatype, try:

SELECT
  *
FROM
  TableName
WHERE
  ColumnName IN (SELECT CONVERT(int,Data) FROM dbo.Split(@DelimitedData, ','))