David Glover
asked on
Passing a string of comma seperated values to use in a stored procedure as part of an IN statement.
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.
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.
ASKER
Thanks Kyle, is this likely to outperform dynamic SQL?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks Scott, I adopted this in my solution!
Regards,
Regards,
select X from y where X in
(select * from dbo.fn_txt_split(@CommaDel
Open in new window