Order by with variable Coalesce-CodePudding

I have the following sql command

DECLARE @cols NVARCHAR (MAX)
SELECT @cols = COALESCE (@cols   ',['  cast(Month as nvarchar(2)) ']' , '['  Cast(Month as nvarchar(2)) ']')
FROM    (select distinct Month from Employee  WHERE  Year*100 Month BETWEEN 201704 and 201712 ) as e
PRINT @cols

The result was

[9],[12],[6],[7],[10],[4],[5],[11],[8]

But I really want it to result in sort order

[4],[5],[6],[7],[8],[9],[10],[11],[12]

CodePudding user response：

Variable coalescing is documented as being non-deterministic, and may cause incorrect results, in particular in the presence of ORDER BY. You can also not place ORDER BY in a derived table or view, for obvious reasons: the final ordering is determined only by the outer query.

You should instead just use STRING_AGG to aggregate. You can use WITHIN GROUP (ORDER BY to get the ordering.

Note also:

Always use QUOTENAME to get brackets round your column names, instead of doing it yourself, as escaping can be complex.
It's better to make exact comparisons on columns, rather than doing calculations on them and then comparing, as then you can hit indexes (sarge-ability).
It's probably better to store dates in actual date columns, rather than messing around with multiple columns, but I will leave database redesign to you

DECLARE @cols NVARCHAR(MAX) = (
    SELECT STRING_AGG(QUOTENAME(Month), N',') WITHIN GROUP (ORDER BY Month)
    FROM (
        select distinct
          Month
        from Employee
        WHERE Year = 2017
          AND Month BETWEEN 4 AND 12
    ) as e
);

For SQL Server 2016 and earlier, you can use the old FOR XML method:

DECLARE @cols NVARCHAR(MAX) = STUFF((
    SELECT N','   QUOTENAME(Month)
    FROM (
        select distinct
          Month
        from Employee
        WHERE Year = 2017
          AND Month BETWEEN 4 AND 12
    ) as e
    ORDER BY Month
    FOR XML PATH(''), TYPE
  ).value('text()[1]','nvarchar(max)')
  , 1, LEN(N','), N'');