Home Forums Article Discussions Article Discussions by Author Discuss content posted by Wagner Crivelini Cursors for T-SQL Beginners RE: Cursors for T-SQL Beginners

  • June 5, 2011 at 10:25 am

    #1334141

    jmaloney-1117255 (5/31/2011)

    Jeff Moden (1/1/2009)

    gryphonsclaw (1/1/2009)

    You don't have to use a cursor to concatenate a string.

    DECLARE @myVar varchar(MAX)

    SET @myVar = ''

    SELECT @myVar = @myVar + mycolumn + ' '

    FROM myTable

    SELECT @myVar

    The author also pointed that out in the article.

    Apologies for the newbishness, but this bit of SQL kinda blows my mind because I've been a developer for years, but had never come across code like this. How does this work? Seems like there would have to be an internal cursor at work here. Can someone please point out a link explaining how this style block of code works? It would be greatly appreciated.

    I've started reading Itzik Ben Gan's T-SQL Querying book (currently on Ch 4) and hope to learn new appraches and techniques. Ch 1 was indeed eye-opening on the order in which clauses are processed. Sad to say that the db's I'm currently working with at my new job are rife with cursors and udfs. You couldn't possibly design less performant dbs if you tried. Oh, and not one single comment ... anywhere. What did I get myself into?

    Apologies for the late response. :blush:

    Think of it as a "set based loop"... something that some of us call a "Pseudo Cursor". For every row in the table, it will add "mycolumn" from the table. So, if the table had the first three letters of the English Alphabet in it, here's what would happen.

    1. The DECLARE and SET create the variable and turns it from containing a NULL to having an empty string in it.

    2. The SELECT reads the "first" row from the table and the content of row/column is added to the variable along with a "space" delimiter (the delimiter isn't important here) and reassigned to the variable kind of like say @counter = @counter + 1.

    The variable now contains "A ".

    3. The SELECT then reads the "second" row from the table and the same thing happens as in #2 above. The content of the second row is added to the variable and the result is stored back in the variable.

    The variable now contatins "A B ".

    4. The SELECT then reads the "third" row from the table and the same thing happens as in #2 and #3 above. The content of the second row is added to the variable and the result is stored back in the variable.

    The variable now contatins "A B C ".

    Unfortunately, the technique is a less-than-optimal method and can get quite slow as the variable gets bigger for reasons similar to why the old fashioned "bubble-sort" get's slower... It has to handle more and more data for each row added in. There's a trick with XML Path that you can do in a setbased fashion to concatenate related row information. Please see the following articles for more information...

    http://www.sqlservercentral.com/articles/Test+Data/61572/

    http://www.sqlservercentral.com/articles/comma+separated+list/71700/

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)