Conditional Order By clause?

Question

Conditional Order By clause?

Andervin

Valued Member

Points: 61
More actions
August 8, 2003 at 7:50 pm

#80786

Hey, I'm trying to creat a simple proc to return an ordered list depending on which column the user selects to sort by, desc or asc. Is this possible?
Developer Developer Developer Developer Developer Developer Developer *jumps around like a moron* Developer Developer

Developer Developer Developer Developer Developer Developer Developer *jumps around like a moron* Developer Developer

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Gabor Nyul SSCrazy Eights Points: 9459 More actions · Answer 1

As far as I know the only possibility you have is to build up dinamically your query within the proc. Of cours doing so you will lose the optimization made by the precomiler but at least you will get what you want.

An example:

create proc sample @OrderedBy varchar(255)

as

declare @SqlString varchar(8000)

select @SqlString = 'select a, b, c, d from MyTable order by ' + @OrderedBy

exec (@SqlString)

go

And you can call your proc like:

exec sample 'a desc, c asc, d desc, b'

I hope this helps

Gabor

Bye
Gabor

bspiglejr SSC Enthusiast Points: 121 More actions · Answer 2

Another possibility if the number of order by rows is small and predetermined

select * from

where selectColumn=@SelectValue

order by case when @SortParm='column1' then column1

when @SortParm='column2' then column2

when @SortPram='column3' then column3

...

else DefaultSortColumn

kenwallacedesign Old Hand Points: 385 More actions · Answer 3

Excellent tip, bspiglejr!

Now, when using your CASE method of determining the ORDER BY criteria, would it be possible to also add an additional CASE statement to determine ASC or DESC?

Edited by - kenwallacedesign on 08/10/2003 7:52:55 PM

Andervin Valued Member Points: 61 More actions · Answer 4

Thanks guys!

Developer Developer Developer Developer Developer Developer Developer *jumps around like a moron* Developer Developer

susan70739 SSC Enthusiast Points: 174 More actions · Answer 5

If the columns are not the same datatype, then you can use:

select * from

where selectColumn=@SelectValue

order by case when @SortParm='column1' then column1 end,

case when @SortParm='column2' then column2 end,

case when @SortPram='column3' then column3 end