May 8, 2007 No comments

Implementing Paging and Dynamic Sorting on SQL 2005 databases

ROW_NUMBER() is a new function in SQL 2005 that returns the sequential number of a row within a partition of a result set, starting at 1 for the first row in each partition.

DECLARE @START int;
DECLARE @END int;
DECLARE @Sort nvarchar(100);
SET @Start=10;
SET @END=20;
SET @Sort=’LastName’;
SELECT *  FROM (SELECT ROW_NUMBER() over (
        ORDER BY CASE
            WHEN @Sort=’FirstName’ THEN FirstName
            WHEN @Sort=’LastName’ THEN LastName
            ELSE FirstName
        END) as R ,* From Students) as tbl
WHERE R BETWEEN @Start AND @End
May 8, 2007 No comments

How to sort the SQL results dynamically (dynamic ORDER BY)

The solution is using CASE statement in your select query .

Here’s a sample:
 
DECLARE @sortColumn VARCHAR(255)
SELECT @sortColumn = ‘title’

SELECT * FROM asset
ORDER BY
CASE
WHEN @sortColumn = ‘title’ THEN title
WHEN @sortColumn = ’start_date’ THEN start_date
WHEN @sortColumn = ‘asset_id’ THEN asset_id
ELSE title
END

Recent Posts

Blogroll