Order By Variable 1

[ice7899]

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

 

[rac2]

... 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

 

[ice7899]

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.

 

[gmmastros]

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.

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

 

[ice7899]

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

 

[gmmastros]

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

 

[ice7899]

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