CREATE PROCEDURE [dbo].[GetPagedCustomers]
(
@PageSize int,
@CurrentPage int,
@ItemCount int output
)
AS
Declare @UpperBand int, @LowerBand int
-- Get The Count Of The Rows That They Meet the Criteria
SET @ItemCount = (SELECT COUNT(*) FROM Customers)
-- Calculate the @LowerCount and @UpperCount
SET @LowerBand = (@CurrentPage - 1) * @PageSize
SET @UpperBand = (@CurrentPage * @PageSize) + 1
-- create a temporaty table
CREATE TABLE #AllRows(
RowID int PRIMARY KEY IDENTITY(1, 1),
CustomerID nvarchar(5),
CompanyName nvarchar(40)
)
-- INSERT ALL THE Rows that meets the Criteria
INSERT INTO #AllRows
SELECT CustomerID, CompanyName
FROM Customers
-- AND finally select and return desired -Paged- Rows
SELECT RowID, CustomerID,CompanyName
FROM #AllRows
WHERE RowID > @LowerBand AND RowID < @UpperBand
RETURN
GO
评论1