【简答题】 请阅读下面分页存储过程的代码，并且为每一行代码加上注释。 create procedure pagequery ( @sqlTable nvarchar(200), ----待查询表名 @sqlColumns nvarchar(500) , ----待显示字段 @sqlWhere nvarchar(1000) , ----查询条件,不需where @sqlSort nvarchar(500) , ----排序字段，不需order by @pageIndex int, ----当前页 @pageSize int, ----每页显示的记录数 @rowTotal int = 1 output ----返回总记录数 ) as begin set nocount on; -- 获取记录总数 declare @sqlcount nvarchar(1000) ; set @sqlcount = N' select @rowTotal=count(*) from '+@sqlTable +' where 1=1 '+ @sqlWhere; exec sp_executesql @sqlcount,N'@rowTotal int out ',@rowTotal out ; -- 返回数据查询 declare @sqldata nvarchar(1000) ; set @sqldata=' select '+ @sqlColumns + ' from (select *,Row_number() over(order by '+ @sqlSort +') as RN from '+ @sqlTable +' where 1=1 '+ @sqlWhere+') as TR where RN>'+ cast(@pageSize*@pageIndex as varchar(20))+' and RN<'+ cast((@pageSize*(@pageIndex+1)+1) as varchar(20)); exec sp_executesql @sqldata ; end --调用如下： declare @totalrow int; exec pagequery 'employee','*','','ID asc',1,10,@totalrow output; select @totalrow; 2）MySQL 版本 create procedure pagequery ( in sqlTable varchar(256), in sqlColumns varchar(512), in sqlWhere varchar(1024), in sqlSort varchar(512), in pageIndex int, in pageSize int, out rowTotal int ) begin declare start_row int default 0; set start_row = pageIndex* pageSize; -- 获取记录总数 set @sqlcount = concat('select count(*) into @rows_total from ', sqlTable, ' where 1=1 ', sqlWhere); prepare count_stmt from @sqlcount; execute count_stmt; deallocate prepare count_stmt; set rowTotal = @rows_total; -- 返回数据查询 set @sqldata = concat('select ', sqlColumns, ' from ', sqlTable, ' where 1=1 ', sqlWhere, ' order by ', sqlSort,' limit ', start_row, ',', pageSize); prepare main_stmt from @sqldata; execute main_stmt; deallocate prepare main_stmt; end --调用如下： call pagequery('emp

1）SQL Server 版本

create procedure pagequery
(
    @sqlTable nvarchar(200),              -- 待查询表名
    @sqlColumns nvarchar(500) ,           -- 待显示字段
    @sqlWhere nvarchar(1000) ,            -- 查询条件,不需where
    @sqlSort nvarchar(500) ,              -- 排序字段，不需order by
    @pageIndex int,                       -- 当前页
    @pageSize int,                        -- 每页显示的记录数
    @rowTotal int = 1 output              -- 返回总记录数
)
as
begin
    set nocount on;                      -- 不返回行数

    -- 获取记录总数
    declare @sqlcount nvarchar(1000);
    set @sqlcount = N'select @rowTotal=count(*) from '+@sqlTable +' where 1=1 '+ @sqlWhere;
    exec sp_executesql @sqlcount,N'@rowTotal int out ',@rowTotal out;

    -- 返回数据查询
    declare @sqldata nvarchar(1000);
    set @sqldata='select '+ @sqlColumns + ' from (select *,Row_number() over(order by '+ @sqlSort +') as RN from '+ @sqlTable +' where 1=1 '+ @sqlWhere+') as TR where RN>'+ cast(@pageSize*@pageIndex as varchar(20))+' and RN<'+ cast((@pageSize*(@pageIndex+1)+1) as varchar(20));
    exec sp_executesql @sqldata;
end

-- 调用如下：
declare @totalrow int;
exec pagequery 'employee','*','','ID asc',1,10,@totalrow output;
select @totalrow;

2）MySQL 版本

create procedure pagequery
(
    in sqlTable varchar(256),      -- 待查询表名
    in sqlColumns varchar(512),    -- 待显示字段
    in sqlWhere varchar(1024),     -- 查询条件,不需where
    in sqlSort varchar(512),       -- 排序字段，不需order by
    in pageIndex int,              -- 当前页
    in pageSize int,               -- 每页显示的记录数
    out rowTotal int               -- 返回总记录数
)
begin
    declare start_row int default 0;
    set start_row = pageIndex * pageSize;

    -- 获取记录总数
    set @sqlcount = concat('select count(*) into @rows_total from ', sqlTable, '  where 1=1 ', sqlWhere);
    prepare count_stmt from @sqlcount;
    execute count_stmt;
    deallocate prepare count_stmt;
    set rowTotal = @rows_total;

    -- 返回数据查询
    set @sqldata = concat('select ', sqlColumns, ' from ', sqlTable, ' where 1=1 ', sqlWhere, ' order by ', sqlSort,' limit ', start_row, ',', pageSize);
    prepare main_stmt from @sqldata;
    execute main_stmt;
    deallocate prepare main_stmt;
end

-- 调用如下：
call pagequery('employee', '*', '', 'ID asc', 1, 10, @totalrow);
select @totalrow;

注释已经添加在代码中。