修改一个分页的存储过程

记得好像有两种，一种是依靠一列可以比较大小的列，用自联方式count小于等于那列的记录数作为rowid
另一种是把 identity(int,1,1)和表的其他数据插入一张临时表，然后再通过临时表进行查询

在MS-SQL2000中模拟窗口函数ROW_NUMBER() 可以使用临时表

SQL2000的写法,alter procedure dbo.PF_Sys_PageControl
    @tableName   varchar(500),          -- 表名
    @tableFields varchar(5000) = '*',  -- 字段名(全部字段为*)
    @sqlWhere    varchar(5000) = NULL, -- 条件语句(不加 where，可带 group by 分组条件，分组条件需要带 group by 关键字)
    @orderFields varchar(5000),        -- 排序字段(必须，支持多字段，不加 order by)
    @pageSize    int,                  -- 页大小(每页多少条记录)
    @pageIndex   int = 1 ,             -- 指定当前为第几页
    @totalPage   int output            -- 返回总页数
AS
BEGIN
    Declare @sql nvarchar(4000);
    Declare @totalRecord int;

    -- 计算总记录数
    if (@sqlWhere = '' or @sqlWhere = NULL)
        set @sql = 'select @totalRecord = count(0) from ' + @tableName
    else
        BEGIN
            if(CHARINDEX('group by', LOWER(@sqlWhere)) > 0)
                set @sql = 'select @totalRecord = count(0) from (select ' + @tableFields + ' from ' + @tableName + ' where ' + @sqlWhere + ') as Tab_GroupTable'
            else
                set @sql = 'select @totalRecord = count(0) from ' + @tableName + ' where ' + @sqlWhere
        END

    EXEC sp_executesql @sql,N'@totalRecord int OUTPUT',@totalRecord OUTPUT

    -- 计算总页数
    select @totalPage=CEILING((@totalRecord+0.0)/@pageSize)

    -- 处理页数超出范围情况
    if @pageIndex<=0
        Set @pageIndex = 1
    if @pageIndex>@totalPage
        Set @pageIndex = @totalPage

    select @totalPage=@totalRecord

    -- 处理开始点和结束点
    Declare @startRecord int
    Declare @endRecord int
    set @startRecord = (@pageIndex-1)*@pageSize + 1
    set @endRecord = @startRecord + @pageSize - 1

    -- 合成sql语句
    if (@sqlWhere = '' or @sqlWhere = NULL)
        set @sql = 'select ' + @tableFields + ',identity(int,1,1) rowId into #t from ' + @tableName
    else
        set @sql = 'select ' + @tableFields + ',identity(int,1,1) rowId into #t from ' + @tableName + ' where ' + @sqlWhere

    set @Sql = @Sql + '; select * from #t where rowId between ' + Convert(varchar(50),@startRecord) + ' and ' +  Convert(varchar(50),@endRecord)

    Exec(@Sql)

end

这样子的调用的时候会有一个错误，说“无法使用 SELECT INTO 语句向表 '#t' 中添加标识列，该表中已有继承了标识属性的列 'id'。”
我传入的表都是有自增ID。
========
谢谢楼上两位的热情回答！

USE [waqi]
GODECLARE @return_value int,
@totalPage intSELECT @totalPage = 1EXEC @return_value = [waqi_f].[PF_Sys_PageControl]
@tableName = N' t_news',
@tableFields = N' * ',
@sqlWhere = N' ',
@orderFields = N' id desc',
@pageSize = 2,
@pageIndex = 1,
@totalPage = @totalPage OUTPUTSELECT @totalPage as N'@totalPage'SELECT 'Return Value' = @return_valueGO

try this,alter procedure dbo.PF_Sys_PageControl
    @tableName   varchar(500),          -- 表名
    @tableFields varchar(5000) = '*',  -- 字段名(全部字段为*)
    @sqlWhere    varchar(5000) = NULL, -- 条件语句(不加 where，可带 group by 分组条件，分组条件需要带 group by 关键字)
    @orderFields varchar(5000),        -- 排序字段(必须，支持多字段，不加 order by)
    @pageSize    int,                  -- 页大小(每页多少条记录)
    @pageIndex   int = 1 ,             -- 指定当前为第几页
    @totalPage   int output            -- 返回总页数
AS
BEGIN
    Declare @sql nvarchar(4000);
    Declare @totalRecord int;

    -- 计算总记录数
    if (@sqlWhere = '' or @sqlWhere = NULL)
        set @sql = 'select @totalRecord = count(0) from ' + @tableName
    else
        BEGIN
            if(CHARINDEX('group by', LOWER(@sqlWhere)) > 0)
                set @sql = 'select @totalRecord = count(0) from (select ' + @tableFields + ' from ' + @tableName + ' where ' + @sqlWhere + ') as Tab_GroupTable'
            else
                set @sql = 'select @totalRecord = count(0) from ' + @tableName + ' where ' + @sqlWhere
        END

    EXEC sp_executesql @sql,N'@totalRecord int OUTPUT',@totalRecord OUTPUT

    -- 计算总页数
    select @totalPage=CEILING((@totalRecord+0.0)/@pageSize)

    -- 处理页数超出范围情况
    if @pageIndex<=0
        Set @pageIndex = 1
    if @pageIndex>@totalPage
        Set @pageIndex = @totalPage

    select @totalPage=@totalRecord

    -- 处理开始点和结束点
    Declare @startRecord int
    Declare @endRecord int
    set @startRecord = (@pageIndex-1)*@pageSize + 1
    set @endRecord = @startRecord + @pageSize - 1

    -- 合成sql语句
    if (@sqlWhere = '' or @sqlWhere = NULL)
        set @sql = 'select ' + @tableFields + ',identity(int,1,1) rowId into #t from (select top 100 percent * from ' + @tableName + ') t '
    else
        set @sql = 'select ' + @tableFields + ',identity(int,1,1) rowId into #t from (select top 100 percent * from ' + @tableName + ') t where ' + @sqlWhere

    set @Sql = @Sql + '; select * from #t where rowId between ' + Convert(varchar(50),@startRecord) + ' and ' +  Convert(varchar(50),@endRecord)

    Exec(@Sql)

end

调试易

修改一个分页的存储过程

解决方案 »