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 «««12345»»

Concatenation Cursor Expand / Collapse
Author
Message
Posted Thursday, February 18, 2010 9:52 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, April 7, 2014 6:59 AM
Points: 24, Visits: 49
Nevermind...
Post #868197
Posted Friday, February 19, 2010 12:50 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, March 28, 2011 12:09 PM
Points: 7, Visits: 47
2005 does not have table variables ...
Post #868797
Posted Friday, February 19, 2010 12:56 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, July 22, 2014 1:45 AM
Points: 2,826, Visits: 3,866
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
Post #868799
Posted Thursday, February 25, 2010 7:21 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, June 19, 2014 10:42 AM
Points: 26, Visits: 83
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..?
Post #872719
Posted Thursday, February 25, 2010 7:53 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, September 2, 2011 3:22 AM
Points: 1,227, 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.
Post #872753
Posted Thursday, February 25, 2010 11:42 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, June 19, 2014 10:42 AM
Points: 26, Visits: 83
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
Post #872918
Posted Friday, February 26, 2010 5:06 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, September 2, 2011 3:22 AM
Points: 1,227, 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.
Post #873333
Posted Friday, February 26, 2010 1:22 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 6:22 AM
Points: 5,927, Visits: 8,177
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
Post #873681
Posted Saturday, February 27, 2010 3:08 PM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Thursday, March 27, 2014 8:16 AM
Points: 534, 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)


Kelsey Thornton
MBCS CITP
Post #874068
Posted Tuesday, March 30, 2010 8:44 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:50 AM
Points: 11,192, Visits: 11,098
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
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #892858
« Prev Topic | Next Topic »

Add to briefcase «««12345»»

Permissions Expand / Collapse