2

I am having some difficulty with using a case statement in the Order By clause and am hoping for some guidance on what I am not taking into account.(SSMS 2017, SS2008 R2 SP3)

Given that I can run:

Select Myname = [name],
        OjectId = [object_id] 
From sys.tables
Order By [name],[object_id]

and can run:

Select Myname = [name],
        OjectId = [object_id] 
From sys.tables
Order By Myname,OjectId

as well as:

Declare @Sort as Varchar(25)
Set @Sort = 'Name'
Select Myname = [name],
        OjectId = [object_id] 
From sys.tables
Order By case @Sort
    When 'Name' 
    Then  [name]
    End

I am having trouble understanding why this fails:

Declare @Sort as Varchar(25)
Set @Sort = 'Name'
Select Myname = [name],
        OjectId = [object_id] 
From sys.tables
Order By case @Sort
    When 'Name' 
    Then  Myname
    End

as well as this:

Declare @Sort as Varchar(25)
Set @Sort = 'NameObj'
Select Myname = [name],
        OjectId = [object_id] 
From sys.tables
Order By case @Sort
    When 'NameObj' then [name],[object_id]
    End

Any input that anyone can share would be helpful.

Payden K. Pringle
  • 61
  • 1
  • 2
  • 19
Stan L
  • 21
  • 1
  • 1
    Look at your first one. What does When 'Name' mean. That should be Case when @Sort = 'Name'. And as coded it will not order by anything if the variable is something else. The second one simply isn't going to work like that. You can't use a case expression like that. It is an expression so it returns a single value, it doesn't control flow like a statement. – Sean Lange Nov 15 '17 at 15:45
  • 2
    @SeanLange, `case @Sort when 'Name' [...]` is a valid construction; see the syntax for the "simple CASE expression" in the [documentation](https://learn.microsoft.com/en-us/sql/t-sql/language-elements/case-transact-sql). I agree with your comments on the second failing statement though. – Joe Farrell Nov 15 '17 at 15:49
  • 1
    `CASE` **expression**, not `CASE` statement. Expressions produce (scalar) *values*. That should be enough to see why `Order By case @Sort When 'NameObj' then [name],[object_id] End` doesn't work. – Damien_The_Unbeliever Nov 15 '17 at 15:54
  • @JoeFarrell yeah...wasn't thinking clearly on that part. Need more coffee. – Sean Lange Nov 15 '17 at 16:38

1 Answers1

2

Here is how Microsoft define Then keyword in case statement

THEN

Is the expression returned when input_expression equals when_expression evaluates to TRUE, or Boolean_expression evaluates to TRUE. result expression is any valid expression.

Now lets define expression?

Expressions

Is a combination of symbols and operators that the SQL Server Database Engine evaluates to obtain a single data value. Simple expressions can be a single constant, variable, column, or scalar function.

https://learn.microsoft.com/en-us/sql/t-sql/language-elements/case-transact-sql

https://learn.microsoft.com/en-us/sql/t-sql/language-elements/expressions-transact-sql

Kashif Qureshi
  • 1,460
  • 2
  • 13
  • 20
  • This explains why the OP can't use the construction in the fifth query he posted. (Sean Lange also explained this in his comment on the question.) But it doesn't explain why it's invalid to use a column alias in a `CASE` expression that appears in an `ORDER BY` clause as shown in the OP's fourth query, when it clearly _is_ valid to use a column alias by itself in an `ORDER BY` clause, as demonstrated in the OP's second query and documented [here](https://learn.microsoft.com/en-us/sql/t-sql/queries/select-clause-transact-sql). – Joe Farrell Nov 15 '17 at 16:00
  • 1
    Thanks for pointing that out.. that has been discussed here previously...https://stackoverflow.com/questions/25763920/why-cant-i-refer-to-a-column-alias-in-the-order-by-using-case – Kashif Qureshi Nov 15 '17 at 16:24
  • Ok, so, if I accept that query 4 fails because that is just how the SQL standard works, (re:Kashis Qureshi link), and query 5 is simply an invalid construct of the use of the Then, the next question that comes to my mind is, is it possible to use the case syntax in the Order By clause to enable sorting by multiple columns? Is there another approach, other than dynamic SQL, that would enable a design that would allow for parameter driven, server side, sorting? – Stan L Nov 15 '17 at 16:55
  • Never mind. Found the answer. Crude example: Order By CASE WHEN @ Sort = 'NameObj' THEN Name END , CASE WHEN @ Sort = 'NameObj' THEN [object_id] END Thank you all for helping me understand this better. – Stan L Nov 15 '17 at 17:07