February 19, 2010 at 12:50 am
2005 does not have table variables ...
February 19, 2010 at 12:56 am
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
February 25, 2010 at 7:21 am
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..?
February 25, 2010 at 7:53 am
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.
February 25, 2010 at 11:42 am
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
February 26, 2010 at 5:06 am
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.
February 26, 2010 at 1:22 pm
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.
February 27, 2010 at 3:08 pm
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
March 30, 2010 at 8:44 am
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
SQLkiwi blog
@SQL_Kiwi
March 31, 2010 at 2:47 pm
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
March 31, 2010 at 2:58 pm
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).
March 31, 2010 at 3:17 pm
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
April 1, 2010 at 3:18 am
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 😉
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
Viewing 13 posts - 31 through 42 (of 42 total)
You must be logged in to reply to this topic. Login to reply