Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
Solved

# dynamic sort by and sort order

Posted on 2014-02-12
Medium Priority
689 Views
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
``````

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

LVL 34

Accepted Solution

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

ID: 39853746
Hi BriCrowe.

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

Thank you much.
0

LVL 34

Expert Comment

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

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

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

Question has a verified solution.

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

What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
###### Suggested Courses
Course of the Month10 days, 18 hours left to enroll