Dynamic Order By problem - 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 2000 Forums

Transact-SQL (2000)

Dynamic Order By problem

Author

Topic

dhw
Constraint Violating Yak Guru

332 Posts

Posted - 2004-04-03 : 11:58:59

Hello...

I need to pass in an Order By clause to my stored procedure.

I searched through the forums here and found Garth's "Dynamic Order By" article. It was a tremendous help. However, one problem persists....I cannot seem to get the ASC or DESC part of my order statement to work. I need to be able to sort the data by either DESC or ASC....as the users will pick a column and also the sort direction.

As a test, I tried this with the Pubs database:
declare @SortOrder TINYINT
set @sortOrder = 2
SELECT au_lname,au_fname
FROM authors
ORDER BY CASE WHEN @SortOrder = 1 THEN au_lname
WHEN @SortOrder = 2 THEN au_fname
END

This works great. When I try and introduce the direction (either ASC or DESC) the query analyzer just reports errors.

First, I tried to include ASC or DESC with the above statement like:
ORDER BY CASE WHEN @SortOrder = 1 THEN au_lname ASC
WHEN @SortOrder = 2 THEN au_lname DESC
WHEN @SortOrder = 3 THEN au_fname ASC
WHEN @SortOrder = 4 THEN au_fname DESC
END

That didn't work..."Incorrect syntax near the keyword "ASC"
So I thought I'd also try this with a variable
I declared another variable, @SortDirection TINYINT and set it to either 1 or 2 (1 = asc and 2 = desc).

ORDER BY CASE WHEN @SortOrder = 1 THEN au_lname
WHEN @SortOrder = 2 THEN au_fname
END
CASE WHEN @SortDirection = 1 THEN ASC
WHEN @SortDirection = 2 THEN DESC
END

This doesn't work either and I get the error "Incorrect syntax near the keyword 'case'".

Does anyone know of a way to handle this without resorting to dynamic sql?

Thanks....dw

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-04-03 : 12:09:01

read the comments from the article (there's 57 of them !), and do a search in the forums for "dynamic order by" (exact phrase). you'll find lots of answers.

- Jeff

dhw
Constraint Violating Yak Guru

332 Posts

Posted - 2004-04-04 : 01:02:44

I did search the forums on "dynamic order by", that is how I found Garth's article as well as some other posts regarding the same subject.

I was just having a problem getting the one column plus Asc or Desc to work properly. The examples from the comments on the article appear to deal with multiple column sorting...I am trying to just sort on a single column. The only solution appears to be including a NULL order by column (like some of the examples) to get around the need to use Asc and Desc with a single column.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-04-04 : 09:08:21

here's 4 i found pretty quickly ...hope this helps !

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=25950&SearchTerms=dynamic+order+by
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=13413&SearchTerms=dynamic+order+by
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=13350&SearchTerms=dynamic+order+by
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=12447&SearchTerms=dynamic+order+by

read the comments carefully ... by defintion, you can only specify the sort order of a column as a constant ... so if you wish to potentially sort a column as eitehr DESC or ASC, then you need to have TWO sort columns (one for each), and make one of them a constant such as NULL if you don't need that direction.

- Jeff

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-04-04 : 14:33:24

What the forum posts are saying basically is:

DECLARE @SortOrder TINYINT

SELECT @sortOrder = 1

SELECT au_lname,au_fname
FROM authors
ORDER BY
CASE WHEN @SortOrder = 1 THEN au_lname END ASC,
CASE WHEN @SortOrder = 2 THEN au_lname END DESC,
CASE WHEN @SortOrder = 3 THEN au_fname END ASC,
CASE WHEN @SortOrder = 4 THEN au_fname END DESC

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources