Pagination in stored procedure

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by Chris Smith » Pagination in stored procedure

Pagination in stored procedure

Rate Topic

Display Mode

Topic Options

Author

Message

Chris Smith-266894

Posted Sunday, September 23, 2007 11:12 AM

Grasshopper

Group: General Forum Members
Last Login: Wednesday, October 22, 2008 6:43 AM
Points: 13, Visits: 7

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

Post #401664

AnzioBake

Posted Tuesday, November 06, 2007 4:49 AM

SSC Veteran

Group: General Forum Members
Last Login: Friday, May 17, 2013 2:43 AM
Points: 210, Visits: 437

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

Post #418967

« Prev Topic | Next Topic »

Permissions