I got into yet another issue with my SQL optimizations this week at work and one of them was that I needed to pass a list of IDs to a function that would be then used in a SQL statement via an “in” clause.  The problem is that the list argument is a varchar:

   1: @docTypeIds varchar(500) = '0',

 

Then I needed to use it in my SQL statement like this:

   1: SELECT *

   2: FROM table

   3: WHERE (

   4:   (@docTypeIds = '0' OR @docTypeIds = '')

   5:   OR

   6:   docType in (@docTypeIds)  

   7: )

The problem with this approach is that the datatype for the docType column was tinyint and then the argument is a varchar list, so it would fail as it cannot cast it. Bummer!  So what to do?  Well, after some searching and searching, the best idea so far (brought up by our local dev guru Sanjib Panda), was to break the list into a simple table with one column of type tinyint, but how?  Here is the simple logic which could easily be created as a UDF:

   1: declare @testString as varchar(20)

   2: set @testString = '1,2,3,4,5,6,7,8,9'

   3:  

   4: declare @tempTable table

   5: (

   6: docType tinyint

   7: )

   8:  

   9: while charindex(',',@testString) > 0

  10: begin

  11:   INSERT INTO @tempTable select substring(@testString,1,1)

  12:   SET @testString = substring(@testString,3,len(@testString))

  13: end

  14:  

  15: select * from @tempTable

So if you run the above, that will give you our temp table with one docType column with all the values we needed.  Man, sometimes I wish SQL could have some ColdFusion goodness and create a cool function like: listToTable() or something.  Anyways, after splitting the list into a table then I can change my SQL to something like this:

   1: AND(

   2:   (@docTypeIds = '0' OR @docTypeIds = '')

   3:   OR

   4:   d.docType in ( select docType from @tempTable )  

   5: )

There you go! The solution is working really fast and given us a nice little split to rows function that can be used on any variable.  Hope this helps.

 

UPDATE

After much trying out and pains, what I had did not work fully :(  So here is an updated function:

   1: -- Temp tables for lists

   2: DECLARE @docTypes TABLE( docType tinyint )

   3: DECLARE @docTypeIds varchar(300)

   4: DECLARE @delimIndex int

   5:  

   6: set @docTypeIds = '11,1,3,54'

   7:     

   8: -- If docTypes sent in, then convert to table rows

   9: IF LEN(@docTypeIds) > 0 AND @docTypeIds <> '0'

  10: BEGIN

  11:     -- Get Delimiter index

  12:     SET @delimIndex = CHARINDEX(',',@docTypeIds)

  13:     -- We have at least one delimiter, start on it

  14:     WHILE @delimIndex > 0

  15:     BEGIN

  16:         INSERT INTO @docTypes SELECT SUBSTRING(@docTypeIds,1,@delimIndex-1)

  17:         SET @docTypeIds = SUBSTRING(@docTypeIds,@delimIndex+1,LEN(@docTypeIds))

  18:         SET @delimIndex = CHARINDEX(',',@docTypeIds)

  19:     END

  20:     -- Insert the last entry or the first single no delim entry

  21:     INSERT INTO @docTypes SELECT @docTypeIds

  22: END

  23:  

  24: select * from @docTypes