0

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
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
user1430949
  • 849
  • 1
  • 6
  • 10
  • 1
    It's absolutely pointless to try and insert into a temporary table in a specific order - when you want the rows back in a specific order, you'll need a specific `ORDER BY` anyway ..... so what are you trying to achieve, really? – marc_s Aug 02 '12 at 15:00
  • I want to use this query for pagination.I will select from my temporary table and I need the autoid to be ordered by the column that is passed in as a parameter. I edited my question to have my final select statement. – user1430949 Aug 02 '12 at 15:25
  • You can do this from the original data - using a CTE and `ROW_NUMBER()` - no need to put that stuff into a separate temporary table first, just to paginate..... – marc_s Aug 02 '12 at 15:27
  • I am using sql server 2000 which does not have the function row_number – user1430949 Aug 02 '12 at 15:39
  • I am also selecting from a long query. I only posted the question using a table as I did not think it was necessary to have the entire query here. basically my question is: Is there any way to select into a temporary table ordering conditionally??? – user1430949 Aug 02 '12 at 15:44
  • Not the way you're trying to do it, no. You'd have to build up a SQL query as a string, and then execute it (technique called *dynamic SQL*) – marc_s Aug 02 '12 at 15:45
  • I noticed that your @OrderBy variable is varchar(10). If your real code has column names greater than 10 characters, this would explain why you're not getting the correct sort. – George Mastros Aug 02 '12 at 18:52
  • Your "order by case" line has "@orderby = 'co1'" instead of "@orderby = 'Col1'" – George Mastros Aug 02 '12 at 18:57

3 Answers3

1

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
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
  • the temp table can be sorted by six columns, and each can be can sorted asc or desc.I don't think I want to write 12 separate queries. – user1430949 Aug 02 '12 at 15:50
1

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
George Mastros
  • 24,112
  • 4
  • 51
  • 59
0

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
AnandPhadke
  • 13,160
  • 5
  • 26
  • 33