Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Pagination in stored procedure Expand / Collapse
Author
Message
Posted Sunday, September 23, 2007 11:12 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

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
Posted Tuesday, November 6, 2007 4:49 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, July 23, 2014 5:35 AM
Points: 211, Visits: 546
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 »

Add to briefcase

Permissions Expand / Collapse