Solved

dynamic sort by and sort order

Posted on 2014-02-12
5
631 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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:Scott Pletcher
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 32

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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how the fundamental information of how to create a table.

695 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