How can I pass a comma-separated list of integers from PHP as a parameter to a stored procedure in 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.

Perhaps: http://stackoverflow.com/questions/337704/parameterizing-an-sql-in-clause — Fionnuala, Jan 30 '13 at 22:55

score 2 · Accepted Answer · answered Jan 31 '13 at 05:54

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, ','))

score 0 · Answer 2 · answered Jan 30 '13 at 22:59

You can pass a comma separate list of values. However, you cannot use them as you like in an in statement. You can do something like this instead:

where ','+@List+',' like '%,'+PrimaryKey+',%'

That is, you like to see if the value is present. I'm using SQL Server syntax for concatenation because the question is tagged Microsoft.

How can I pass a comma-separated list of integers from PHP as a parameter to a stored procedure in SQL Server 2008?

2 Answers2