Tip SQL Parameter And The IN Operator

[rcombs4]

I know in the past I've wanted to pass a comma separated variable to a SP and have it be used in an IN statement. Before I found this solution I did some pretty stupid tactics to make it work the way I wanted. But now I found a solution that will work the way I want it to. And unfortunately I don't remember where I found it.

First we need to create a Table-valued Function. We call it SeparateValues, and we really don't need to care how it works but what it does is make a table with one column and each row is one of the values you pass in. The function takes two arguments. The first is the data the second is the delimiter.

CREATE Function [dbo].[SeparateValues]
(
    @data VARCHAR(MAX),
    @delimiter VARCHAR(10) 
) 
    RETURNS @tbldata TABLE(col VARCHAR(100))
As
Begin
    DECLARE @pos INT
    DECLARE @prevpos INT

    SET @pos = 1 
    SET @prevpos = 0

    WHILE @pos > 0 
        BEGIN

        SET @pos = CHARINDEX(@delimiter, @data, @prevpos+1)

        if @pos > 0 
        INSERT INTO @tbldata(col) VALUES(LTRIM(RTRIM(SUBSTRING(@data, @prevpos+1, @pos-@prevpos-1))))

        else

        INSERT INTO @tbldata(col) VALUES(LTRIM(RTRIM(SUBSTRING(@data, @prevpos+1, len(@data)-@prevpos))))

        SET @prevpos = @pos 
    End

    RETURN

END

So now if we have a list of IDs like:
15,18,38,18,168

We can pass that as a string as the parameter @IDList

Then in our SP we do...

SELECT *
FROM TableName
WHERE ID IN (SELECT Col FROM [SeparateValues](@IDList, ','))

I know there are probably other ways to do this but this is the best I found or could come up with.