Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Another way to get a table list from a string Expand / Collapse
Author
Message
Posted Tuesday, June 15, 2010 12:09 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, January 28, 2014 11:34 AM
Points: 313, Visits: 177
Comments posted to this topic are about the item Another way to get a table list from a string
Post #937261
Posted Tuesday, June 15, 2010 7:52 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, May 2, 2013 9:10 PM
Points: 26, Visits: 263
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.
Post #937513
Posted Tuesday, June 15, 2010 12:35 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: 2 days ago @ 3:40 PM
Points: 1,787, Visits: 5,692
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


  • MMGrid Addin
  • MMNose Addin


  • Forum Etiquette: How to post Reporting Services problems
  • Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
  • How to Post Performance Problems - by Gail Shaw

  • Post #937758
    Posted Wednesday, June 16, 2010 11:36 AM
    SSC Veteran

    SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

    Group: General Forum Members
    Last Login: Thursday, December 20, 2012 1:03 PM
    Points: 265, Visits: 589
    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?
    Post #938445
    Posted Thursday, June 17, 2010 11:13 AM
    Valued Member

    Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

    Group: General Forum Members
    Last Login: Yesterday @ 9:29 AM
    Points: 57, Visits: 577
    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).
    Post #939111
    Posted Monday, August 9, 2010 9:14 AM
    Forum Newbie

    Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

    Group: General Forum Members
    Last Login: Thursday, September 6, 2012 4:11 AM
    Points: 2, Visits: 8
    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
    Post #966036
    « Prev Topic | Next Topic »

    Add to briefcase

    Permissions Expand / Collapse