SQL Server 2005 Filterung und Blätterung mit ROW_NUMBER()

Question

Ich habe eine Tabelle mit Tausenden von Datensätzen und möchte eine Paginierungslogik implementieren. Nach einiger Recherche bin ich auf die ROW_NUMBER() Funktion gestoßen, die in SQL Server 2005 eingeführt wurde. Mein Problem ist, dass sie anscheinend nicht meinen genauen Anforderungen entspricht und ich frage mich, wie ich meine gespeicherte Prozedur anpassen kann, um sie wie erwartet zu nutzen:

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

Wenn ich diese gespeicherte Prozedur ausführe, erhalte ich die folgenden Ergebnisse

Führe [dbo].[irweb_Posts_CollectCategoryIdDatesRange] aus ( 
@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    
--------------------- ----------- ----------- ----------- 
Keine Zeilen betroffen.
(0 Zeile(n) zurückgegeben)
@RETURN_VALUE = 609
Ausführung von [dbo].[irweb_Posts_CollectCategoryIdDatesRange] abgeschlossen.

Führe [dbo].[irweb_Posts_CollectCategoryIdDatesRange] aus ( 
@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           
Keine Zeilen betroffen.
(6 Zeile(n) zurückgegeben)
@RETURN_VALUE = 609
Ausführung von [dbo].[irweb_Posts_CollectCategoryIdDatesRange] abgeschlossen.

Es scheint, dass das Feld für die Zeilennummer nicht bei 1 beginnt, wie es sollte. Ich vermute, dass es bei der gesamten Tabelle bei 1 beginnt und nicht bei der gefilterten Ergebnismenge. Das wäre kein Problem, wenn ich keine Paginierung der gefilterten Datensätze erfordern würde. Wie kann ich das zum Laufen bringen?

Answer 1

Verschieben Sie die WHERE-Klausel nach innen und lassen Sie die Zeilennummernprüfung außen

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)))