SQL Server 2005 filtering and paging with ROW_NUMBER()

Question

I have a table called with thousands of records and would like to implement paging logic. After doing some research, I came across the ROW_NUMBER() function introduced in SQL Server 2005. My problem is, it seems to not meet my exact need and I'm wondering how to tweak my stored procedure to make it work as expected:

ALTER PROCEDURE dbo.irweb_Posts_CollectCategoryIdDatesRange
    (
    @CategoryId int,
    @StartDate datetime,
    @EndDate datetime,
    @IsDeleted bit,
    @PageIndex int,
    @PageSize int,
    @Offset int
    )
AS
    DECLARE @TotalRecords   int

    SELECT @TotalRecords = (
     SELECT COUNT(irweb_Posts.PostId) 
     FROM irweb_Posts 
     WHERE (IsDeleted = @IsDeleted)
     AND (CategoryId = @CategoryId)
 AND (DateCreated >= @StartDate) 
 AND (DateCreated <= @EndDate)   
 )

    SELECT * 
    FROM (
     SELECT  ROW_NUMBER() OVER (ORDER BY DateCreated DESC) AS RowId, irweb_Posts.*
     FROM      irweb_Posts
     ) AS p
    WHERE   ((IsDeleted = @IsDeleted)
     AND (CategoryId = @CategoryId)
     AND (DateCreated >= @StartDate) 
     AND (DateCreated <= @EndDate)  
     AND ((RowId > @Offset) AND (RowId <= (@Offset + @PageSize)))) 

    RETURN @TotalRecords

If I execute this stored procedure, I get the following results

Running [dbo].[irweb_Posts_CollectCategoryIdDatesRange] ( 
@CategoryId = 7, 
@StartDate = 5/1/2009 12:00:00 AM, 
@EndDate = 5/31/2009 11:59:59 PM, 
@IsDeleted = False, 
@PageIndex = 0, 
@PageSize = 20, 
@Offset = 0 ).

RowId                 PostId      CategoryId  ParentId    
--------------------- ----------- ----------- ----------- 
No rows affected.
(0 row(s) returned)
@RETURN_VALUE = 609
Finished running [dbo].[irweb_Posts_CollectCategoryIdDatesRange].


Running [dbo].[irweb_Posts_CollectCategoryIdDatesRange] ( 
@CategoryId = 7, 
@StartDate = 5/1/2009 12:00:00 AM, 
@EndDate = 5/31/2009 11:59:59 PM, 
@IsDeleted = False, 
@PageIndex = 0, 
@PageSize = 210, 
@Offset = 0 ).

RowId                 PostId      CategoryId  ParentId    
--------------------- ----------- ----------- ----------- 
205                   1173        7           0           
206                   1169        7           0           
207                   1168        7           0           
208                   1167        7           0           
209                   1165        7           0           
210                   1164        7           0           
No rows affected.
(6 row(s) returned)
@RETURN_VALUE = 609
Finished running [dbo].[irweb_Posts_CollectCategoryIdDatesRange].

It seems the row number field is not starting at 1 like it is supposed to. I suspect it starts at 1 for the whole table not the filtered result set. That would not be a problem if I do not require paging of filtered records. How can I make this work?

Answer 1

Move the where clause inside and leave the row number check outside

SELECT *     
FROM (        
     SELECT  ROW_NUMBER() OVER (ORDER BY DateCreated DESC) AS RowId, irweb_Posts.*        
     FROM      irweb_Posts        
     WHERE   ( (IsDeleted = @IsDeleted)        
      AND (CategoryId = @CategoryId)        
      AND (DateCreated >= @StartDate)         
      AND (DateCreated <= @EndDate))
    ) as p    
WHERE ((RowId > @Offset) AND (RowId <= (@Offset + @PageSize)))