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