order by using CASE 1

[developer77]

Hi everyone,

I have a stored procedure that has the following sort by section and @SortColumn is one of the passed parameters:

ORDER BY
CASE @SortColumn
WHEN 1 THEN p.LastName
WHEN 2 THEN p.Address
WHEN 3 THEN p.Year
ELSE NULL
END,
p.Degree,
p.StudentID ASC


I would like to change it so that when 1 is passed in for @SortColumn, then it will sort by p.LastName, p.FirstName. I tried changing it what's below but get an error saying "incorrect syntax near ",". Is there a way for me to sort by more than one column if 1 is passed?



ORDER BY
CASE @SortColumn
WHEN 1 THEN p.LastName, p.FirstName
WHEN 2 THEN p.Address
WHEN 3 THEN p.Year
ELSE NULL
END,
p.Degree,
p.StudentID ASC


Thanks in advance..

 

[SQLBill]

Take out the comma after END.

-SQLBill

Posting advice: FAQ481-4875

 

[developer77]

Hi Bill, thanks for your reply. I changed it to the following but it still won't work. I get the same error message. Does CASE statements allow you to specify more than one column?

ORDER BY
CASE @SortColumn
WHEN 1 THEN p.LastName, p.FirstName
WHEN 2 THEN p.Address
WHEN 3 THEN p.Year
ELSE NULL
END

 

[mharroff]

How about

ORDER BY
    CASE @SortColumn 
            WHEN 1 THEN p.LastName
            WHEN 2 THEN p.Address
            WHEN 3 THEN p.Year
            ELSE NULL          
      END,
    CASE @SortColumn 
            WHEN 1 THEN p.FirstName
            ELSE NULL          
      END,
      p.Degree,
      p.StudentID ASC

 

[developer77]

Thanks SQLBill and mharroff. I tried what you suggested mharroff and it worked...thanks again!!!