Pagination in stored procedure

• Chris Smith-266894

  SSC Enthusiast

  Points: 161

  More actions

  September 23, 2007 at 11:12 am

  #181116

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

• AnzioBake

  SSCarpal Tunnel

  Points: 4135

  More actions

  November 6, 2007 at 4:49 am

  #748588

  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