给你一个例子,
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
--利用SQL未公开的存储过程实现分页
create procedure p_splitpage
@sql nvarchar(4000), --要执行的sql语句
@currentpage int=2, --要显示的页码
@pagesize int=10, --每页的大小
@recordcount int=0 out, --记录数
@pagecount int=0 out --总页数
as
set nocount on
declare @p1 int
exec sp_cursoropen @p1 output,@sql,@scrollopt=1,@ccopt=1,@rowcount=@pagecount output
select @recordcount=@pagecount,@pagecount=ceiling(1.0*@pagecount/@pagesize) ,@currentpage=(@currentpage-1)*@pagesize+1
select @recordcount recordcount ,@pagecount pagecount,@currentpage currentpage
exec sp_cursorfetch @p1,16,@currentpage,@pagesize
exec sp_cursorclose @p1 go
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
--利用SQL未公开的存储过程实现分页
create procedure p_splitpage
@sql nvarchar(4000), --要执行的sql语句
@currentpage int=2, --要显示的页码
@pagesize int=10, --每页的大小
@recordcount int=0 out, --记录数
@pagecount int=0 out --总页数
as
set nocount on
declare @p1 int
exec sp_cursoropen @p1 output,@sql,@scrollopt=1,@ccopt=1,@rowcount=@pagecount output
select @recordcount=@pagecount,@pagecount=ceiling(1.0*@pagecount/@pagesize) ,@currentpage=(@currentpage-1)*@pagesize+1
select @recordcount recordcount ,@pagecount pagecount,@currentpage currentpage
exec sp_cursorfetch @p1,16,@currentpage,@pagesize
exec sp_cursorclose @p1 go
http://topic.csdn.net/t/20040825/14/3308140.html
--try
declare @P1 int,@P2 int,@P3 int,@P4 int ,@sql nvarchar(2000)select @P1=180150477 ,@P2=8,@P3=1 ,@P4=2
@sql = N'SELECT csub_name ,cstation ,TaskState ,dintime ,csub_id,ctaskid FROM UA_TaskLog WHERE cAppServer like ''{E21A9F49-5FD7-4A29-BB31-FD3D932F97D1}%'' ORDER BY cstation,csub_name,TaskState'exec sp_cursoropen @P1 output, @sql, @P2 output, @P3 output, @P4 output
select @P1, @P2, @P3, @P4
N'SELECT csub_name ,cstation ,TaskState ,dintime ,csub_id,ctaskid
FROM UA_TaskLog
WHERE cAppServer like ''{E21A9F49-5FD7-4A29-BB31-FD3D932F97D1}%''
ORDER BY cstation,csub_name,TaskState',
@P2 output,
@P3 output,
@P4 output --看起来似乎没有错误啊......