Consolidated: SQL Pass comma separated values in SP for filtering

Question

I'm here to share a consolidated analysis for the following scenario:

I've an 'Item' table and I've a search SP for it. I want to be able to search for multiple ItemCodes like:

- Table structure : Item(Id INT, ItemCode nvarchar(20))
- Filter query format: SELECT * FROM Item WHERE ItemCode IN ('xx','yy','zz')

I want to do this dynamically using stored procedure. I'll pass an @ItemCodes parameter which will have comma(',') separated values and the search shud be performed as above.

---

Well, I've already visited lot of posts\forums and here're some threads:

• Dynamic SQL might be a least complex way but I don't want to consider it because of the parameters like performance,security (SQL-Injection, etc..)..

Also other approaches like XML, etc.. if they make things complex I can't use them.

And finally, no extra temp-table JOIN kind of performance hitting tricks please. I've to manage the performance as well as the complexity.

• T-SQL stored procedure that accepts multiple Id values

• Passing an "in" list via stored procedure I've reviewed the above two posts and gone thru some solutions provided, here're some limitations:

http://www.sommarskog.se/arrays-in-sql-2005.html This will require me to 'declare' the parameter-type while passing it to the SP, it distorts the abstraction (I don't set type in any of my parameters because each of them is treated in a generic way)

http://www.sqlteam.com/article/sql-server-2008-table-valued-parameters This is a structured approach but it increases complexity, required DB-structure level changes and its not abstract as above.

http://madprops.org/blog/splitting-text-into-words-in-sql-revisited/ Well, this seems to match-up with my old solutions. Here's what I did in the past -

I created an SQL function : [GetTableFromValues] (returns a temp table populated each item (one per row) from the comma separated @ItemCodes)

And, here's how I use it in my WHERE caluse filter in SP -

SELECT * FROM Item WHERE ItemCode in (SELECT * FROM[dbo].[GetTableFromValues](@ItemCodes))

This one is reusable and looks simple and short (comparatively of course). Anything I've missed or any expert with a better solution (obviously 'within' the limitations of the above mentioned points).

Thank you.

Answer 1

I think using dynamic T-SQL will be pragmatic in this scenario. If you are careful with the design, dynamic sql works like a charm. I have leveraged it in countless projects when it was the right fit. With that said let me address your two main concerns - performance and sql injection.

With regards to performance, read T-SQL reference on parameterized dynamic sql and sp_executesql (instead of sp_execute). A combination of parameterized sql and using sp_executesql will get you out of the woods on performance by ensuring that query plans are reused and sp_recompiles avoided! I have used dynamic sql even in real-time contexts and it works like a charm with these two items taken care of. For your satisfaction you can run a loop of million or so calls to the sp with and without the two optimizations, and use sql profiler to track sp_recompile events.

Now, about SQL-injection. This will be an issue if you use an incorrect user widget such as a textbox to allow the user to input the item codes. In that scenario it is possible that a hacker may write select statements and try to extract information about your system. You can write code to prevent this but I think going down that route is a trap. Instead consider using an appropriate user widget such as a listbox (depending on your frontend platform) that allows multiple selection. In this case the user will just select from a list of "presented items" and your code will generate the string containing the corresponding item codes. Basically you do not pass user text to the dynamic sql sp! You can even use slicker JQuery based selection widgets but the bottom line is that the user does not get to type any unacceptable text that hits your data layer.

Next, you just need a simple stored procedure on the database that takes a param for the itemcodes (for e.g. '''xyz''','''abc'''). Internally it should use sp_executesql with a parameterized dynamic query.

I hope this helps. -Tabrez