1

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.

eidylon
  • 7,068
  • 20
  • 75
  • 118
  • 4
    Well this pattern is not so "tried-and-true", then... That's because you are relying on undocumented behavior. – usr Oct 15 '12 at 19:08
  • 2
    This is actually known to be 'tried and failed'. – Remus Rusanu Oct 15 '12 at 19:26
  • What happens if you do the `order by` in a sub-query and concatenate the results of this query? – Paul Williams Oct 15 '12 at 19:36
  • ORDER BY the sub-query it returns all rows alpha-sorted again. I'm surprised by the comments. I thought this was pretty much the "standard" way of doing row-value concatenation in SQL server. Interesting... – eidylon Oct 15 '12 at 19:41

1 Answers1

3

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.

Community
  • 1
  • 1
Martin Smith
  • 438,706
  • 87
  • 741
  • 845