分页用的存储过程 (for SQL 2000/2005)

  由于之前做网站的时候老是要做分页，所以找了一些分页的存储过程来使用。这里就将自己使用的分页存储过程在这里发布，用作保留之用……

在SQL 2000数据库下使用的：

-- =============================================
-- DocumentName       分页存储过程
-- Author             ajayumi
-- 创建日期           2007-01-15
-- 描述               利用SQL查询语句进行分页
-- 输入
--       {
--        @SQL            :    SQL查询语句,示例：'Select * from [TableName]'
--        @Order        :    排序,示例：[ColumnName] [ASC | DESC]
--        @CurPage        :    当前页,示例：0..9
--        @PageRows        :    每页显示的行数,示例：0..9
--        @TotalRecorder:    查询记录总数(输出参数)
--          @IsXML        :    表示返回的结果，0表示以表格形式记录，1表示以XML格式返回记录；默认为0
--       }
-- =============================================

ALTER PROCEDURE [dbo].[SeparatePage]
    -- Add the parameters for the stored procedure here
    @SQL Nvarchar(2000),
    @Order Nvarchar(20),
    @CurPage int,
    @PageRows int,
    @TotalRecorder int output,
    @IsXML bit = 0
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    SET NOCOUNT ON;
    declare @ExceSQL nvarchar(4000)
    
    --设置开始行号
    declare  @start_row_num AS int
    SET @start_row_num = (@CurPage - 1) * @PageRows
   
    if @CurPage > 1 
        BEGIN
            SET @start_row_num = @start_row_num + 1;
            SET @PageRows = @PageRows - 1
        END
    else
        SET @start_row_num = @start_row_num
    
    --获取总记录数
    set @ExceSQL = 'SELECT tb.* into [tb_Temp] FROM ('+ @SQL +') tb ORDER BY'+ @order
    execute(@ExceSQL)
    SELECT @TotalRecorder = COUNT(*) from [tb_Temp]

    ALTER TABLE [tb_Temp] add [RowNumber] int

    --设置查询语句
    SET @ExceSQL = 'SELECT TOP '+ convert(varchar(10),@PageRows) +' * 
                    FROM (SELECT TOP '+ convert(varchar(10),@PageRows) +' * 
                    FROM (SELECT TOP '+ convert(varchar(10),@TotalRecorder-@start_row_num+1) +' *
                    FROM [tb_Temp] ORDER BY '+ REPLACE(@Order,'DESC','ASC') +') AS a
                    ORDER BY '+ REPLACE(@Order,'ASC','DESC') +') AS b'

    IF(@IsXML = 1)
        SET @ExceSQL = @ExceSQL + ' FOR XML AUTO,ELEMENTS'
 
    EXECUTE(@ExceSQL)
    DROP Table [tb_Temp]
END

 

在SQL 2005数据库下使用的：

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go


-- =============================================
-- DocumentName       分页存储过程
-- Author             ajayumi
-- 创建日期             2007-01-15
-- 描述                 利用SQL查询语句进行分页
-- 输入
--       {
--        @SQL            :    SQL查询语句,示例：'Select * from [TableName]'
--        @Order        :    排序,示例：[ColumnName] [ASC | DESC]
--        @CurPage        :    当前页,示例：0..9
--        @PageRows        :    每页显示的行数,示例：0..9
--        @TotalRecorder:    查询记录总数(输出参数)
--          @IsXML        :    表示返回的结果，0表示以表格形式记录，1表示以XML格式返回记录；默认为0
--       }
-- =============================================

ALTER PROCEDURE [dbo].[SeparatePage]
    -- Add the parameters for the stored procedure here
    @SQL Nvarchar(2000),
    @Order Nvarchar(20),
    @CurPage int,
    @PageRows int,
    @TotalRecorder int output,
    @IsXML bit = 0
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    SET NOCOUNT ON;
    declare @ExceSQL nvarchar(4000)
    
    --设置开始行号
    declare  @start_row_num AS int
    SET @start_row_num = (@CurPage - 1) * @PageRows
   
    if @CurPage > 1 
        BEGIN
            SET @start_row_num = @start_row_num + 1;
            SET @PageRows = @PageRows - 1
        END
    else
        SET @start_row_num = @start_row_num
    
    --设置标签语句
    declare @RowNumber nvarchar(100)
    set @RowNumber = ', ROW_NUMBER() OVER(ORDER BY ' + @Order + ') as RowNumber from '

    set @SQL = Replace(@SQL,' from ',@RowNumber)

    --获取总记录数
    set @ExceSQL = 'WITH [TempTable] AS (' + @SQL + ') 
        select @TotalRecorder=max(RowNumber) from [TempTable]'

    execute sp_executesql @ExceSQL,N'@TotalRecorder int output',@TotalRecorder output

    --设置查询语句
    set @ExceSQL = 'WITH [TempTable] AS (' + @SQL + ') 
        select * from [TempTable] where RowNumber between ' + Convert(nvarchar,@start_row_num)
        + ' And ' + Convert(nvarchar,@start_row_num+@PageRows)
    
    IF(@IsXML = 1)SET @ExceSQL = @ExceSQL + 'FOR XML AUTO,ELEMENTS'
 
    execute(@ExceSQL)

END