SqlServer分页存储过程

使用Northwind库Customer表

不带条件的分页

CREATE  PROCEDURE up_pagecutSample2
    @PageIndex INT, /*@PageIndex从计数,0为第一页*/
    @PageSize  INT, /*页面大小*/
    @RecordCount INT OUT, /*总记录数*/
    @PageCount INT OUT /*页数*/
AS
/*获取记录数*/
SELECT @RecordCount = COUNT(0) FROM dbo.customers
/*计算页面数据*/
SET @PageCount = CEILING(@RecordCount * 1.0 / @PageSize)
/*TOP记录数*/
DECLARE @TOPCOUNT INT
SET @TOPCOUNT = @RecordCount - @PageSize * @PageIndex
DECLARE @SQLSTR NVARCHAR(1000)
IF @PageIndex = 0 OR @PageCount <= 1
BEGIN
    SET @SQLSTR =N'SELECT TOP '+STR(@PageSize)+
    ' * FROM dbo.customers ORDER BY CustomerID DESC'
END
ELSE
BEGIN
    IF @PageIndex = @PageCount - 1
    BEGIN
       SET @SQLSTR =N'SELECT * FROM ( SELECT TOP ' + STR(@TOPCOUNT) +
       ' * FROM dbo.customers ORDER BY CustomerID ASC) T ORDER BY CustomerID DESC'
    END
    ELSE
    BEGIN
       SET @SQLSTR =N' SELECT TOP '+STR(@PageSize)+'* FROM (SELECT TOP ' + STR(@TOPCOUNT) +
       ' * FROM dbo.customers ORDER BY CustomerID ASC) T ORDER BY CustomerID DESC'
    END
END
/*执行*/
EXEC (@SQLSTR)

GO

带参数的分页

CREATE  PROCEDURE up_pagecutSample3
    @PageIndex INT, /*@PageIndex从计数,0为第一页*/
    @PageSize  INT, /*页面大小*/
    @RecordCount INT OUT, /*总记录数*/
    @PageCount INT OUT, /*页数*/
    @CustomerID NVARCHAR(5) /*查询条件*/
AS
/*获取记录数*/
SELECT @RecordCount = COUNT(0) FROM dbo.customers
/*计算页面数据*/
SET @PageCount = CEILING(@RecordCount * 1.0 / @PageSize)
/*TOP记录数*/
DECLARE @TOPCOUNT INT
SET @TOPCOUNT = @RecordCount - @PageSize * @PageIndex
DECLARE @SQLSTR NVARCHAR(1000)
IF @PageIndex = 0 OR @PageCount <= 1
BEGIN
    SET @SQLSTR =N'SELECT TOP '+STR(@PageSize)+
    ' * FROM dbo.customers WHERE CustomerID LIKE @CustomerID  ORDER BY CustomerID DESC'
END
ELSE
BEGIN
    IF @PageIndex = @PageCount - 1
    BEGIN
       SET @SQLSTR =N'SELECT * FROM ( SELECT TOP ' + STR(@TOPCOUNT) +
       ' * FROM dbo.customers CustomerID LIKE @CustomerID ORDER BY CustomerID ASC) T ORDER BY CustomerID DESC'
    END
    ELSE
    BEGIN
       SET @SQLSTR =N' SELECT TOP '+STR(@PageSize)+'* FROM (SELECT TOP ' + STR(@TOPCOUNT) +
       ' * FROM dbo.customers CustomerID LIKE @CustomerID ORDER BY CustomerID ASC) T ORDER BY CustomerID DESC'
    END
END
SELECT @CustomerID = N'%' + @CustomerID + N'%'
/*执行*/
EXEC sp_executesql @SQLSTR,N'@CustomerID NVARCHAR(5)',@CustomerID

GO

  使用临时表实现分页
create   PROC up_pagecutSample
    @pagesize INT,
    @page INT
AS
    DECLARE @ks INT
    DECLARE @str VARCHAR(200)
    SET @ks=@pagesize*(@page-1)
    IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[temp_table91]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
    BEGIN
        SELECT * INTO temp_table91 FROM dbo.customers ORDER BY CustomerID DESC
        SET rowcount @pagesize
        SET @str='select * from temp_table91 where CustomerID not in (select top '+str(@ks)+' CustomerID from temp_table91)'
        EXECUTE(@str)
        DROP TABLE temp_table91
    END
GO

通用分页
CREATE       PROCEDURE up_pagecutSample4
    @PageIndex INT, /*@PageIndex从计数,0为第一页*/
    @PageSize  INT, /*页面大小*/
    @TabName NVARCHAR(50),/*表名称*/
    @Condition NVARCHAR(500)='1=1',/*条件*/
    @OrderBy NVARCHAR(50),/*排序字段*/
    @RecordCount INT OUT, /*总记录数*/
    @PageCount INT OUT /*页数*/
AS
    /*获取记录数*/
    --SELECT @RecordCount = COUNT(0) FROM dbo.customers
    DECLARE @SQLSTR1 AS NVARCHAR(1000)
    SET @SQLSTR1=N'SELECT @RecordCount = COUNT(0) FROM ' +@TabName + ' WHERE ' + @Condition
    EXEC SP_EXECUTESQL @SQLSTR1,N'@RecordCount AS INT OUTPUT',@RecordCount OUTPUT
    /*计算页面数据*/
    SET @PageCount = CEILING(@RecordCount * 1.0 / @PageSize)
    /*TOP记录数*/
    DECLARE @TOPCOUNT INT
    SET @TOPCOUNT = @RecordCount - @PageSize * @PageIndex
    DECLARE @SQLSTR NVARCHAR(1000)
    IF @PageIndex = 0 OR @PageCount <= 1
    BEGIN
        SET @SQLSTR =N'SELECT TOP '+STR(@PageSize)+
            ' * FROM ' + @TabName + ' WHERE ' + @Condition + ' ORDER BY ' + @OrderBy + ' DESC'
    END
    ELSE
    BEGIN
        IF @PageIndex = @PageCount - 1
        BEGIN
            SET @SQLSTR =N'SELECT * FROM ( SELECT TOP ' + STR(@TOPCOUNT) +
                     ' * FROM ' + @TabName + ' WHERE ' + @Condition + ' ORDER BY ' + @OrderBy + ' ASC) T ORDER BY ' + @OrderBy + ' DESC'
        END
        ELSE
        BEGIN
            SET @SQLSTR =N' SELECT TOP '+STR(@PageSize)+'* FROM (SELECT TOP ' + STR(@TOPCOUNT) +
                   ' * FROM ' + @TabName + ' WHERE ' + @Condition + ' ORDER BY ' + @OrderBy + ' ASC) T ORDER BY ' + @OrderBy + ' DESC'
        END
    END
    /*执行*/
    EXEC (@SQLSTR)
GO