March 21, 2011 at 6:40 am
Such a shame that the author did not include the disclaimer that says that this method is not supported and not guaranteed to return correct results. I have lost the URL, but I once saw a page with several examples where this same code returns the data in a different order (in cases where an ORDER BY is also used - not really relevant in this case), or incomplete data.
Please use the FOR XML method instead. See the post by feeza_ibrahim (third post on page 1 of this topic)
March 21, 2011 at 6:51 am
One of the threads where a method like this was discussed someone from MS came on and said that it relied on an undocumented feature in the query optimizer that could be changed at any time. Unfortunately, like Hugo I don't have the URL to that. The shame is that a method that's so widely used (I've seen it discussed several times) isn't well documented to be unsupported.
March 21, 2011 at 6:53 am
intrope (3/21/2011)
You can also do this with two variables with one SELECT:
DECLARE @t varchar(max)
DECLARE @s-2 varchar(1)
SET @t = ''
SET @s-2 = ''
SELECT @t += @s-2 + Tag, @s-2 = ',' FROM Tags ORDER BY Tag
SELECT @t
And just for fun, a recursive CTE solution! :hehe:
DECLARE @t varchar(max);
SET @t = '';
WITH rectags AS
(
SELECT TOP 1 Tag FROM Tags ORDER BY Tag
UNION ALL
(
SELECT ', ' + Tag FROM Tags
EXCEPT
SELECT TOP 1 ', ' + Tag FROM Tags ORDER BY Tag
)
)
SELECT @t += Tag FROM rectags
SELECT @t
Is this close enough to what you were refering to Hugo?
March 21, 2011 at 6:54 am
And this the original thread that started the connect ticket.
http://www.sqlservercentral.com/Forums/Topic607455-145-1.aspx
March 21, 2011 at 7:05 am
hi Hugo,
What do you mean with "incomplete data"?
I only found these articles:
http://sqlblogcasts.com/blogs/tonyrogerson/archive/2006/05/18/449.aspx
http://ryanfarley.com/blog/archive/2004/03/01/390.aspx
They only explain the obvious: When assigning a single value using the SELECT @var = column FROM table syntax assigns the last row value, which may be any one due to optimization.
March 21, 2011 at 7:14 am
stefan.hoffmann (3/21/2011)
hi Hugo,What do you mean with "incomplete data"?
I only found these articles:
http://sqlblogcasts.com/blogs/tonyrogerson/archive/2006/05/18/449.aspx
http://ryanfarley.com/blog/archive/2004/03/01/390.aspx
They only explain the obvious: When assigning a single value using the SELECT @var = column FROM table syntax assigns the last row value, which may be any one due to optimization.
Hi Stefan,
Thanks to the excellent links just piosted by Ninja (thanks, Ninja!), I can show you the code to demonstrate what I mean. Just copy, run, and check the results. (Note that this code is an almost verbatim copy of the code Ninja included in the Conenct item he links to a few posts up).
PRINT 'No ORDER BY';
PRINT '';
DECLARE @X NVARCHAR(MAX);
SET @X = '';
SELECT @X = @X + 'PRINT ''[' + name + ']'';' + CHAR(13) + CHAR(10) FROM msdb.sys.tables ORDER BY name;
PRINT @X;
go
PRINT '';
PRINT '';
PRINT 'ORDER BY NEWID()';
PRINT '';
DECLARE @X NVARCHAR(MAX);
SET @X = '';
SELECT @X = @X + 'PRINT ''[' + name + ']'';' + CHAR(13) + CHAR(10) FROM msdb.sys.tables ORDER BY NEWID();
PRINT @X;
go
PRINT '';
PRINT '';
PRINT 'ORDER BY SELECT(NEWID())';
PRINT '';
DECLARE @X NVARCHAR(MAX);
SET @X = '';
SELECT @X = @X + 'PRINT ''[' + name + ']'';' + CHAR(13) + CHAR(10) FROM msdb.sys.tables ORDER BY (SELECT NEWID());
PRINT @X;
go
Note that this is not the code I originally found on some website (I believe Steve Kass posted that code), but the result is similar enough - instead of all rows concatenated, you get just one of the rows. Probably the one that happens to be processed last.
March 21, 2011 at 7:44 am
hi Hugo,
http://msdn.microsoft.com/en-us/library/ms187953.aspx
states that the result is defined as the value of the last row and not undefined as Umachandar in the Connect ticket says, should be corrected then.
USE AdventureWorks2008R2 ;
GO
DECLARE @EmpIDVariable NVARCHAR(MAX) ;
-- Success
SELECT @EmpIDVariable = COALESCE(@EmpIDVariable + ', ', '') + CAST(BusinessEntityID AS NVARCHAR(MAX))
FROM HumanResources.Employee
ORDER BY BusinessEntityID DESC ;
SELECT @EmpIDVariable ;
-- Failure
SET @EmpIDVariable = NULL ;
SELECT @EmpIDVariable = COALESCE(@EmpIDVariable + ', ', '') + CAST(BusinessEntityID AS NVARCHAR(MAX))
FROM HumanResources.Employee
ORDER BY CASE WHEN NationalIDNumber > 100000000 THEN 0 ELSE 1 END ASC ;
-- OR
-- ORDER BY 1 ASC ;
SELECT @EmpIDVariable ;
Imho a problem of the optimizer. Seems that ORDER BY works on columns in the table, but not on expressions. So it's in the end only a cursor or FOR XML.
March 21, 2011 at 8:12 am
stefan.hoffmann (3/21/2011)
hi Hugo,http://msdn.microsoft.com/en-us/library/ms187953.aspx
states that the result is defined as the value of the last row and not undefined as Umachandar in the Connect ticket says, should be corrected then.
That page is about the syntax SELECT @variable = column, not about the syntax SELECT @variable = @variable + column. The latter is undocumented. And the results are, as Umachandar says, undefined.
Imho a problem of the optimizer. Seems that ORDER BY works on columns in the table, but not on expressions. So it's in the end only a cursor or FOR XML.
Definitely a result of how the optimizer creates plans, but I would not call it a problem. Since the behaviour of this specific syntax is not defined, both results can be considered correct. (In fact, I tend to find the result with only one value in the list a bit more correct than the result with all the rows, since set-based SQL is supposed to behave as if all rows are processed at once, not one by one).
March 21, 2011 at 8:28 am
hi Hugo,
I'd like to disagree. While the last example uses only @var = column the explanation above it clearly states:
"If a SELECT statement returns more than one row and the variable references a nonscalar expression, the variable is set to the value returned for the expression in the last row of the result set. For example, in the following batch @EmpIDVariable is set to the BusinessEntityID value of the last row returned, which is 1:"
"references a nonscalar expression" should describe exactly our SELECT @var = @var + column FROM table ORDER BY columnOrExpression scenario.
Or is there any subtle interpretation I don't get here, maybe my English is not sufficient enough...
March 21, 2011 at 8:37 am
I believe that the method in the original article is documented as unsupported by Microsoft, and is subject to change, and it may not work in future releases of SQL Server.
But, the technique has been known for years, and it's hard to see how Microsoft can actually remove this behavior now. You never know.
March 21, 2011 at 8:40 am
Since the example doesn't do any concatenation I wouldn't assume that it's referring to the situation that the article is discussing.
USE AdventureWorks2008R2;
GO
DECLARE @EmpIDVariable int;
SELECT @EmpIDVariable = BusinessEntityID
FROM HumanResources.Employee
ORDER BY BusinessEntityID DESC;
SELECT @EmpIDVariable;
GO
All the example is showing is that if you're pulling multiple rows what's returned last gets assigned to the variable. It doesn't state anything about how the expression is compiled so if it's more than just a simple column pull this doesn't document the behavior.
March 21, 2011 at 8:47 am
Woof...how it will?:unsure:
March 21, 2011 at 11:58 am
Doesn't the FOR XML method blow up when certain characters are in place in the data??
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
March 21, 2011 at 12:05 pm
Here is how I grab a Project record and all Sites associated with it using the STUFF and FOR XML
SELECT p.ProjectDescription,
STUFF((SELECT ',' + s.SiteCode FROM ProjectSitesps
JOIN
ALL_SitessON ps.SiteID = s.SiteID
WHERE ps.ProjectNum = @ProjectNum ORDER BY s.SiteCode FOR XML PATH ('')
)
,1,1, '') As ProjectSites
FROM Projects p
WHERE p.ProjectNum = @ProjectNum
I got htis from Sharath_123 on EE
March 21, 2011 at 12:22 pm
TheSQLGuru (3/21/2011)
Doesn't the FOR XML method blow up when certain characters are in place in the data?
Unless there's a character other than the three big ones I know that could cause it to have issues it works fine. The output may just need a little more massaging. The first query below shows the direct method. The second one puts the real value back in.
declare @xml_char table (
xmlchar char(1)
)
insert into @xml_char
select '<'
union all
select '>'
union all
select '&'
SELECT REPLACE((SELECT DISTINCT RTRIM(LTRIM(xmlchar)) AS 'data()'
FROM @xml_char
FOR XML PATH ( '' )), ' ', ', ')
SELECT REPLACE(REPLACE(REPLACE(REPLACE((SELECT DISTINCT RTRIM(LTRIM(xmlchar)) AS 'data()'
FROM @xml_char
FOR XML PATH ( '' )), ' ', ', '),'&','&'),'>','>'),'<','<')
Viewing 15 posts - 31 through 45 (of 87 total)
You must be logged in to reply to this topic. Login to reply