set
ANSI_NULLS
ON
set QUOTED_IDENTIFIER ON
go
ALTER PROCEDURE [ dbo ] . [ DividePage ]
@tabname nvarchar ( 2000 ), -- 要查询表名
@fidlelist nvarchar ( 1000 ), -- 要查询字段
@wheresql nvarchar ( 1000 ), -- 查询条件
@pk nvarchar ( 20 ), -- 主键
@order nvarchar ( 100 ), -- 排序字段
@pagenum int , -- 要查询的页号
@rowsum int , -- 每页要显示的行数
@allpage int output, -- 计算总页数
@allrow int output -- 计算总行数
AS
declare @sqlstr nvarchar ( 4000 ), @tmpwhere nvarchar ( 4000 ), @tmporder nvarchar ( 100 )
BEGIN
if ( @order != '' )
begin
set @tmporder = @order
end
else
set @tmporder = @pk ;
set @tmpwhere = '' ;
if ( @wheresql != '' )
begin
set @tmpwhere = ' where ' + @wheresql ;
end
set @sqlstr = N ' select * from(select ' + @fidlelist + ' , ROW_NUMBER() OVER(order by ' + @tmporder + ' ) as row from ' + @tabname + @tmpwhere + ' ) tmp where row between ' + cast ((( @pagenum - 1 ) * @rowsum + 1 ) as nvarchar ) + ' and ' + cast ( @pagenum * @rowsum as nvarchar );
exec sp_executesql @sqlstr
set @sqlstr = N ' select @allrow1 =count( ' + @pk + ' ) from ' + @tabname + @tmpwhere
exec sp_executesql @sqlstr ,N ' @allrow1 int OUTPUT ' , @allrow1 = @allrow output
if ( @allrow % @rowsum <> 0 )
begin
set @allpage = @allrow / @rowsum + 1 -- 获得总页数
end
else
set @allpage = @allrow / @rowsum -- --获得总页数
END
set QUOTED_IDENTIFIER ON
go
ALTER PROCEDURE [ dbo ] . [ DividePage ]
@tabname nvarchar ( 2000 ), -- 要查询表名
@fidlelist nvarchar ( 1000 ), -- 要查询字段
@wheresql nvarchar ( 1000 ), -- 查询条件
@pk nvarchar ( 20 ), -- 主键
@order nvarchar ( 100 ), -- 排序字段
@pagenum int , -- 要查询的页号
@rowsum int , -- 每页要显示的行数
@allpage int output, -- 计算总页数
@allrow int output -- 计算总行数
AS
declare @sqlstr nvarchar ( 4000 ), @tmpwhere nvarchar ( 4000 ), @tmporder nvarchar ( 100 )
BEGIN
if ( @order != '' )
begin
set @tmporder = @order
end
else
set @tmporder = @pk ;
set @tmpwhere = '' ;
if ( @wheresql != '' )
begin
set @tmpwhere = ' where ' + @wheresql ;
end
set @sqlstr = N ' select * from(select ' + @fidlelist + ' , ROW_NUMBER() OVER(order by ' + @tmporder + ' ) as row from ' + @tabname + @tmpwhere + ' ) tmp where row between ' + cast ((( @pagenum - 1 ) * @rowsum + 1 ) as nvarchar ) + ' and ' + cast ( @pagenum * @rowsum as nvarchar );
exec sp_executesql @sqlstr
set @sqlstr = N ' select @allrow1 =count( ' + @pk + ' ) from ' + @tabname + @tmpwhere
exec sp_executesql @sqlstr ,N ' @allrow1 int OUTPUT ' , @allrow1 = @allrow output
if ( @allrow % @rowsum <> 0 )
begin
set @allpage = @allrow / @rowsum + 1 -- 获得总页数
end
else
set @allpage = @allrow / @rowsum -- --获得总页数
END
关键的函数ROW_NUMBER() 是sqlserver 2000 中没有的。