SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Concatenation Cursor


Concatenation Cursor

Author
Message
abrazier
abrazier
SSC Rookie
SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)

Group: General Forum Members
Points: 26 Visits: 59
Nevermind...
yordan.georgiev
yordan.georgiev
Grasshopper
Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)

Group: General Forum Members
Points: 19 Visits: 47
2005 does not have table variables ...
Christian Buettner-167247
Christian Buettner-167247
Hall of Fame
Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)

Group: General Forum Members
Points: 3537 Visits: 3889
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
nicolasallan
SSC Rookie
SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)

Group: General Forum Members
Points: 26 Visits: 89
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
Clive Chinery
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1255 Visits: 154
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
nicolasallan
SSC Rookie
SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)

Group: General Forum Members
Points: 26 Visits: 89
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
Clive Chinery
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1255 Visits: 154
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
Hugo Kornelis
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10811 Visits: 11967
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 MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Kelsey Thornton
Kelsey Thornton
SSChasing Mays
SSChasing Mays (631 reputation)SSChasing Mays (631 reputation)SSChasing Mays (631 reputation)SSChasing Mays (631 reputation)SSChasing Mays (631 reputation)SSChasing Mays (631 reputation)SSChasing Mays (631 reputation)SSChasing Mays (631 reputation)

Group: General Forum Members
Points: 631 Visits: 282
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) :-D

Kelsey Thornton
MBCS CITP
Paul White
Paul White
SSCoach
SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)

Group: General Forum Members
Points: 15512 Visits: 11354
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.



Paul White
SQLPerformance.com
SQLblog.com
@SQL_Kiwi
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search