SQL SERVER – How to Order By a Parameter?

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.

SQL SERVER - How to Order By a Parameter? orderbyaparameter-800x323

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:

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)

, , ,
Previous Post
How to Find IP Address of All SQL Server Connection? – Interview Question of the Week #280
Next Post
Why SQL Server Doesn’t Give the Memory Back to OS?

Related Posts

3 Comments. Leave new

Leave a Reply

Menu