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.

Order And Number The Records

Now that we have all the records we need to number them by the sort criteria. To do this we wrap the SQL that returns the records as a source for a second SELECT. We use the ROW_NUMBER() OVER() method to then apply the row numbering as follows.

-- Apply row numbering (this is also where sorting takes place)
SELECT
      x1.*
      ,ROW_NUMBER() OVER(ORDER BY userID ASC) AS rowNumber
FROM (      
      -- Get all records that match the filter criteria
      SELECT
            userid
            ,username
      FROM dbo.users
      WHERE
      --put filtering conditions (excluding row number limits) here
      (@nameLike IS NULL OR username LIKE '%'+ @nameLike + '%')
) AS x1

Dynamic Sorting

You will note at this stage that the "Order By" condition is hard coded to "userid desc". In the real world though we may want to order our results by a different datapoint such as username. To do this it would be great if we could do something like this.

ORDER BY @orderCol @orderDir

Unfortunately this is not possible so we need to get creative. To date the only way I have found to implement dynamic sorting in a stored procedure is to use a rather ugly and verbose hack using a case statement.

ORDER BY
-- START UGLY HACK --
--For each column we would like sortable create two case statements (one for ASC and one for DESC)
CASE WHEN lower(@orderCol) = 'userid' AND lower(@orderDir) = 'asc' THEN userId END ASC
,CASE WHEN lower(@orderCol) = 'userid' AND lower(@orderDir) = 'desc' THEN userId END DESC
,CASE WHEN lower(@orderCol) = 'username' AND lower(@orderDir) = 'asc' THEN username END ASC
,CASE WHEN lower(@orderCol) = 'username' AND lower(@orderDir) = 'desc' THEN username END DESC
-- END UGLY HACK --

Combining this with the previous code gives us this.

-- Apply row numbering (this is also where sorting takes place)
SELECT
      x1.*
      ,ROW_NUMBER() OVER(
            ORDER BY
            -- START UGLY HACK --
            --For each column we would like sortable create two case statements (one for ASC and one for DESC)
            CASE WHEN lower(@orderCol) = 'userid' AND lower(@orderDir) = 'asc' THEN userId END ASC
            ,CASE WHEN lower(@orderCol) = 'userid' AND lower(@orderDir) = 'desc' THEN userId END DESC
            ,CASE WHEN lower(@orderCol) = 'username' AND lower(@orderDir) = 'asc' THEN username END ASC
            ,CASE WHEN lower(@orderCol) = 'username' AND lower(@orderDir) = 'desc' THEN username END DESC
            -- END UGLY HACK --
      ) AS rowNumber
FROM (      
      -- Get all records that match the filter criteria
      SELECT
            userid
            ,username
      FROM dbo.users
      WHERE
      --put filtering conditions (excluding row number limits) here
      (@nameLike IS NULL OR username LIKE '%'+ @nameLike + '%')
) AS x1
1 2 3

Related Blog Postings

What Do You Think?

Reader Comments

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