Author: Steven Neiland
Published:

Warning: This blog entry was written two or more years ago. Therefore, it may contain broken links, out-dated or misleading content, or information that is just plain wrong. Please read on with caution.

In a previous posting I discussed Dynamic Paging In TSQL Stored Procedures. As a followup today I am going to show a slightly simpler usage of the same technique to allow for dynamic sorting inside a SQL server stored procedure.

Start: Basic Query

To start lets take a simple query to get a userId and username from a table of users.

SELECT 
      userId
      ,username
FROM
      users

For fun lets throw in a @top variable to limit the number of records we get.

DECLARE @top INTEGER

SET @top = 5

SELECT
      TOP(@top)
      userId
      ,username
FROM
      users

Give that a run and everything works fine. We get back just 5 records.

Trying To Sort With Variables (Won't work)

Nothing difficult about that. Now lets try to sort the returned records by userId using variables.

DECLARE @SortCol VARCHAR(100)
DECLARE @SortDir VARCHAR(4)
DECLARE @top INTEGER

SET @SortCol = 'userId'
SET @SortDir = 'ASC'
SET @top = 5

SELECT
      TOP(@top)
      userId
      ,username
FROM
      users
ORDER BY
      @SortCol @SortDir

Wouldn't it be nice if it was this simple. Well sorry but because of how SQL Server works this is not possible. Instead we get a nice error:

Incorrect syntax near '@SortDir'.

Solution Use A Case Statement

As with the dynamic paging problem, the solution is to use a series of CASE statements like this.

DECLARE @SortCol VARCHAR(100)
DECLARE @SortDir VARCHAR(4)
DECLARE @top INTEGER

SET @SortCol = 'userId'
SET @SortDir = 'ASC'
SET @top = 5

SELECT
      TOP(@top)
      userId
      ,username
FROM
      users
      
ORDER BY
      CASE WHEN @SortCol = 'userId' AND @SortDir = 'ASC'
            THEN userId END ASC,
      CASE WHEN @SortCol = 'userId' AND @SortDir = 'DESC'
            THEN userId END DESC,
      CASE WHEN @SortCol = 'username' AND @SortDir = 'ASC'
            THEN username END ASC,
      CASE WHEN @SortCol = 'username' AND @SortDir = 'DESC'
            THEN username END DESC

Give this a run and you should now be able to sort in either direction by userId or username.

Yes it's very wordy, and gets more so with each column you want to be able to sort on. However to date it is the only solution I have found to work other than dynamically creating sql inside the stored procedure. And we all know that it is a very bad idea to create dynamic sql don't we. So there you go.

Related Blog Postings

What Do You Think?

Reader Comments

Dan's Gravatar
Dan
Thursday, April 18, 2013 at 4:35:04 PM EDT

This was precisely the solution I was looking for. It's nowhere near 'wordy' as the alternative. Many thanks.

Nizam Khan's Gravatar
Nizam Khan
Friday, February 17, 2017 at 5:26:41 AM EST

Precised and exactly the solution I was looking for, great job Steven

Post a Comment

Comment Etiquette:

  • Please keep comments on-topic.
  • Please do not post unrelated questions or large chunks of code.
  • Please do not engage in flaming/abusive behaviour.
  • Comments that contain or appear to be advertisments, will not be published.
  • Comments that appear to be created for the purpose of linkbuilding to commercial sites will be removed.

We are all adults here so play nice.

*
*



Archives Blog Listing

Tag Listing

Learn CF In A Week

Treehouse

 
Fork me on GitHub