Another way to get a table list from a string

Question

Another way to get a table list from a string

bernabe.diaz@yahoo.com

SSC Enthusiast

Points: 170
More actions
June 15, 2010 at 12:09 am

#233741

Comments posted to this topic are about the item Another way to get a table list from a string
Forum Etiquette: How to post Reporting Services problems

[/url]

Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
[/url]

How to Post Performance Problems - by Gail Shaw
[/url]

deanroush SSC-Addicted Points: 474 More actions · Answer 1

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.

mister.magoo SSC-Forever Points: 47068 More actions · Answer 2

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

http://bradsruminations.blogspot.com/2010/01/delimited-string-tennis-again-final.html

in which he describes a few tweaks that really speed things up...

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

sjsubscribe SSCrazy Points: 2080 More actions · Answer 3

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?

aaron.bertrand Ten Centuries Points: 1260 More actions · Answer 4

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).

gabriele.acconcia Grasshopper Points: 18 More actions · Answer 5

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

akljfhnlaflkj SSC Guru Points: 76202 More actions · Answer 6

akljfhnlaflkj

SSC Guru

Points: 76202

May 19, 2016 at 6:52 am

#1880186

Thanks for the script.