dwain.c (8/7/2012)
Personally I wouldn't use Dynamic SQL for this.My suggestion is to pass the two lists of values (@CriteriaA and @CriteriaB) as delimited strings and use a string splitter like Jeff Moden's (community) DelimitedSplit8K function to split them and pass that result into the IN.
+1000000
There are very few absolutes in sql server but directly executing a parameter is one of them. If you use the dynamic sql approach you took your procedure and made it instantly vulnerable to sql injection.
Since you posted in the 2008 forum I would suggest that and even better approach than parsing this is to use a table valued parameter instead.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/