-----------------------------------------------------------------
-- Date Created: 2009-5-15 16:18:04
-- Created By: Generated by CodeSmith
-----------------------------------------------------------------
USE Northwind
GO
-- If procedure [SP_Products_GetPaged] is already exist, delete it.
IF EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'dbo.SP_Products_GetPaged') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE [SP_Products_GetPaged]
GO
-- Create the new procedure [SP_Products_GetPaged] by Libing, 2009-5-15 16:18:05
CREATE PROCEDURE [SP_Products_GetPaged]
@WhereClause VARCHAR (2000),
@OrderBy VARCHAR (2000),
@PageIndex INT,
@PageSize INT
AS
DECLARE @PageLowerBound INT
DECLARE @PageUpperBound INT
SET @PageLowerBound = @PageSize * @PageIndex
SET @PageUpperBound = @PageLowerBound + @PageSize
CREATE TABLE #PageIndex
(
[IndexID] INT IDENTITY (1, 1) NOT NULL,
[ProductID] INT
)
DECLARE @SQL AS NVARCHAR(4000)
SET @SQL = 'INSERT INTO #PageIndex ([ProductID])'
SET @SQL = @SQL + ' SELECT'
IF @PageSize > 0
SET @SQL = @SQL + ' TOP ' + CONVERT(NVARCHAR, @PageUpperBound)
SET @SQL = @SQL + ' [ProductID]'
SET @SQL = @SQL + ' FROM [Products]'
IF LEN(@WhereClause) > 0
SET @SQL = @SQL + ' WHERE ' + @WhereClause
IF LEN(@OrderBy) > 0
SET @SQL = @SQL + ' ORDER BY ' + @OrderBy
EXEC (@SQL)
SELECT
TempTable.[ProductID],
TempTable.[ProductName],
TempTable.[SupplierID],
TempTable.[CategoryID],
TempTable.[QuantityPerUnit],
TempTable.[UnitPrice],
TempTable.[UnitsInStock],
TempTable.[UnitsOnOrder],
TempTable.[ReorderLevel],
TempTable.[Discontinued]
FROM
[Products] TempTable
INNER JOIN
#PageIndex PageIndex
ON
TempTable.[ProductID] = PageIndex.[ProductID]
WHERE
PageIndex.IndexID > @PageLowerBound
AND
PageIndex.IndexID <= @PageUpperBound
ORDER BY
PageIndex.IndexID
SET @SQL = 'SELECT COUNT(*) AS TotalRowCount'
SET @SQL = @SQL + ' FROM [Products]'
IF LEN(@WhereClause) > 0
SET @SQL = @SQL + ' WHERE ' + @WhereClause
EXEC (@SQL)
GO
--Example
EXECUTE [SP_Products_GetPaged] '','[ProductID] ASC',0,10
EXECUTE [SP_Products_GetPaged] '','[ProductID] ASC',1,10
EXECUTE [SP_Products_GetPaged] '[UnitPrice] > 20','[UnitPrice] DESC',0,10
EXECUTE [SP_Products_GetPaged] '[UnitPrice] > 20','[UnitPrice] DESC',1,10