Solved

dynamic sort by and sort order

Posted on 2014-02-12
5
549 Views
Last Modified: 2014-02-19
Hello,

I am on SQL Server 2012 trying to add a Dynamic ORDER BY clause.

	ORDER BY 
		CASE 
			WHEN @SortBy = 1 THEN LastRunTime 
                        WHEN @SortBy = 2 THEN Name 
		END			
		CASE 
			WHEN @SortOrder = 1 THEN ASC
			WHEN @SortOrder = 2 THEN DESC
		END

Open in new window


But it does not seem to let me specify the direction with the use of a CASE statement.

Can anyone please teach me a work around that does not involve dynamic sql, or having to  write twice the entire query using an IF.

If possible that is.

Thank you so much.
0
Comment
Question by:metropia
5 Comments
 
LVL 34

Accepted Solution

by:
Brian Crowe earned 500 total points
ID: 39853615
SELECT ...,
   CASE
      WHEN @SortBy = 1 AND @SortOrder = 1 THEN ROW_NUMBER() OVER(ORDER BY LastRunTime ASC)
      WHEN @SortBy = 1 AND @SortOrder = 2 THEN ROW_NUMBER() OVER(ORDER BY LastRunTime DESC)
      WHEN @SortBy = 2 AND @SortOrder = 1 THEN ROW_NUMBER() OVER(ORDER BY Name ASC)
      WHEN @SortBy = 2 AND @SortOrder = 2 THEN ROW_NUMBER() OVER(ORDER BY Name DESC)
   END AS RowNumber  
FROM ...
ORDER BY RowNumber
0
 

Author Comment

by:metropia
ID: 39853746
Hi BriCrowe.

Thank you for your example.

Would you mind explaining real briefly what this part does:
ROW_NUMBER() OVER

Thank you much.
0
 
LVL 34

Expert Comment

by:Brian Crowe
ID: 39853777
Basically it is calculating an index value or "row number' for each row based on the criteria within the OVER clause.  We use the CASE statement to vary the way we calculate the row number depending on your @SortBy and @SortOrder parameters.

If you end up having issues referencing RowNumber in the ORDER BY clause then you may need to use an ordinal reference.  For instance if the RowNumber is the first column in your SELECT then "ORDER BY 1"
0
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 39853784
I'd take a different approach, specifying both ASC and DESC sort values but leaving the unneeded one blank/empty, so it won't affect the sort anyway.

That is, when the @SortOrder = 1, ASC, the DESC sort value will always be blank (''), and when the @SortOrder = DESC, the ASC sort value will always be blank.


ORDER BY
    CASE WHEN @SortOrder = 1 THEN
        CASE WHEN @SortBy = 1 THEN CONVERT(varchar(30), LastRunTime, 121)
                  WHEN @SortBy = 2 THEN Name
        END
        ELSE ''
        END ASC,
    CASE WHEN @SortOrder = 2 THEN
        CASE WHEN @SortBy = 1 THEN CONVERT(varchar(30), LastRunTime, 121)
                  WHEN @SortBy = 2 THEN Name
        END
        ELSE ''
        END DESC
0
 
LVL 31

Expert Comment

by:awking00
ID: 39854429
case when @sortby = 1 and @sortorder = 1 then LastRunTime ASC
     when @sortby = 1 and @sortorder = 2 then LastRunTime DESC
     when @sortby = 2 and @sortorder = 1 then Name ASC
     when @sortby = 2 and @sortorder = 2 then Name DESC
end
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now