sql2005 存储过程分页示例代码

--分页存储过程示例
alter procedure [dbo].[jh_pagedemo]
@pagesize int = 9000000000,
@pageindex int = 1 ,
@orderby nvarchar(200) = '' -- 不加order by
as
set nocount on
--声明变量
declare @select varchar(3048);
declare @from varchar(512);
declare @rownumber varchar(256);
declare @condition nvarchar(3990);
declare @groupby varchar(50);
declare @sql varchar(3998);
declare @rowstartindex int;
declare @rowendindex int;
begin
set nocount on
if @orderby <> ''
set @orderby = ' order by ' + @orderby;
else
set @orderby = ' order by userid ' ;
set @select = ' select userid,username ,';
--设置排序语句
set @rownumber ='row_number() over (' + @orderby + ' ) as rownumber ';
set @select = @select + @rownumber;
set @from = ' from users ';
--设置条件语句@gulevel
set @condition = ' where 1=1 ';
set @condition = @condition + 'and userid > 0';
--分组语句
set @groupby = ' group by userid '
set @rowstartindex = ( @pageindex -1) * @pagesize + 1
set @rowendindex = @pageindex * @pagesize ;
--查询结果
set @sql = 'set nocount on;
with resulttable as ( ' + @select + @from + @condition +')
select * from resulttable where rownumber between ' +
cast(@rowstartindex as varchar(32)) + ' and ' + cast(@rowendindex as varchar(32))
+ ' ; select count(*) as totalcount ' + @from + @condition + ' '

--print @sql;
exec(@sql);
end