You can use a complicated order by
clause. That requires one case
for each sort direction and each data type. With this example dataset:
create table t1 (id int, name varchar(50), created date);
insert t1 values
(1, 'Chihiro Ogino', '2012-01-01'),
(2, 'Spirit of the Kohaku River', '2012-01-03'),
(3, 'Yubaba', '2012-01-02');
You could use an order by
clause like:
declare @sortColumn varchar(50) = 'created'
declare @sortOrder varchar(50) = 'DESC'
select *
from t1
order by
case
when @sortOrder <> 'ASC' then 0
when @sortColumn = 'id' then id
end ASC
, case
when @sortOrder <> 'ASC' then ''
when @sortColumn = 'name' then name
end ASC
, case
when @sortOrder <> 'ASC' then cast(null as date)
when @sortColumn = 'created' then created
end ASC
, case
when @sortOrder <> 'DESC' then 0
when @sortColumn = 'id' then id
end DESC
, case
when @sortOrder <> 'DESC' then ''
when @sortColumn = 'name' then name
end DESC
, case
when @sortOrder <> 'DESC' then cast(null as date)
when @sortColumn = 'created' then created
end DESC
Working example at SQL Fiddle.
Another option is to create the query dynamically, and run it with exec
. For example:
declare @sql nvarchar(max)
set @sql="select * from YourTable order by " + @sortColumn + ' ' + @sortDir
exec (@sql)