alter
PROCEDURE
pager
@tblname varchar ( 100 ), -- 表名
@pkname varchar ( 100 ), -- 主键名称
@pgsize int , -- 每页数据大小
@pg int , -- 当前页码
@Col_list varchar ( Max ) = null , -- 不为空,以英文逗号(,)开始
@Filter varchar ( Max ) = null , -- 不为空,以where开始 ……
@sortColumn varchar ( 500 ) = null , -- 不为空,以Order by开始
@totalcount int output
as
declare @toid int , @tid int ,
@strsql varchar ( 200 ), @sql nvarchar ( 200 )
set @sql = ' select @Rcount=count( ' + @pkname + ' ) from ' + @tblname
exec sp_executesql @sql ,N ' @Rcount int output ' , @totalcount output
set @toid = ( @pg - 1 ) * @pgsize + 1
set @tid = @pg * @pgsize
-- 以下只是都不为空的时候,其余7中自行书写代码
if @Filter != null and @sortColumn != null and @Col_list != null
begin
set @strsql = ' select * from(select row_number() Over(Order By ' + @pkname + ' Desc) as rownum ' + @Col_list + ' from ' + @tblname + ' ' + @Filter + ' ' + @sortColumn + ' ) as D where rownum between ' + Cast ( @toid as varchar ) + ' and ' + cast ( @tid as varchar )
end
exec ( @strsql )
@tblname varchar ( 100 ), -- 表名
@pkname varchar ( 100 ), -- 主键名称
@pgsize int , -- 每页数据大小
@pg int , -- 当前页码
@Col_list varchar ( Max ) = null , -- 不为空,以英文逗号(,)开始
@Filter varchar ( Max ) = null , -- 不为空,以where开始 ……
@sortColumn varchar ( 500 ) = null , -- 不为空,以Order by开始
@totalcount int output
as
declare @toid int , @tid int ,
@strsql varchar ( 200 ), @sql nvarchar ( 200 )
set @sql = ' select @Rcount=count( ' + @pkname + ' ) from ' + @tblname
exec sp_executesql @sql ,N ' @Rcount int output ' , @totalcount output
set @toid = ( @pg - 1 ) * @pgsize + 1
set @tid = @pg * @pgsize
-- 以下只是都不为空的时候,其余7中自行书写代码
if @Filter != null and @sortColumn != null and @Col_list != null
begin
set @strsql = ' select * from(select row_number() Over(Order By ' + @pkname + ' Desc) as rownum ' + @Col_list + ' from ' + @tblname + ' ' + @Filter + ' ' + @sortColumn + ' ) as D where rownum between ' + Cast ( @toid as varchar ) + ' and ' + cast ( @tid as varchar )
end
exec ( @strsql )