Binaryworld - Tricky order by without writing dynamic T-SQL ... [ SQL Server -> T-SQL ]

This example shows you how to write dynamic order by statement without writing Dynamic T-SQL which is required to re-compile the execution plan every time you run it.

Click here to copy the following block

if object_id('t1') is not null
drop table t1

go

CREATE TABLE T1
(
city VARCHAR(50) ,
state VARCHAR(50)
)

INSERT T1 VALUES('Atlanta', 'GA')
INSERT T1 VALUES('Chicago', 'IL')
INSERT T1 VALUES('Richmond', 'VA')
INSERT T1 VALUES('Pittusberg', 'PA')
INSERT T1 VALUES('Albany', 'GA')
INSERT T1 VALUES('Huntsville', 'AL')

declare @sortcolumn varchar(50)
declare @sortorder varchar(50)

set @sortcolumn='State'
set @sortorder='DESC'

select
city,
State
from t1
order by
case when @sortcolumn='city' and @sortorder='ASC' then city end asc,
case when @sortcolumn='city' and @sortorder='DESC' then city end desc,
case when @sortcolumn='state' and @sortorder='ASC' then state end asc,
case when @sortcolumn='state' and @sortorder='DESC' then state end desc