SQL 分页存储过程

-----------------------------------------------------------------
-- 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