Case order by

Hello
I was wondering if you guys could help me, I have a table with Firstname and
Lastname columns.
And I want to pass a parameter @.SortCol char(10) and @.AscDesc, so I can sort
Firstname ASC/DESC or Lastname ASC/DESC....
how's that done, i need to put 4 different Cases?
TIA
/LasseWhat about that
Use northwind
DECLARE @.col varchar(200)
DECLARE @.Direc varchar(200)
SET @.col = 'OrderID'
SET @.Direc = 'ASC'
EXEC('Select * from Orders Order by ' + @.col + ' ' + @.Direc)
http://www.sommarskog.se/dynamic_sql.html
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Lasse Edsvik" <lasse@.nospam.com> schrieb im Newsbeitrag
news:OKlIIzxSFHA.1384@.TK2MSFTNGP09.phx.gbl...
> Hello
> I was wondering if you guys could help me, I have a table with Firstname
> and
> Lastname columns.
> And I want to pass a parameter @.SortCol char(10) and @.AscDesc, so I can
> sort
> Firstname ASC/DESC or Lastname ASC/DESC....
> how's that done, i need to put 4 different Cases?
> TIA
> /Lasse
>|||SELECT <column list>
FROM <table>
ORDER BY CASE @.AscDesc WHEN 'ASC' THEN
CASE @.SortCol
WHEN 'Firstname' THEN Firstname
WHEN 'Lastname' THEN Lastname
END END ASC,
CASE @.AscDesc WHEN 'DESC' THEN
CASE @.SortCol
WHEN 'Firstname' THEN Firstname
WHEN 'Lastname' THEN Lastname
END END DESC,
Jacco Schalkwijk
SQL Server MVP
"Lasse Edsvik" <lasse@.nospam.com> wrote in message
news:OKlIIzxSFHA.1384@.TK2MSFTNGP09.phx.gbl...
> Hello
> I was wondering if you guys could help me, I have a table with Firstname
> and
> Lastname columns.
> And I want to pass a parameter @.SortCol char(10) and @.AscDesc, so I can
> sort
> Firstname ASC/DESC or Lastname ASC/DESC....
> how's that done, i need to put 4 different Cases?
> TIA
> /Lasse
>|||Lasse
ORDER BY CASE WHEN @.sort = 'col1' AND @.dir = 1 THEN col1 END ASC,
CASE WHEN @.sort = 'col1' AND @.dir = 0 THEN col1 END DESC,
CASE WHEN @.sort = 'col2' AND @.dir = 1 THEN col2 END ASC,
CASE WHEN @.sort = 'col2' AND @.dir = 0 THEN col2 END DESC
"Lasse Edsvik" <lasse@.nospam.com> wrote in message
news:OKlIIzxSFHA.1384@.TK2MSFTNGP09.phx.gbl...
> Hello
> I was wondering if you guys could help me, I have a table with Firstname
and
> Lastname columns.
> And I want to pass a parameter @.SortCol char(10) and @.AscDesc, so I can
sort
> Firstname ASC/DESC or Lastname ASC/DESC....
> how's that done, i need to put 4 different Cases?
> TIA
> /Lasse
>|||How do I use a variable in an ORDER BY clause?
http://www.aspfaq.com/show.asp?id=2501
AMB
"Lasse Edsvik" wrote:

> Hello
> I was wondering if you guys could help me, I have a table with Firstname a
nd
> Lastname columns.
> And I want to pass a parameter @.SortCol char(10) and @.AscDesc, so I can so
rt
> Firstname ASC/DESC or Lastname ASC/DESC....
> how's that done, i need to put 4 different Cases?
> TIA
> /Lasse
>
>|||The below uses one input variable to control which column to sort by, and
whether to cort asc or desc...
Use NorthWind
Declare @.Order TinyInt
Set @.Order = 1 -- 2,3,4
-- --
Select * From Customers
Order By Case @.Order
When 1 Then CompanyName
When 3 Then ContactName
End,
Case @.Order
When 2 Then CompanyName
When 4 Then ContactName
End Desc
"Lasse Edsvik" wrote:

> Hello
> I was wondering if you guys could help me, I have a table with Firstname a
nd
> Lastname columns.
> And I want to pass a parameter @.SortCol char(10) and @.AscDesc, so I can so
rt
> Firstname ASC/DESC or Lastname ASC/DESC....
> how's that done, i need to put 4 different Cases?
> TIA
> /Lasse
>
>sql