Ques on Order By clause in sp 1

[batteam]

I am attempting to program multiple 'Order By' criteria inside a 'Case' stmt in sp like:

SP Name...etc.

@txtlastname varchar(50),
@OrderbyCritiera int

As
Select first_name, last_name, city, state

from Table1
where last_name = @txtlastname

Order by
Case @OrderbyCriteria
When 1 then first_name
when 2 then last_name
when 3 then city, last_name
End

Problem is, when @OrderbyCriteria is 3, the result is not sorted by city then last name. In fact, the result is not sorted at all. Question: can more than one 'Order By' criteria be used in a 'Case' stmt as shown above (#3)? When only one field name is specified in my 'Order By' clause inside the 'Case' stmt, results are sorted as they should. Thanks for any assistance you can offer.

 

[bborissov]

What about:

when 3 then city + last_name

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.

 

[mreigler]

Or if the two fields are not the same data type (which doesn't look the case here)

Order by
  Case @OrderbyCriteria
    When 1 then first_name
    when 2 then last_name
    when 3 then city
  End,
  Case @OrderbyCriteria
    when 3 then last_name
  End

Mike Reigler
Melange Computer Services, Inc

 

[batteam]

bborissov:

That worked. I tried many combinations including declaring some variables but nothing worked. And of course, the three books I looked in didn't offer any exact examples. Thanks so much for your time and help.

 

[bborissov]

You are welcome
But I think Mike's answer is better, becuase you could put ASC or DESC for every fields in order.

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.