Nov 26, 2007

Conditional TSQL sorting using CASE statement

We can do conditional sorting in 2 ways.
1. using dynamic SQL
2. using CASE statements.

1. using Dynamic SQL
DECLARE @sql nvarchar(400), @input int, @paramdef nvarchar(100)
SELECT @input = 2, @paramdef = N'@input int'

set @sql = N'if @input = 1 select * from acctlist order by email ASC; if @input = 2 select * from acctlist order by email DESC'

exec sp_executesql @sql, @paramdef, @input

2. using CASE statements:
select * from authors
order by
CASE WHEN @col = 'firstname' AND @order = 'ASC' THEN firstname END ASC,
CASE WHEN @col = 'firstname' AND @order = 'DESC' THEN firstname END DESC,
CASE WHEN @col = 'email' AND @order = 'ASC' THEN email END ASC,
CASE WHEN @col = 'email' AND @order = 'DESC' THEN email END DESC,
----Default sort by lastname
CASE WHEN @order = 'ASC' THEN lastname END ASC,
CASE WHEN @order = 'DESC' THEN lastname END DESC

kick it on DotNetKicks.com

No comments: