?
Solved

dynamic sort by and sort order

Posted on 2014-02-12
5
Medium Priority
?
711 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 2000 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 70

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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Suggested Courses

864 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