Common Table Expressions (CTE) are a really cool recent addition to my vocabulary. Operating a lot like a temporary table, they only last for the duration of the query and clean up after themselves.
Another cool idea is string concatenation within a SELECT statement. Together they make some interesting code.
So we had some code that looked something like this:
;WITH Person as ( --This is where we kick off the CTE
SELECT @STR = @STR +
CASE E.FirstName WHEN ''
C.FirstName --Remember this line.
ELSE
E.FirstName
AS 'FristName'
FROM Employees E
JOIN Contacts C ON E.Email = C.Email
ORDER BY
FirstName
)
An elegant solution for a more civilized age. The string concatenates the correct field while the CTE loops over the tables all without the use of a cursor.
Puzzle:
This code would work, except we care about order. And order and string concatenation don't mix, at least according to the following knowledge base article.
What's interesting is that the query doesn't blow up, it just acts...weird. Like returning on the last thing to be concatenated. Or not causing any problem at all. Remember the line I marked above? If I set it to a string literal, the code operates normally. Otherwise, weirdness.
Solution:
Dump it all into a temp table to order it. Of course you lose your nice tidy code that way, but it works. At least you can say you tried to use a CTE.
If you have a more elegant solution, please post it below in the comments. I'd love to hear it.
Ongoing Mystery:
So what actually is happening with those queries where the undefined behavior hasn't bitten me, yet? Do tests for multi row result concatenation conclusively tell me whether I've missed the error. Or is the weird undefined behavior dependent not only on query structure but also table contents?