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.
Getting The Desired Range Of Rows
Finally once we have numbered the rows we simply select out the ones that match our desired range. To do this we wrap the previous SQL in another SELECT and filter by the startRow and endRow ranges.
-- Return the records in the specified range
SELECT
x2.*
,@maxRowNumber as maxRowNumber
FROM (
-- 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
) AS x2
WHERE
rowNumber BETWEEN @startRow AND @endRow
You will note that we also included the value of the @maxRowNumber variable to the records we return at this stage. The first time the stored procedure is called, the default row number range will be passed to get some results to display on the first page. However to work out the appropriate paging mechanism the application will need know how many subsequent records exist.
The Final Code
Putting everything together we end up with the following code for our stored procedure.
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Steven Neiland
-- Description: Returns a paged of user records
-- =============================================
ALTER PROCEDURE dbo.proc_getUsersPaged
@orderCol varchar(50),
@orderDir varchar(4),
@startRow int,
@endRow int,
@nameLike varchar(50) = null
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Declare variables
DECLARE @maxRowNum INT
-- Get the total number of records matching the filter criteria
SELECT @maxRowNum = COUNT(*)
FROM dbo.users
WHERE
--put filtering conditions (excluding row number limits) here
(@nameLike IS NULL OR @nameLike LIKE '%'+ @nameLike + '%')
-- Return the records in the specified range
SELECT
x2.*
,@maxRowNumber as maxRowNumber
FROM (
-- 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
) AS x2
WHERE
rowNumber BETWEEN @startRow AND @endRow
END
GO
Reader Comments