Sort by ascending/descending non dynamic

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Sort by ascending/descending non dynamic

Author

Topic

tishri
Yak Posting Veteran

95 Posts

Posted - 2006-04-08 : 07:54:18

select * from @mytable
order by
case when @orderby = 'fname' then firstname end,
case when @orderby = 'lname' then lastname end,
case when @orderby = 'mname' then middlename end
ASC <---- this statement causes an error

how can i add order by ascending or descending without using dynamic sql query
using the above statement.

TCC

tishri
Yak Posting Veteran

95 Posts

Posted - 2006-04-08 : 08:08:40

i also tried this one.. but still it wont work

create procedure DisplayResult
@orderby varchar(20),
@order varchar(4)
as
select * from @mytable
order by
case when @orderby = 'fname' then firstname end,
case when @orderby = 'lname' then lastname end,
case when @orderby = 'mname' then middlename end
case when @order='asc' then ASC else DESC end
go

exec dbo.DisplayResult ('fname','desc')

TCC

tishri
Yak Posting Veteran

95 Posts

Posted - 2006-04-08 : 08:11:38

What do you think guys. do i need to use dynamic sql query for this one?
or there is other way around.

TCC

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-04-08 : 08:11:43

select * from @mytable
order by
case when @orderby = 'fname' then firstname ~~end,~~
~~case~~ when @orderby = 'lname' then lastname ~~end,~~
~~case~~ when @orderby = 'mname' then middlename end
ASC

tishri
Yak Posting Veteran

95 Posts

Posted - 2006-04-08 : 08:15:48

khtan i encountered error with the stament you wrote.. it happens when

select * from @mytable
order by
case when @orderby = 'fname' then firstname
when @orderby = 'lname' then lastname
when @orderby = 'mname' then middlename
when @orderby = 'account_no' then account_no end
ASC

account_no is of type integer this causes an error if i use it within the same case statement.

TCC

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-04-08 : 08:23:12

convert it to varchar


select * from @mytable
order by
case when @orderby = 'fname' then firstname
when @orderby = 'lname' then lastname
when @orderby = 'mname' then middlename
when @orderby = 'account_no' then convert(varchar(10), account_no) end
ASC

tishri
Yak Posting Veteran

95 Posts

Posted - 2006-04-08 : 21:28:57

thanks khtan

TCC

jallen
Starting Member

1 Post

Posted - 2006-04-23 : 00:57:10

Try combining logic of field and sort order.
Joey

The case is a powerful statement......

select name, id, sdate
from (
--create some data to use
select name='abc', id=3, sdate = getdate()-1
union all select 'xyz', 1, getdate()-2
union all select 'mno', 2, getdate()-3
) X
order by
--if ordering by name asc
case when 1=0 and 2=2 then
name
else
--this nullify the asc at the end, no pun intended
null
end asc,

--if ordering by name desc
case when 3=3 and 4=4 then
name
else
null
end desc,

--through in another field
case when 1=1 then
id
else
null
end

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources