nazham.com

I have one problem the other day; Why does my “SELECT * FROM tbl WHERE col IN (@list)” does not work? The col datatype is int, while the @list parameter is a comma-separated varchar.

The Problem
In a stored procedure, I’m composing a SELECT statement with a WHERE clause in which the column condition datatype is integer, while the parameter supplied to it has the datatype of varchar:

CREATE PROCEDURE get_product_names @ids varchar(50) AS
SELECT ProductID, ProductName
FROM Northwind..Products
WHERE ProductID IN (@ids)

ProductID is int, @ids is a comma-separated string, i.e.: ‘9,12,27,37’.

When try to call:
EXEC get_product_names '9,12,27,37'

But this fails with:
Server: Msg 245, Level 16, State 1, Procedure get_product_names, Line 2
Syntax error converting the varchar value '9,12,27,37' to a column of data type int.

Then comes the solution, convert the comma-separated string to a recordset in a temporary table.

The Solution
There’s quite a number of solutions out there, but I’ve found a pretty good ways along with a very comprehensive explainations here. The one that I’m implementing is called the Iterative Method.

Create a new User Defined Function:

CREATE FUNCTION iter_intlist_to_table (@list ntext)
      RETURNS @tbl TABLE (listpos int IDENTITY(1, 1) NOT NULL,
                          number  int NOT NULL) AS
   BEGIN
      DECLARE @pos      int,
              @textpos  int,
              @chunklen smallint,
              @str      nvarchar(4000),
              @tmpstr   nvarchar(4000),
              @leftover nvarchar(4000)

      SET @textpos = 1
      SET @leftover = ''
      WHILE @textpos <= datalength(@list) / 2
      BEGIN
         SET @chunklen = 4000 - datalength(@leftover) / 2
         SET @tmpstr = ltrim(@leftover + substring(@list, @textpos,
                                 @chunklen))
         SET @textpos = @textpos + @chunklen

         SET @pos = charindex(',', @tmpstr)
         WHILE @pos > 0
         BEGIN
            SET @str = substring(@tmpstr, 1, @pos - 1)
            INSERT @tbl (number) VALUES(convert(int, @str))
            SET @tmpstr = ltrim(substring(@tmpstr, @pos + 1,
                                 len(@tmpstr)))
            SET @pos = charindex(',', @tmpstr)
         END

         SET @leftover = @tmpstr
      END

      IF ltrim(rtrim(@leftover)) <> ''
         INSERT @tbl (number) VALUES(convert(int, @leftover))

      RETURN
   END

Modify the Stored Procedure to the one below:

CREATE PROCEDURE get_product_names @ids varchar(50) AS
      SELECT ProductID, ProductName
      FROM   Northwind..Products
      WHERE  ProductID IN (SELECT number FROM
                       dbo.iter_intlist_to_table(@ids))
   go

Execute the Stored Procedure:
EXEC get_product_names_iter '9,12,27,37'

Now the stored procedure will happily accepts the comma-separated varchar parameter, and returns the records needed. Is there any other method to this? Feel free to share. 🙂