ORDER BY and CASE headache. - 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)

ORDER BY and CASE headache.

Author

Topic

whitesword
Starting Member

17 Posts

Posted - 2003-08-02 : 14:48:57

Here's an example table format.

CREATE TABLE PostResults (
PostID INT IDENTITY(1,1) PRIMARY KEY,
LastPostTime DATETIME NOT NULL,
Subject VARCHAR(50) NULL,
Author VARCHAR(500 NULL,
Replies INT NULL
}

I have an SP to get a certain page to be displayed (thanks to an earlier post [url]http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=28181[/url]) however I am trying to do this without the need without an EXEC() statement.

PROCEDURE GetPostPage
@sortcolumn VARCHAR(25)
@sortorder VARCHAR(5)
AS
DECLARE @sql
-- Chop, snip, slice, gobble :) --
SELECT @sql='SELECT * FROM PostResults ORDER BY ' + @sortcolumn + ' ' + @sortorder'
EXEC @sql

However, after much perusing this forum, I have tried to steer away from the above line (you know all what I mean) and came up with something like:

IF @sortorder='DESC'
BEGIN
SELECT * FROM PostResults
ORDER BY
CASE @sortcolumn
WHEN 'LastPostTime' THEN LastPostTime
WHEN 'Replies' THEN Replies
WHEN 'Subject' THEN Subject
WHEN 'Author' THEN Author
END
DESC
END
ELSE
BEGIN
SELECT * FROM PostResults
ORDER BY
CASE @sortcolumn
WHEN 'LastPostTime' THEN LastPostTime
WHEN 'Replies' THEN Replies
WHEN 'Subject' THEN Subject
WHEN 'Author' THEN Author
END
END

I have two problems with it...
1) It just looks plain ugly. There has to be a way I can add @sortorder to the end of the ORDER BY clause and

2) Server: Msg 241, Level 16, State 1, Line 7 Syntax error converting datetime from character string. A squizz at BOL indicates that it's a data precedence thing. Because there's a DATETIME field in there, the CASE clause will come back as a DATETIME format.

Is there another way to do this, else I'll be forced into a giant IF block for each variation and I'll only do that as a last resort.

Cheers
Roger

robvolk
Most Valuable Yak

15732 Posts

Posted - 2003-08-02 : 15:13:03

SELECT * FROM PostResults
ORDER BY
CASE @sortorder WHEN 'DESC' THEN
CASE @sortcolumn
WHEN 'LastPostTime' THEN LastPostTime
WHEN 'Replies' THEN Replies
WHEN 'Subject' THEN Subject
WHEN 'Author' THEN Author
END
ELSE Null END DESC,

CASE @sortorder WHEN 'ASC' THEN
CASE @sortcolumn
WHEN 'LastPostTime' THEN LastPostTime
WHEN 'Replies' THEN Replies
WHEN 'Subject' THEN Subject
WHEN 'Author' THEN Author
END
ELSE Null END ASC

It's really nothing more than including both ASC and DESC clauses, and nesting the CASE expression that chooses the sort column inside another CASE to evaluate the direction. Only one direction will be evaluated based on the direction passed; the other clause will evaluate to Null, which sorts the same regardless of direction.

whitesword
Starting Member

17 Posts

Posted - 2003-08-02 : 15:36:38

Unfortunately it doesn't get rid of the error message.

If I set @sortkey='LastPostTime' or 'Replies", then it works wonderfully as they both come back with a numeric result. If @sortkey is set to eith subject or author, then that very annoying error message comes up.

How do I get around that?

robvolk
Most Valuable Yak

15732 Posts

Posted - 2003-08-02 : 15:42:16

SELECT * FROM PostResults
ORDER BY
CASE @sortorder WHEN 'DESC' THEN
CASE @sortcolumn
WHEN 'LastPostTime' THEN CAST(LastPostTime as varbinary)
WHEN 'Replies' THEN CAST(Replies as varbinary)
WHEN 'Subject' THEN CAST(Subject as varbinary)
WHEN 'Author' THEN CAST(Author as varbinary)
END
ELSE Null END DESC,

CASE @sortorder WHEN 'ASC' THEN
CASE @sortcolumn
WHEN 'LastPostTime' THEN CAST(LastPostTime as varbinary)
WHEN 'Replies' THEN CAST(Replies as varbinary)
WHEN 'Subject' THEN CAST(Subject as varbinary)
WHEN 'Author' THEN CAST(Author as varbinary)
END
ELSE Null END ASC

For ORDER BY to parse correctly, all expressions returned by CASE have to be of the same datatype. Casting them as varbinary does this as well as preserve their value for purposes of ordering.

whitesword
Starting Member

17 Posts

Posted - 2003-08-02 : 16:06:20

Wahoooo

Give the man a beer...
Thanks Rob.

I've been banging my head against this for too many hours. Never even thought to of CASTing them. Cheers Dude...

Now to get that paging algorithm by Jeff to work with this....

Cheers
Roger

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources