Rob Critchley's Blog

Don't know whether anyone is likely to have to do stuff like this - but it's nice to know you can if you want... It's possible to include case statements in an order by clause. The example below is a bit contrived and has no use whatsoever but I have just recently wanted to do just such a thing!

begin tran

create table #t (a int, b int)

insert into #t values(1,3)
insert into #t values(2,2)
insert into #t values(3,1)

select * from #t order by a

select * from #t order by b

select * from #t order by case when a<2 then a else b end

rollback tran

Have you ever wanted to get the results from a stored procedure into a table for further processing. You can call "insert into <table> exec <stored proc>" see below for an example:

create proc temp1
as
select 1, 2
union
select 3, 4
go

create table #t (a int, b int)

insert into #t exec temp1

select * from #t
drop table #t

go

drop proc temp1

go