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:
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:
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.
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)
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.
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 -
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).
Your solution is what I would have recommended. The only change I would offer is to JOIN directly to the function rather than use IN. The optimizer should come up with the same plan but just like this a little better:
The only other thing I would be curious about is to see your parse function...
answered Nov 02, 2009 at 12:52 PM
For discussion on various "split functions" and their relative performance see:
answered Nov 03, 2009 at 10:24 AM
See if this article helps http://www.simple-talk.com/sql/t-sql-programming/faking-arrays-in-transact-sql/
answered Nov 02, 2009 at 11:30 AM