Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Concatenation Cursor


Concatenation Cursor

Author
Message
abrazier
abrazier
Grasshopper
Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)

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

Group: General Forum Members
Points: 13 Visits: 47
2005 does not have table variables ...
Christian Buettner-167247
Christian Buettner-167247
SSCrazy
SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)

Group: General Forum Members
Points: 2949 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.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1229 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.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1229 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
SSCrazy Eights
SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)

Group: General Forum Members
Points: 8323 Visits: 11580
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
Mr or Mrs. 500
Mr or Mrs. 500 (559 reputation)Mr or Mrs. 500 (559 reputation)Mr or Mrs. 500 (559 reputation)Mr or Mrs. 500 (559 reputation)Mr or Mrs. 500 (559 reputation)Mr or Mrs. 500 (559 reputation)Mr or Mrs. 500 (559 reputation)Mr or Mrs. 500 (559 reputation)

Group: General Forum Members
Points: 559 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
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: 10338 Visits: 11350
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