SQL分页存储过程

<div id="_mcePaste">--存储过程实现分页</div>
<div id="_mcePaste">if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[p_splitpage]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)</div>
<div id="_mcePaste">drop procedure [dbo].[p_splitpage]</div>
<div id="_mcePaste">GO</div>
<div id="_mcePaste">create procedure p_splitpage</div>
<div id="_mcePaste">@sql nvarchar(4000), --要执行的sql语句</div>
<div id="_mcePaste">@currentpage int=1,  --要显示的页码</div>
<div id="_mcePaste">@pagesize int=10  --每页的大小</div>
<div id="_mcePaste">--@pagecount int=0 out --总页数</div>
<div id="_mcePaste">as</div>
<div id="_mcePaste">set nocount on</div>
<div id="_mcePaste">declare @p1 int,@pagecount int</div>
<div id="_mcePaste">exec sp_cursoropen @p1 output,@sql,@scrollopt=1,@ccopt=1,@rowcount=@pagecount output</div>
<div id="_mcePaste">select @pagecount=ceiling(1.0*@pagecount/@pagesize)</div>
<div id="_mcePaste">,@currentpage=(@currentpage-1)*@pagesize+1</div>
<div id="_mcePaste">--select @currentpage</div>
<div id="_mcePaste">exec sp_cursorfetch @p1,16,@currentpage,@pagesize</div>
<div id="_mcePaste">exec sp_cursorclose @p1</div>
<div id="_mcePaste">declare @sql2 varchar(5000)</div>
<div id="_mcePaste">set @sql2 = 'select count(*) from (' + @sql + ') tb'</div>
<div id="_mcePaste">exec(@sql2)</div>
<div id="_mcePaste">go</div>
--存储过程实现分页if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[p_splitpage]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[p_splitpage] GO
create procedure p_splitpage @sql nvarchar(4000), --要执行的sql语句@currentpage int=1,  --要显示的页码@pagesize int=10  --每页的大小--@pagecount int=0 out --总页数
asset nocount ondeclare @p1 int,@pagecount int
exec sp_cursoropen @p1 output,@sql,@scrollopt=1,@ccopt=1,@rowcount=@pagecount output
select @pagecount=ceiling(1.0*@pagecount/@pagesize) ,@currentpage=(@currentpage-1)*@pagesize+1--select @currentpageexec sp_cursorfetch @p1,16,@currentpage,@pagesize exec sp_cursorclose @p1

declare @sql2 varchar(5000)set @sql2 = 'select count(*) from (' + @sql + ') tb'exec(@sql2)go

转载于:https://www.cnblogs.com/joyho/articles/2602565.html