Building SQL Statements with multiple values for the "IN" CLAUSE

You would think that SQL would be able to have a easy way where you could pass it a simple string, with some values separated by commas, and it could throw that right into a sql statement like:

SELECT *
FROM Clients
WHERE ClientID IN (1,5,6)

However, this just doesn't work in sql.  In fact, there is a very complex solution, which involves creating a function and then passing in you values separated by spaces as a NVARCHAR.

The function looks like this


FUNCTION [dbo].[iter_intlist_to_tbl] (@list nvarchar(MAX))
   RETURNS @tbl TABLE (listpos int IDENTITY(1, 1) NOT NULL,
                       number  int NOT NULL) AS
BEGIN
   DECLARE @startpos int,
           @endpos   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 @startpos = 0
      SET @endpos = charindex(' ' COLLATE Slovenian_BIN2, @tmpstr)

      WHILE @endpos > 0
      BEGIN
         SET @str = substring(@tmpstr, @startpos + 1,
                              @endpos - @startpos - 1)
         IF @str <> ''
            INSERT @tbl (number) VALUES(convert(int, @str))
         SET @startpos = @endpos
         SET @endpos = charindex(' ' COLLATE Slovenian_BIN2,
                                 @tmpstr, @startpos + 1)
      END

      SET @leftover = right(@tmpstr, datalength(@tmpstr) / 2 - @startpos)
   END

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

   RETURN
END

Pretty crazy huh.  All this does is pretty much build a table with the unique values you throw into it, so you can join on those values with the column you are trying to get the data from, and pull out the rows where any of the values match any of the rows in that table.

You utilize this function like so:


PROCEDURE [dbo].[Client_Get_All_By_ClientIDs]

@clientIDs NVARCHAR(20)

AS


SELECT *
  FROM Client
  WHERE ClientID IN
  (SELECT i.number from iter_intlist_to_tbl(@ClientIDs) i)

Then you run it like this

EXEC [dbo].[Client_Get_All_By_ClientIDs] '2 4 7 190'


And there it is.  That is the simple way to do a mutiple IN CLAUSE SQL statement.  Remember that if you are building your values to input into the stored procedure, to separate them by spaces " ", and NOT commas ",".