Dynamic Sorting in Select - SQL Server Forums

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 2005 Forums

Transact-SQL (2005)

Dynamic Sorting in Select

Author

Topic

s1234
Starting Member

5 Posts

Posted - 2009-12-24 : 06:38:27

Hi,
I need to sort the data dynamically which is prefered by user.
Below is some sample script .

I need to sort the data based on user preferemce which is stored in a variable.. when i run the below select query i get an error like this - Incorrect syntax near the keyword 'asc'.

create table test_sort (id int , name char(5))

insert into test_sort values (1,'abc')

insert into test_sort values (2,'def')

declare @sort1 varchar(5)
declare @sort2 varchar(5)
set @sort2 = 'name'
set @sort1 = 'id'
select id, name from test_sort
order by case when @sort1 = 'id' and @sort2 = 'name' then id asc, name asc end

Can anyone here please help me out..

Thanks in advance

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-12-24 : 07:09:04

Try this:
order by
case when @sort1 = 'id' and @sort2 = 'name' then row_number() over (order by id asc, name asc) end

No, you're never too old to Yak'n'Roll if you're too young to die.

s1234
Starting Member

5 Posts

Posted - 2009-12-24 : 07:17:05

oops. i am sorry.. i have posted in a wrong section... i am using sql server 2000. we dont have row_number() function in 2000 right

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-12-24 : 07:44:45

Try this

order by case when @sort1 = 'id' and @sort2 = 'name' then id ,name end asc

Madhivanan

Failing to plan is Planning to fail

s1234
Starting Member

5 Posts

Posted - 2009-12-24 : 08:05:57

Hi Madhivanan thanks for u r reply..
i am getting an error like this
Incorrect syntax near ','

. Just now my lead told that sort order also would be sent by user, which has to be done dynamically.
so i will have two additional variables
@sort_order1
@Sort_Order2
with values as ascending and descending.@Sort_order1 is meant for @sort1 and @sort_order2 is meant for @sort2..

s1234
Starting Member

5 Posts

Posted - 2009-12-24 : 08:28:00

Please let me know if my question is confusing

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-12-24 : 08:29:20

Try this

order by
case when @sort1 = 'id' and @sort2 = 'name' then id end asc,
case when @sort1 = 'id' and @sort2 = 'name' then name end asc

Madhivanan

Failing to plan is Planning to fail

DP978
Constraint Violating Yak Guru

269 Posts

Posted - 2009-12-24 : 08:35:36

create table #test_sort (id int , name char(5))

insert into #test_sort values (1,'abc')

insert into #test_sort values (2,'def')

declare @sort1 varchar(5)
declare @sort2 varchar(5)
set @sort2 = 'name'
set @sort1 = 'id'
select id, name from #test_sort
order by case when @sort1 = 'id' and @sort2 = 'name' then id end asc,
case when @sort1 = 'id' and @sort2 = 'name' then name end asc

EDIT!: Madiv, you beat me by a couple minutes! that will teach me not to refresh more :)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources