Using a delimited list for SQL ‘IN’ statement

Sometimes I want to pass a list of items from my C# code to my stored procedures so they can be used as part of an ‘IN’ clause.

In my greener years I would do something like:

Unsafe Way

<WARNING>Do NOT write code like this. This opens the door to SQL injection. </WARNING>

   1: -- @ProductIDs contains a list of id's delimited by a ',' (i.e. '1,34,56,7,8,98')

   2:  

   3: sp_executesql 'SELECT * FROM Products WHERE ProductID IN (' + @ProductIDs + ')'

Safer Way

My safer solution is adding a function to my database called “SPLIT.”

Function:

   1: CREATE FUNCTION [dbo].[SPLIT] (

   2:     @str_in VARCHAR(8000),

   3:     @separator VARCHAR(4) )

   4: RETURNS @strtable TABLE (strval VARCHAR(8000))

   5:  

   6: AS

   7:  

   8: BEGIN

   9:     DECLARE

  10:         @Occurrences INT,

  11:         @Counter INT,

  12:         @tmpStr VARCHAR(8000)

  13:     

  14:     SET @Counter = 0

  15:  

  16:     IF SUBSTRING(@str_in,LEN(@str_in),1) <> @separator 

  17:         SET @str_in = @str_in + @separator

  18:  

  19:     SET @Occurrences = (DATALENGTH(REPLACE(@str_in,@separator,@separator+'#')) - DATALENGTH(@str_in))/ DATALENGTH(@separator)

  20:     SET @tmpStr = @str_in

  21:  

  22:     WHILE @Counter <= @Occurrences 

  23:     BEGIN

  24:         SET @Counter = @Counter + 1

  25:         INSERT INTO @strtable

  26:             VALUES ( SUBSTRING(@tmpStr,1,CHARINDEX(@separator,@tmpStr)-1))

  27:  

  28:         SET @tmpStr = SUBSTRING(@tmpStr,CHARINDEX(@separator,@tmpStr)+1,8000)

  29:  

  30:         IF DATALENGTH(@tmpStr) = 0

  31:  

  32:         BREAK

  33:     END

  34:  

  35: RETURN 

  36: END

This function takes a “str_in” parameter representing the delimited String, and separator parameter representing the delimiter of the string.

Using these two values, the delimited string is broken apart into a table that can be queried.

Usage:

   1: SELECT *

   2: FROM Products

   3: WHERE

   4: ProductID IN ( SELECT * FROM SPLIT(@ProductIDs, ','))

I’m sure there is a more efficient way to perform the same task, but it works well enough for my needs. However, I am open to suggestions.

 

Happy Coding,

Justin