tsql string concat with select and order by does not work with function in order by clause?

Answer 1

I don't know if this is at all helpful, but when I try this:

set @s = '          ';

select @s = @s + s from #test order by dbo.wtfunc(s);
select @s AS myTest

I get this (note there are spaces prepending the 'foo' and none trailing):

  foo

I guess this is some obscure little bug?!

Answer 2

It would appear that this is a known issue with Aggregate Concatenation Queries.

From the link:

"The ANSI SQL-92 specification requires that any column referenced by an ORDER BY clause match the result set, defined by the columns present in the SELECT list. When an expression is applied to a member of an ORDER BY clause, that resulting column is not exposed in the SELECT list, resulting in undefined behavior."

Answer 3

You can do this by using a computed column, for instance:

DROP TABLE dbo.temp;

CREATE TABLE dbo.temp
(
  s varchar(20)
 ,t AS REVERSE(s)
);
INSERT INTO dbo.temp (s) VALUES ('foo');
INSERT INTO dbo.temp (s) VALUES ('bar');
INSERT INTO dbo.temp (s) VALUES ('baz');
INSERT INTO dbo.temp (s) VALUES ('blah');
GO

-- Using the function directly doesn't work:
DECLARE @s varchar(2000);
SELECT s, REVERSE(s) FROM dbo.temp ORDER BY REVERSE(s);
SET @s = '';
SELECT @s = @s + s FROM dbo.temp ORDER BY REVERSE(s);
SELECT @s;
GO

-- Hiding the function in a computed column works:
DECLARE @s varchar(2000);
SELECT s, t FROM dbo.temp ORDER BY t;
SET @s = '';
SELECT @s = @s + s FROM dbo.temp ORDER BY t;
SELECT @s;
GO