SQL Server 2000: select into case when in order by clause

Question

I am trying to select rows into a temporary table with a CASE statement in the ORDER BY clause but records are not being sorted on insert.

Declare @orderby varchar(10) , @direction varchar(10)
set @orderby = 'col1'
set @direction = 'desc'
select identity (int) as autoid, *
into #temp
from table 
order by case when @direction = 'desc' and @orderby = 'co1' then col1 end desc

declare @startrow int
declare @maxrows int
set @starrow = 19
set @maxrow = 30
set rowcount @maxrows
select * from #temp 
where autoid > @startrow

Answer 1

You can achieve this not using #temp tables insted use normal table temp. Later when you are done with your process you can drop it at the end.

declare @dir varchar(10)='desc'
DECLARE @str varchar(1000)
SET @str='select identity (int) as autoid,
* into temp from cust1 order by TransactionType '+@dir 
exec(@str)
select * from temp

Answer 2

Worst case - you'll just have to use two separate SQL queries to achieve your goal:

if @direction = 'desc'
  select identity (int) as autoid, *
  into #temp
  from table 
  order by col1 desc

if @direction = 'asc'
  select identity (int) as autoid, *
  into #temp
  from table 
  order by col1 asc

Answer 3

You'll need to use multiple sort conditions in your order by clause to handle this properly. The problem with this approach is that the performance will be bad when you have a lot of rows in the table because of that nasty sort operation.

Instead, you may be better off using dynamic SQL (as someone else suggested).

Declare @orderby varchar(100) , @direction varchar(10)
set @orderby = 'col1'
set @direction = 'desc'
select identity (int) as autoid, *
into #temp
from table 
order by case when @direction = 'desc' and @orderby = 'col1' then col1 end desc,
         case when @direction = 'asc'  and @orderby = 'col1' then col1 end,
         case when @direction = 'desc' and @orderby = 'col2' then col2 end desc,
         case when @direction = 'asc'  and @orderby = 'col2' then col2 end,
         case when @direction = 'desc' and @orderby = 'col3' then col3 end desc,
         case when @direction = 'asc'  and @orderby = 'col3' then col3 end,
         case when @direction = 'desc' and @orderby = 'col4' then col4 end desc,
         case when @direction = 'asc'  and @orderby = 'col4' then col4 end,
         case when @direction = 'desc' and @orderby = 'col5' then col5 end desc,
         case when @direction = 'asc'  and @orderby = 'col5' then col5 end