Please start any new threads on our new site at We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 Site Related Forums
 Article Discussion
 Article: Dynamic IN without Dynamic SQL

Author  Topic 

Ask SQLTeam Question

0 Posts

Posted - 2002-08-19 : 15:37:38
Dave submitted "A quick demonstration of a technique for using a comma delimited value in a SQL Server stored procedure that doesn't use dynamic sql."

Article Link.

Dr. Cross Join

7423 Posts

Posted - 2002-11-07 : 16:38:36
Try this for much easier method:

select * from TableOfIDs
charindex(',' + ID,',' + @IDList,1) <> 0

@IDList the comma-delimited list of ID's.

Adding a comma before both strings ensures that if you are looking for an ID of 15 you won't get 14415223 as well. If you are using ID's that are the same length, you don't need do append the comma.

Try it out, let me know what you think.

Go to Top of Page

Dave Kawliche
Starting Member

20 Posts

Posted - 2002-11-07 : 17:01:27
That is definitely a cool solution to the problem. The biggest drawback I can see is that it will always force a tablescan whereas the technique in the linked article can make use of an index-friendly inner join expression. If you just want to type less but still make use of indexes, I think graz's recent idea to wrap the csv to tablevar parsing into a UDF gives you the best of both worlds. See

best regards,

Dave Kawliche
Go to Top of Page

- Advertisement -