Archive for May, 2007
Implementing Paging and Dynamic Sorting on SQL 2005 databases
by mahpour on May.08, 2007, under Uncategorized
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
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
How to sort the SQL results dynamically (dynamic ORDER BY)
by mahpour on May.08, 2007, under Uncategorized
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
