Concatenation Cursor

  • yordan.georgiev

    SSC Enthusiast

    Points: 175

    2005 does not have table variables ...

  • Christian Buettner-167247

    SSChampion

    Points: 13729

    yordan.georgiev (2/19/2010)


    2005 does not have table variables ...

    It does suport table variables, and so does SQL Server 2000.

    You probably are talking about table variables as parameters.

    Best Regards,

    Chris Büttner

  • nicolasallan

    SSC Rookie

    Points: 38

    I use this a lot also, but beware of the ORDER BY gotchya. Depending on the complexity of your query you may find that adding an ORDER BY prevents the loop from looping beyond a single record. I believe this is a SQL Server bug..?

  • Clive Chinery

    SSCrazy

    Points: 2503

    nicolasallan (2/25/2010)


    ... beware of the ORDER BY gotchya. ... I believe this is a SQL Server bug..?

    If it is a bug and you can create a script to reproduce it, please post it here and at ">https://connect.microsoft.com/dashboard/?wa=wsignin1.0

    Readers of this thread can then reproduce it and confirm it as a bug.

  • nicolasallan

    SSC Rookie

    Points: 38

    Thanks Clive,

    Here's an example of what I'm talking about...

    DECLARE @List nvarchar(max)

    SELECT @List = IsNull(@List + ', ', '') + QuoteName(name)

    FROM sys.objects

    --Uncommenting the following line will return only one list item...

    -- ORDER BY NewID()

    PRINT @List

    I won't raise this though, as it appears that this has already been raised by another SQL ServerCentral.com user. See "Warning... Order by FILTERS results".

    According to MS this is "by design"(!) There are various workarounds to this "feature", follow the above link if you're interested.

    _Nick

  • Clive Chinery

    SSCrazy

    Points: 2503

    nicolasallan (2/25/2010)


    Thanks Clive,

    ... There are various workarounds to this "feature", follow the above link if you're interested.

    _Nick

    Thank you for the link.

  • Hugo Kornelis

    SSC Guru

    Points: 64645

    I know the question is quite a few days already, but I just HAVE to reply. Hoping that everyone who has enthused about this method in this thread is still reading, I'll raise the red flag (if I only knew how to get red text here):

    THIS METHOD IS NOT DOCUMENTED, NOT SUPPORTED, AND NOT GUARANTEED TO RETURN WHAT YOU EXPECT

    It's not only the order of the names that is not guaranteed, even if an ORDER BY clause is present. There is, in fact, no guarantee that all names will be included. This method works for you today? Fine. Go ahead and use it in your one-time use scripts. But never include it in production code. It might break when you upgrade to a new version, when you install a service pack or security fix, when you upgrade your hardware, or even when a change in table statistics causes the query optimizer to compile a different execution plan.


    Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
    Visit my SQL Server blog: https://sqlserverfast.com/blog/
    SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

  • Kelsey Thornton

    SSCrazy

    Points: 2157

    Hugo Kornelis (2/26/2010)


    It might break when you upgrade to a new version, when you install a service pack or security fix, when you upgrade your hardware, or even when a change in table statistics causes the query optimizer to compile a different execution plan.

    Sounds just like all the rest of Microsoft's "standard" stuff...

    Like loads of calls which changed without warning when the OS changed from NT to XP, or MS decided to upgrade the Visual C libraries... or... or...

    (the task of filling up the blanks I'd rather leave to you) 😀

    Kelsey Thornton
    MBCS CITP

  • Paul White

    SSC Guru

    Points: 150442

    WayneS (2/16/2010)


    Just remember that without an order by clause, there is no guaranteed order. The correct answer for this could have been just as easily "Bill, Mark, John".

    Agreed. That distinction would have made this QOD better. As it stands, it risks encouraging this dubious practice, when FOR XML PATH offers a much better (and faster) alternative. Sigh.

  • mtillman-921105

    SSCertifiable

    Points: 7049

    I must be missing something really simple here, but what happened to the last comma?

    I would think that the result would have to end in a comma since what is stringed together ends in a comma -> @MaleNames + ', '

    Never mind - I see that it actiually adds the [name] after the ISNULL funtion.

    ______________________________________________________________________The greatest enemy of knowledge is not ignorance, it is the illusion of knowledge. - Stephen Hawking

  • Hugo Kornelis

    SSC Guru

    Points: 64645

    The query uses [font="Comic Sans MS"]IsNull(@MaleNames + ', ','') + [name][/font].

    When @MaleNames is NULL, [font="Comic Sans MS"]@MaleNames + ', '[/font] is NULL as well, so the ISNULL kicks in and replaces the NULL with an empty string, to which [name] is concatenated. This leaves us with a single name and no comma.

    When @MaleNames is not NULL (but, for instance, a single name and no comma), [font="Comic Sans MS"]@MaleNames + ', '[/font] adds a comma to that name, the ISNULL does nothing, and then [Name] is concatenated - so now we have two names, seperated by a comma.

    After that it continues to add ', ' and a name to the string, until processing stops.

    DISCLAIMER: This description is based on the assumption that SQL Server will process the rows one by one and not reset the variable in between; this is often observed but undocumented behaviour and should not be relied on. Other behaviour can happen anytime (and has in fact sometimes been observed).


    Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
    Visit my SQL Server blog: https://sqlserverfast.com/blog/
    SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

  • mtillman-921105

    SSCertifiable

    Points: 7049

    Thanks Hugo, I should have mulled over that question just a few more minutes before asking it.

    ______________________________________________________________________The greatest enemy of knowledge is not ignorance, it is the illusion of knowledge. - Stephen Hawking

  • Paul White

    SSC Guru

    Points: 150442

    Hugo Kornelis (2/26/2010)


    I'll raise the red flag (if I only knew how to get red text here):

    THIS METHOD IS NOT DOCUMENTED, NOT SUPPORTED, AND NOT GUARANTEED TO RETURN WHAT YOU EXPECT

    Like that 😉

Viewing 13 posts - 31 through 43 (of 43 total)

You must be logged in to reply to this topic. Login to reply