The question of getting a stored procedure to take a string like '12,13,14,16', we'll say @CommaDelimitedString and then turning that into something like the select statement below has been asked a lot from googling a bit.
select X from Y where X in(@CommaDelimitedString)
Clearly the above does not work so you have to choose from breaking up the string into a table by parsing or looping or to use dynamic SQL.
Dynamic SQL is the tidiest by far but obviously has some injection risk but from a performance perspective which approach would be best?
In my scenario the table being scanned is 4-5 million records long. Would avoiding dynamic SQL allow the stored procedure to optimize more effectively and therefore justify a lengthier parsing stage or would this be virtually pointless?
Perhaps someone can comment.
I am using SQL server 2012.