Thursday, October 29, 2009

Stored Procedure: Dynamic IN Clause Solution

I'm sure many database programmers have run across the issue of wanting to pass a delimited string of values as a parameter, then using that in your IN clause.  Example:

CREATE PROCEDURE MyProc @StringOfValues VARCHAR(200)
AS

SELECT *
FROM table
WHERE column IN (@StringOfValues)

To execute, you do the following:
EXEC MyProc '1,2,3'

If the column data type is an integer, you will likely get the error "Conversion failed when converting the varchar value '1,2,3' to data type int."  Now, you could certainly create a string for the SQL statement - which is a pain to say the least - then EXEC(thestring), but let's look at a nice alternative to that.

First, you will need a table valued function that allows you to pass in a delimited list of values:
CREATE FUNCTION [dbo].[ParamParser]
    (
     @delimString VARCHAR(255)
    ,@delim CHAR(1)
    )
RETURNS @paramtable TABLE ( id INT )
AS BEGIN
    DECLARE @len INT
       ,@index INT
       ,@nextindex INT

    SET @len = DATALENGTH(@delimString)
    SET @index = 0
    SET @nextindex = 0

    WHILE ( @len >= @index )
        BEGIN

            SET @nextindex = CHARINDEX(@delim, @delimString, @index)

            IF ( @nextindex = 0 )
                SET @nextindex = @len + 2

            INSERT  @paramtable
                    SELECT  SUBSTRING(@delimString, @index, @nextindex - @index)

            SET @index = @nextindex + 1

        END
    RETURN
   END

Now that you have that table valued function, you can rewrite your stored procedure as such:
CREATE PROCEDURE MyProc @StringOfValues VARCHAR(200)
AS

SELECT *
FROM table
INNER JOIN dbo.ParamParser(@StringOfValues,',') tvf ON table.column = tvf.id

When you then execute your stored procedure call, you get back the results as expected - filtered accordingly on your delimited string of values.