SQL Server 2005通用分页存储过程

/*-------Microsoft SQL Server 2005 Pagination-------*/
CREATE PROCEDURE MsSql2K5Pagination
(
    @Columns NVARCHAR(500),         --数据库中相关表表列
    @TbNames  NVARCHAR(200),        --数据库中相关表表名
    @WhereCondition NVARCHAR(1500), --Where条件语句,不含有Where关键字
    @OrderColumns NVARCHAR(350),    --排序列名称,支持多列排序,例如ORDER BY column1,column2但是语句中不能还有ORDER BY关键字
    @IsOrderByASC BIT,              --排序方式1:ASC,0:DESC   
    @CurrentPageIndex INT,          --当前分页页面数,如果程序是第一次使用则该值为1
    @PageSize INT,                  --程序需求每页显示的数据条数
    @TotalPages INT OUTPUT,         --数据库中总的页面数量
    @TotalRecords INT OUTPUT        --数据库中总的记录数量
)
AS
SET NOCOUNT ON
--------设置WHERE条件--------
BEGIN
DECLARE @WHERE NVARCHAR(2000)
DECLARE @OTHERWHERE NVARCHAR(2000)
IF ISNULL(@WhereCondition,'') = ''
    SET @WHERE = ''
ELSE
    SET @WHERE = ' WHERE ' + @WhereCondition   
END
--------设置ORDER BY条件--------
BEGIN
DECLARE @ORDERBY NVARCHAR(800)
IF ISNULL(@OrderColumns,'') = ''
   SET @ORDERBY = ''
ELSE
   BEGIN
     IF @IsOrderByASC = 1
        SET @ORDERBY = ' ORDER BY ' + @OrderColumns + ' ASC '
     ELSE
        SET @ORDERBY = ' ORDER BY ' + @OrderColumns + ' DESC '
   END
END
--------计算分页页面数量和总数据条数--------
BEGIN
DECLARE @_PageNum NVARCHAR(1000)
SET @_PageNum = 'SELECT @TotalRecords=COUNT(*), @TotalPages=CEILING((COUNT(*)+0.0/'
                + CAST(@PageSize AS VARCHAR) + ') FROM ' + @TbNames + @WHERE
EXEC SP_EXECUTESQL @_PageNum,N'@TotalRecords INT OUTPUT,@TotalPages INT OUTPUT',
                   @TotalRecords OUTPUT,@TotalPages OUTPUT
END
--------现在开始实行分页--------
BEGIN
DECLARE @_PAGESQL NVARCHAR(4000)
IF @CurrentPageIndex = 1
   SET @_PAGESQL = 'SELECT TOP ' + STR(@PageSize) + ' ' + @Columns
                   + ' FROM ' + @TbNames + @WHERE + @ORDERBY
ELSE
  BEGIN
    DECLARE @NEWROWS NVARCHAR(1500)
    SET @NEWROWS = ' ROW_NUMBER() OVER(ORDER BY ' + @ORDERBY + ') AS ROWNUM FROM '
                 + @TbNames + @WHERE + ' AS tab '
    SET @_PAGESQL = 'SELECT TOP ' + STR(@PageSize) + @Columns + ' FROM (SELECT ' + @Columns
                  + ',' + @NEWROWS + ' WHERE tab.ROWNUM >' + STR(@PageSize*(@CurrentPageIndex-1))
  END
  EXEC(@_PAGESQL)
END 
GO

 

如果您要需要查看SQL Server 2000通用数据分页存储过程请点击： SQL Server 2000通用分页存储过程