There are no stupid questions or smart questions, they are all information. Recently while working with a huge telecommunication organization on Comprehensive Database Performance Health Check, we figured out that one of the stored procedures is taking too long to run for a client. After a quick investigation, we figured out that there was a very poor code written in one of the Stored Procedure’s Order By clause which accepts parameters. Let us learn today how to order by a parameter.
Order By a Parameter
Let us discuss a scenario. We have a sample database WideWorldImporters and we have a table called Sales.Invoices. Now let us assume that we want to order the table ascending and descending based on the sort direction and also based on columns passed in the variable. Here is the T-SQL example of how we can do that easily.
DECLARE @SortDirection VARCHAR(10); DECLARE @SortBy VARCHAR(100); SET @SortDirection = 'D'; SET @SortBy = 'InvoiceID'; SELECT * FROM [Sales].[Invoices] ORDER BY CASE WHEN @SortDirection = 'A' THEN CASE WHEN @SortBy = 'OrderID' THEN OrderID WHEN @SortBy = 'InvoiceID' THEN InvoiceID END END ASC , CASE WHEN @SortDirection = 'D' THEN CASE WHEN @SortBy = 'OrderID' THEN OrderID WHEN @SortBy = 'InvoiceID' THEN InvoiceID END END DESC;
In the above example, @SortDirection is descending and the column to SortBy is InvoiceID. You can use see how the Order by constructed in the above example and also add more columns if you want to. There are many such simple tricks out there but often we miss them out. Once I fixed the example for the stored procedure, the stored procedure started to work efficiently.
Here are a few related blog posts:
- SQL SERVER – CASE Statement/Expression Examples and Explanation
- What is Alternative to CASE Statement in SQL Server? – IIF Function
- How to Write Case Statement in WHERE Clause? – Interview Question of the Week #127
- SQL SERVER – Implementing IF … THEN in SQL SERVER with CASE Statements
- How to Use a CASE Statement in the WHERE Clause? – Interview Question of the Week #239
- SQL SERVER – Computed Column and Conditions with Case Statement
- How to Use OR Condition in CASE WHEN Statement? – Interview Question of the Week #102
- SQL SERVER – CASE Statement in ORDER BY Clause – ORDER BY using Variable
If you have any such example, please post it here and I will be happy to post it with due credit to you. SQL Server is a very interesting subject. You can connect with on LinkedIn here.
Reference:Â Pinal Dave (https://blog.sqlauthority.com)
7 Comments. Leave new
Good info.
Sorry, got it now…
Excelente
Hello,
I have two issues with this
1. That “select * ” works perfect, but when I add “distinct” and list of fields I get error “ORDER BY items must appear in the select list if SELECT DISTINCT is specified.” even so all fields listed.
2. I need to sort either by ID or by Name
In your example both sorting fields are integers. In my case one integer and another one is varchar. And when I switch to the second one I get an error :”Conversion failed when converting the varchar value….. to data type int.”
I would love to hear from you
Evelyn, long term subscriber :)
if want sort by date at a once & string at a once, if is throwing error as below
CODE:
ORDER BY
CASE WHEN @P_ShortDirection = ‘asc’ THEN
CASE
WHEN @P_ShortBy = ‘Title’ THEN Title
WHEN @P_ShortBy = ‘SubmittedDate’ THEN SubmittedDate
END
END ASC
, CASE WHEN @P_ShortDirection = ‘desc’ THEN
CASE
WHEN @P_ShortBy = ‘Title’ THEN Title
WHEN @P_ShortBy = ‘SubmittedDate’ THEN SubmittedDate
END
END DESC
ERROR:
Conversion failed when converting date and/or time from character string.
If you’re mixing datatypes in your sort you’ll have to create case statements for each type e.g.
ORDER BY
CASE WHEN @SortBy = ‘OrderID’ AND @SortDirection = ‘A’ THEN OrderID END ASC,
CASE WHEN @SortBy = ‘InvoiceID’ AND @SortDirection = ‘A’ THEN InvoiceID END ASC,
CASE WHEN @SortBy = ‘OrderID’ AND @SortDirection = ‘D’ THEN OrderID END DESC,
CASE WHEN @SortBy = ‘InvoiceID’ AND @SortDirection = ‘D’ THEN InvoiceID END DESC
Hi @gmb, you saved my life. It does indeed need each case statements for each type when then are different datatypes