SQL

The crew at 4 Guys From Rolla have come through for me again (as they frequently do in matters of ASP, .NET and SQL). In building a new webservice, I wanted to be able to pass in a comma-delimited list of ID's to a SQL stored procedure. Unfortunately, there isn't a SQL datatype for something like "comma delimited list". I could have taken the "easy" way out and built the SQL query dynamically in C# instead of calling a stored procedure, but then I would have been mad at myself for breaking the rule that we agreed on that all the webservices would strictly call stored procedures. I figured I'd have to use SQL to parse a passed in string into ID's, and build the SQL query from there. However, this seemed like something someone else probably would have done in the past. I found a few techniques online but this one seemed the cleanest and best-implemented. You basically pass in a big varchar string of id's into the stored procedure and then call a SQL User Defined Function called "Split" which returns a table of the ID's which you can join to. It's simpler than it sounds...instead of having something like

SELECT *
FROM table
WHERE id in (--lots of convoluted code to parse out the ids--)

you just call

SELECT *
FROM table
WHERE id in ( SELECT convert(int,Value) FROM dbo.Split(@list_string,',') )

The complete code for the function and examples of how to use it are listed in the article. Thanks to "4 Guys" for saving me a couple hours and some headaches with this nifty technique!