CELKO (6/21/2011)
Some version of this mis-use of SQL gets invented again every few months. It is a slow, dangerous mess. Are you familar with the long parameters list idiom? SQL is a data base language, not a tool for writing parsers.
Joe,
I'm very familiar with the fact that SQL is not a language for writing parsers. I worked on an early word processor in PDP-11 assembly language and I've written 2 text editors in C. I've also had to do parsing in COBOL. Believe me, C is the language of choice for parsing.
However, a delimited array of parameters is a very common SQL problem. The idea is to make it easy to program and still be robust both in the front end and back end. I've seen this requirement many, many times and this technique is rock solid.
This technique, in any case, is NOT a parser. All of the parsing is done at the beginning, which converts the string array into a TABLE, which is what SQL Server handles best. That's the whole point of the article - let SQL Server do what it does best. Join to tables and such.
Todd Fifield