Jeff Moden (8/12/2010)
Someone already said it... I'd love to read that article. Would you post the URL on this thread so we can take a peek? Thanks, Joe.

Adam Gojdas (8/13/2010)

---

Maybe this is the article he was referring to:
http://www.simple-talk.com/sql/learn-sql-server/values%28%29-and-long-parameter-lists/

SQLZ (8/13/2010)

---

This was an interesting article with an even more interesting discussion.

The thing I'd like to pick up on, that drew my attention, was your reasons for moving away from the loop method. You commented that you found there was too much coding for so little functionality.

The interesting thing though, is that the loop method performs as much as 5 times faster than the alternative method you provided. This becomes apparent when you consider 4000 different states.

Now I come across this a lot. I'll look at a piece of code that a developer has produced and I'll wonder why they've adopted a certain approach - very often the answer is "it's a neater solution". My approach is, "sure, but is it a fast solution"?

Not withstanding the already mentioned solutions that others have commented on that improve on the loop method, and not withstanding that your intention was probably never to deal with 100's or thousands of states, in my book, if a procedure has 5 lines more code but gives me performance gain by a factor of 5 then it's worth keeping those extra lines. Heck, for 5-times the performance, give me 100 extra lines of code ;).

dbuendiab (8/15/2010)

---

I was about to answer Jeff about performance question and your post lights a little the arguments a wanted to expose.

One thing I've learned with this my first writting experience is the range of levels your readers have, and the different approach everyone has when reading some other point of view.

Having said this, let me tell you why I did not insist on performance issue. As someone stated, Erland's work on arrays is the ultimate reference on methods to split a string of parameters. No substantial things can be added on this question, I think. Moreover, the method I expose is really one of the worst, if you read his work.

As I teach SQL from the last year on, the big trouble I've seen in my students is to adopt the set based thinking. They all come from procedural languages, they all are clever and should find how to split a string by the loop method I discard. Why I do it - to show them how to think in SQL. It took me several months to get it, and I think that's the key point to leverage their SQL skills.

That is, performance is a concept every programmer, procedural or not, can adquire naturally. SQL thinking is not so straight forward, though. Sadly, in my writting all this considerations where implicit, as I had only my point of view in mind. That's why I thank from heart received criticism, particularly those of Jeff, a little radical in style but right in the bottom.

OFF TOPIC> I'm on holidays, I've survived an interoceanic/intercontinental flight between Madrid and San Francisco, via New York - only 18 hours, worth the pay for living two weeks away from SQL. I'll try to check email but it will not be easy - mainly / it's so cool to be on vacation, you know ... See you all, and thank for your inputs.\\

Diego Buendia
Barcelona Spain