Pagination in stored procedure

  • Chris Smith-266894

    SSC Enthusiast

    Points: 161

    Comments posted to this topic are about the item Pagination in stored procedure

  • AnzioBake

    Hall of Fame

    Points: 3954

    Unless I am misunderstanding this procedure is incorrect.

    My understanding is that the

    @PageIndex indicates which page number is required

    and @PageSize is the number of records per Page

    Exec SP @PageIndex = 1, @PageSize = 10 results in rows 1-10

    Exec SP @PageIndex = 2, @PageSize = 10 results in rows 11-20

    Your Procedure is returning the following

    Exec SP @PageIndex = 1, @PageSize = 10 results in rows 1-10

    Exec SP @PageIndex = 2, @PageSize = 10 results in rows 2-11

    I believe you want something like

    CREATE PROCEDURE [dbo].[uspOrderReport] (@PageIndex INT, @PageSize INT) AS

    DECLARE @Rows INT

    DECLARE @tempIDs TABLE

    (

    [identity] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED,

    [OrderID] [int] NULL

    ) --TO STORE THE SCORED TABLE

    SELECT @Rows = ( @PageIndex * @PageSize )

    SET ROWCOUNT @Rows

    INSERT INTO

    @tempIDs (OrderID)

    SELECT

    OrderId

    FROM

    orders

    ORDER BY OrderID

    SELECT

    ORD.OrderId,

    ORD.CustomerId,

    ORD.EmployeeId

    ,[tid].[Identity]

    FROM

    orders ORD LEFT OUTER JOIN @tempIDs TID

    ON ORD.OrderID = TID.OrderID

    WHERE

    tid.[Identity] > ( @PageIndex - 1 ) * @PageSize

    ORDER BY ORD.OrderID

    RETURN

    go

    exec [dbo].[uspOrderReport] 1, 20

    exec [dbo].[uspOrderReport] 1, 10

    exec [dbo].[uspOrderReport] 2, 10

Viewing 2 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply