CODEWITHSUNDEEP
sql-serversql-order-byconcatenation
eidylon
eidylon

Reputation: 7238

ORDER BY suddenly conflicting with VARCHAR concatenation in TSQL

I have code in Sql Server 2008 which concatenates some strings from a query into a variable using the tried-and-true SELECT @VAR = @VAR + FIELD FROM TABLE ORDER BY OTHERFIELD syntax.

This is my exact SQL: 

SELECT @SQL = @SQL + 
    ISNULL(FORMULA, 
    CASE WHEN USEMAP = 1 THEN 
        'dbo.getFieldTranslation('+CONVERT(VARCHAR,ROWID)+', [' + ISNULL(ENCOMPASSFIELD,'') + '])' 
    ELSE 
        '[' + ISNULL(ENCOMPASSFIELD,'') + ']' END
    ) + 
    ' AS "' + FILECOLNAME + '",' + @CRLF  
FROM dbo.EXPORTMAP_EX 
WHERE WAREHOUSEID = @WHSID 
ORDER BY ORDERIDX

This was working beautifully and perfectly. Then suddenly today, it stopped working. It was only concatenating on the last row's values. In debugging, I found out that if I take out the ORDER BY clause, then all the fields come back, but sorted strictly alphabetically by the string value of the fields.

This SQL however is being used to generate a view for an export. The export file must have its fields in the proper order, hence the ORDER BY clause. Why is the ORDER BY suddenly serving to filter my results?

I cannot use the XML/STUFF route because some of the data does have < and > signs in it.
I'd rather not have to go back to using an old WHILE loop, but I may have to.

Upvotes: 1

Views: 284

Answers (1)

Martin Smith
Martin Smith

Reputation: 453707

As indicated in the comments you are relying on undocumented behaviour.

Microsoft say "The correct behavior for an aggregate concatenation query is undefined.". If the compute scalar moves to the wrong place in the plan then it just stops working.

The work around you are using of defining the ordering expression in a sub-query and concatenating the results of this query I believe is safe . From Ordering guarantees in SQL Server...

For backwards compatibility reasons, SQL Server provides support for assignments of type SELECT @p = @p + 1 ... ORDER BY at the top-most scope.

That posting was written in 2005 though so I'm not sure if the guarantee still holds (Edit: After reviewing Connect Items on this issue I'm sure that it doesn't)

BTW: You say

I cannot use the XML/STUFF route because some of the data does have < and > signs in it. I'd rather not have to go back to using an old WHILE loop, but I may have to.

This is not the case but it needs a slightly more complicated XML PATH query than you were probably using see the answer here for an example.

Upvotes: 3

Related Questions