SQLServerCentral / Article Discussions / Article Discussions by Author / Discuss content posted by Bernabe Diaz / Another way to get a table list from a string / Latest Posts

RE: Another way to get a table list from a string

gabriele.acconcia — Mon, 09 Aug 2010 09:14:26 GMT

Is the script correct? When I run it, I got an "Unclosed quotation mark after the character string '" error message. So I commented out the last + ' (that before the EXEC(@STR) statement) and could create the stored procedure.After the fix, the stored procedure seems to work fine.Best regards

RE: Another way to get a table list from a string

aaron.bertrand — Thu, 17 Jun 2010 11:13:12 GMT

This is clever and a little more flexible than some of the other techniques - and useful if all you need to do is return the output directly to the caller. But the weakness is that the dynamic SQL means it can't be moved to a function - using a procedure really limits what you can do with the output. I've had very good results that use a table-valued function and a numbers table to parse lists, and then the TVF results can be used in JOINs and other structs, unlike the output of a stored procedure (which can only be dumped to a temp table if further processing or filtering is required).

RE: Another way to get a table list from a string

sjsubscribe — Wed, 16 Jun 2010 11:36:39 GMT

Good parsing technique. Even though @strDataSet is declared as max, aren't the procedure parameters limited to max length of only 128 characters? That limits the longest string that can be parsed to that limit. Right?

RE: Another way to get a table list from a string

mister.magoo — Tue, 15 Jun 2010 12:35:56 GMT

Thanks for the Article.For further reading, anyone interested in using xml to split strings may be interested in this excellent blog post from Brad Schulz[url]http://bradsruminations.blogspot.com/2010/01/delimited-string-tennis-again-final.html[/url]in which he describes a few tweaks that really speed things up...

RE: Another way to get a table list from a string

deanroush — Tue, 15 Jun 2010 07:52:15 GMT

Excellent! I have two other procs that do this but not with such elegance. i will be testing performance of the three versions soon and will add findings. The other versions also return only strings.I did have to add one more single quote at the end to make it work.

Another way to get a table list from a string

diaz.bernabe — Tue, 15 Jun 2010 00:09:45 GMT

Comments posted to this topic are about the item [B]Another way to get a table list from a string [/B]