Yesterday, I saw a post on asp.net forums about dynamic queries to handle where clause with a list of values. I’m not a fan of dynamic queries and would love to avoid it at any cost. So I started to see if there is a way around it. How about converting that list it to some temporary table or something and use it in JOIN. Finally came up with a table valued function which returns a table with values in the list as rows. We can use a INNER JOIN now instead of creating a dynamic “where coulumnname IN” query.
Create Function [dbo].[ParamTable] (@querystring varchar(1000))
Returns @ParamTable Table ( Row varchar(100))
As
Begin
;With T as (
select substring(@querystring,1,(case when charindex(’,',@querystring) = 0 then 1 else charindex(’,',@querystring) end) -1) SubStr,substring(@querystring,charindex(’,',@querystring)+1,len(@querystring)) Remainder
Union all
select substring(Remainder,1,(case charindex(’,',Remainder) when 0 then len(Remainder) + 1 Else charindex(’,',Remainder) end) -1) SubStr,case when charindex(’,',Remainder) = 0 then ” else substring(Remainder,charindex(’,',Remainder) + 1 ,len(Remainder)) end Remainder
From T
where substring(Remainder,1,(case charindex(’,',Remainder) when 0 then len(Remainder) + 1 Else charindex(’,',Remainder) end) -1) > ”
) Insert @ParamTable
Select SubStr From T
where substr <>”
Return
End
To test this, try this query:
Select * From ParamTable(’1,2,3,4,5′)
To use it in joins:
Original query:
Select * from Tablename Where Columnname in (’1,2,3,4′)
Equivalent using the function:
Select * from Tablename Inner join ParamTable(’1,2,3,4,5′) On Columnname=Row
Apparently this works for a list with 100 items, its a limitation of recursive common table expressions. Before i published, wanted to check if there are any better implementations. I was pretty sure someone might have already done this in a better way.
I was right. Here is a page with bunch of different implementations. I have to do some performance comparisons with my implementation and see how bad mine is, 
http://www.sommarskog.se/arrays-in-sql-2005.html
Would love to hear your comments.
