CODEWITHSUNDEEP
sql-servert-sqlsql-order-by
Dan Cook
Dan Cook

Reputation: 2072

Conditional ORDER BY not working for temp table

For the following simplified T-SQL statement

DECLARE @SortColumn nvarchar(50)
SET @SortColumn = 'Price'

SELECT Name, 1+1 as Price
INTO #MyTempTable
FROM Events
ORDER BY 
    CASE 
       WHEN @SortColumn = 'Name' THEN Name 
    END,
    CASE 
       WHEN @SortColumn = 'Price' THEN Price 
    END DESC

Why do I get the error:

Invalid column name 'Price'.

Is it possible to sort on a calculated column?

Note that my actual problem is part of a much more complex query, but this simplified example does demonstrate the problem and will throw an error.

Upvotes: 0

Views: 114

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269753

You can order by a computed field. You just can't include such a field as an expression in the order by. Here is one work-around:

SELECT e.Name, v.Price
INTO #MyTempTable
FROM Events e OUTER APPLY
     (VALUES (1 + 1)) v(price)
ORDER BY (CASE WHEN @SortColumn = 'Name' THEN e.Name END),
         (CASE WHEN @SortColumn = 'Price' THEN v.Price END DESC);

That is, you can define the expressions using OUTER APPLY. Then the result can be used just as if it were a column in a table.

There is basically no limit on defining the expression. In can include columns from all tables that appear earlier in the FROM clause.

Upvotes: 1

Related Questions