Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Order By Variable 1

Status
Not open for further replies.

ice7899

Programmer
May 14, 2006
59
GB
Using the following code to try to order my results but it's flagging an error. What's the correct way ?


ORDER BY

CASE @SortBy

WHEN 'ItemPhoto' THEN ItemPhoto

END

CASE @SortDirection
WHEN 'desc' THEN DESC
ELSE ASC
END


 
... flagging an error ...

I see three errors in the code you posted.


Suggestions for Getting Quick and Appropriate Answers faq183-874 in particular Item 15.

Learn to Use Tek-Tips Features faq183-3179
 
The Error I am getting is as follows

Server: Msg 156, Level 15, State 1, Procedure InitClsfd_qry, Line 200
Incorrect syntax near the keyword 'CASE'.

This refers to CASE @SortDirection

I have read that SQL Server is very fussy about the positioning of DESC or ASC.


 
I was playing around with this a bit and haven't come up with a 'slick' solution.

Suppose you had this table...

[tt]
Name FavoriteColor
---- -------------
Joe Blue
Jane Purple
[/tt]

If I understand correctly, you want to use a set of variables to determine the sorting (by column and Ascending/Descending).

You could use dynamic sql for this, but I wouldn't recommend it. This isn't very elegant, but should do the trick.

Code:
Insert Into @Temp Values('Jane', 'Purple')

Declare @SortBy VarChar(20)
Declare @SortDirection VarChar(10)

Set @SortBy = 'Name'
Set @SortDirection = 'Desc'

If @SortDirection = 'DESC'
  Select * 
  From 	 @Temp
  Order By Case When @SortBy = 'Name'
                Then Name
	        Else FavoriteColor
	        End [!]DESC[/!]
Else
  Select * 
  From   @Temp
  Order By Case When @SortBy = 'Name'
	        Then Name
	        Else FavoriteColor
	        End [!]Asc[/!]

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom
 
My select statement is very long and I don't want to have to copy it into 2 sections if I can avoid it.

The structure I am trying is this, which follows on from the SELECT statement. All variables have been declared.


If @SortDirection = 'DESC'

ORDER BY Case When @SortBy = 'ItemPhoto'
Then ItemPhoto
End DESC

Else

ORDER BY Case When @SortBy = 'ItemPhoto'
Then ItemPhoto
End Asc


But I'm getting the following error

Server: Msg 156, Level 15, State 1, Procedure InitClsfd_qry, Line 195
Incorrect syntax near the keyword 'Order'.

This refers to the first occurence of ORDER BY
 
Like I said...

I played around with this a little. Unfortunately, I haven't come up with a better solution. Perhaps someone else....

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom
 
I've been looking around. I don't think there is a better solution to the problem. I read somewhere that Microsoft admitted this. This way I can get it to work well. Thanks
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top