x

Consolidated: SQL Pass comma separated values in SP for filtering

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.

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 -

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.

more ▼

asked Nov 02 '09 at 10:56 AM in Default

Hmnt gravatar image

Hmnt
43 3 5 5

(comments are locked)
10|1200 characters needed characters left

3 answers: sort voted first

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:

SELECT i.<column List>            
FROM   Item i            
inner join [dbo].[GetTableFromValues](@ItemCodes) ic            
       on ic.<output col> = i.ItemCode            

The only other thing I would be curious about is to see your parse function...

more ▼

answered Nov 02 '09 at 12:52 PM

TG gravatar image

TG
1.8k 1 3

(comments are locked)
10|1200 characters needed characters left

For discussion on various "split functions" and their relative performance see:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=50648

more ▼

answered Nov 03 '09 at 10:24 AM

Kristen gravatar image

Kristen ♦
2.2k 6 7 10

(comments are locked)
10|1200 characters needed characters left
more ▼

answered Nov 02 '09 at 11:30 AM

Madhivanan gravatar image

Madhivanan
1.1k 1 3 6

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x402
x34
x27
x22

asked: Nov 02 '09 at 10:56 AM

Seen: 3532 times

Last Updated: Nov 03 '09 at 10:18 AM