Dynamic Order By and Sort

Question

Dynamic Order By and Sort

Steve-335899

SSChasing Mays

Points: 639
More actions
January 30, 2007 at 1:07 pm

#174586

Please, if anyone can help I would greatly appreciate it.
This is my ORDER BY CASE statement:
ROW_NUMBER() OVER(ORDER BY
-- #### STRING COLUMNS #### --
CASE @xOrderByColumn
WHEN ''Description'' THEN A.Description
WHEN ''Description1'' THEN B.Description
WHEN ''Description2'' THEN C.Description
END,
-- #### NUMERIC COLUMNS #### --
CASE @xOrderByColumn
WHEN ''DataSourceMemberID'' THEN A.DataSourceMemberID
WHEN ''DataSourceID'' THEN DSM.DataSourceID
WHEN ''DataSourceMemberTypeID'' THEN DSM.DataSourceMemberTypeID
END
) AS RowNum
I would like to also have the Sort Order dynamic as well. So I tried this:
ROW_NUMBER() OVER(ORDER BY
-- #### STRING COLUMNS #### --
CASE @xOrderByColumn
WHEN ''Description'' THEN A.Description
WHEN ''Description1'' THEN B.Description
WHEN ''Description2'' THEN C.Description
END,
-- #### NUMERIC COLUMNS #### --
CASE @xOrderByColumn
WHEN ''ID1'' THEN A.ID
WHEN ''ID2'' THEN B.ID
WHEN ''ID3'' THEN C.ID
END
' + @SortOrder + '
) AS RowNum
It ignores the value you pass in the @SortOrder and sets it to ASC. I've also tried passing the Sort Order as a parameter and still nothing. can someone help me and point me in the right direction.
Thanks,
Steve

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

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

JDixon-385826 SSCrazy Points: 2723 More actions · Answer 1

Are you using sp_executeSql? To work correctly as an order by clause you may have to. Useing sp_executeSql should let you make it as dynamic as you want it.

Prasad Bhogadi SSCrazy Eights Points: 9235 More actions · Answer 2

If you have definite set of options its always better to use CASE in order by clause and define the conditions and coming to Sort Order, I don't see a reason why you want to adopt to Dynamic SQL which is not advised. You can just write two sets of queries one each of ASC and DESC and based on the parameter call the respective query. Stay away from Dynamic SQL if possible.

Prasad Bhogadi
www.inforaise.com

Steve-335899 SSChasing Mays Points: 639 More actions · Answer 3

Hey guys,

After a couple of head pounding hours I got it to work. I need the dynamics of it as this will be used in an application that will have a paging feature, so I need to have it that way unfortunately. The mistake was that the ' + @SortOrder + ' needs to be after both ENDs in each case statement to work. Yeah, I know, I shoulda known.

Thanks for the time in lookin at my post

Steve

Gary Woodfine-408359 SSC Veteran Points: 225 More actions · Answer 4

Do you have the code of your SQL to show, I am coming across a similar situation

Kindest Regards,

Gary Woodfine
threenineconsulting.com

Steve-335899 SSChasing Mays Points: 639 More actions · Answer 5

Hey Gary,

Here is what I was able to do. It uses dynamic SQL, so the @xOrderByColumn is a parameter

ORDER BY

-- We need a case option for all existing columns

-- being returned in the Subset.

CASE @xOrderByColumn

WHEN ''AID'' THEN CAST(A.AID AS nvarchar(50))

WHEN ''CID'' THEN CAST(A.CID AS nvarchar(50))

WHEN ''PID'' THEN CAST(A.PID AS nvarchar(50))

WHEN ''ATID'' THEN CAST(A.ATID AS nvarchar(50))

WHEN ''Description'' THEN A.Description

WHEN ''DateCreated'' THEN CAST(A.DateCreated AS nvarchar(50))

WHEN ''DateLastModified'' THEN CAST(A.DateLastModified AS nvarchar(50))

WHEN ''P'' THEN P.Description

WHEN ''AT'' THEN AT.Description

WHEN ''C'' THEN C.Description

END '

+ @sort + '

--You have to cast INTs to the text format you have. Same with Bits.

If you have anymore questions, ask away.

Prasad Bhogadi SSCrazy Eights Points: 9235 More actions · Answer 6

Hi Gary,

Please post your SQL Query so that we can suggest you the solution.

Prasad Bhogadi
www.inforaise.com