SQLServer 通用分页存储过程(使用游标)


/*
  Powered By CMSDream Copyright (c) All rights reserved.
  通用分页存储过程
*/
CREATE proc [dbo].[cmsdream_SP_MultiPage](
	@sql varchar(8000)='',
	@PageSize int=1,
	@CurrentPage int=1,
	@RecordCount int=0 output,
	@PageCount int=1 output
)AS
if @PageSize < 1 set @PageSize = 1
if @CurrentPage < 1 set @CurrentPage = 1
begin
	set nocount on

	declare @p1 int,
	@rowcount int
	exec sp_cursoropen @p1 output,@sql,@scrollopt=1,@ccopt=1,@rowcount=@rowcount output
	set @PageCount = ceiling(1.0 * @rowcount / @PageSize)   
	set @CurrentPage = (@CurrentPage - 1) * @PageSize + 1
	set @RecordCount = @rowcount
	exec sp_cursorfetch @p1, 16, @CurrentPage, @PageSize
	set nocount off
end

------------------------
-- test:
-- exec cmsdream_SP_MultiPage 'select * from table', 10, 1
------------------------

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO


15年前9月20日 阅读:283 评论:0

青锋幽灵

゛. - 看過世间最冷漠滴眼神,  爱過⒈生最无缘滴人﹎

评论 More..

该页面还没有任何评论,赶快占个沙发吧!

登录 注册

您没有登录,如果还不是会员请先注册

文明上网,理性发帖!


顶部