September 23, 2007 at 11:12 am
Comments posted to this topic are about the item Pagination in stored procedure
November 6, 2007 at 4:49 am
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 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply