分页存储过程

create procedure Proc_PageList
(
	@sqlstr nvarchar(4000), --查询字符串 
	@currentpage int, --第N页 
	@pagesize int --每页行数 
)  
as  
set nocount on	--不返回计数
	declare @P1 int, @rowcount int  --P1是游标的id
	exec sp_cursoropen @P1 output,@sqlstr,@scrollopt=1,@ccopt=1,@rowcount=@rowcount output
	select @rowcount
	select ceiling(1.0*@rowcount/@pagesize) as 总页数--,@rowcount as 总行数,@currentpage as 当前页 
	set @currentpage=(@currentpage-1)*@pagesize+1
	exec sp_cursorfetch @P1,16,@currentpage,@pagesize 
	exec sp_cursorclose @P1
set nocount off

sp_cursoropen语法：

sp_cursoropen [@cursor =] cursor_handle OUTPUT, [@stmt =] 'stmt' [, [@scrollopt =] scroll_options OUTPUT] [, [@ccopt =] concurrency_options OUTPUT] [, [@rowcount =] rowcount OUTPUT] [ {, [@paramdef =] N'parameter_name data_type [,...n]' } {, [@param1 =] value1 [,...n] } ] 本文来自CSDN博客，转载请标明出处：http://blog.csdn.net/happyflystone/archive/2009/04/29/4137136.aspx