SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
ALTER PROCEDURE USP_P_SPLITPAGE
@SQL NVARCHAR(4000),--sql语句
@CURRENTPAGE INT=1,--当前页
@PAGESIZE INT=10--每页行数
--@RECORDCOUNT INT=0 OUTPUT,
--@PAGECOUNT INT=0 OUTPUT
AS
SET NOCOUNT ON
DECLARE @P1 INT,@RECORDCOUNT INT,@PAGECOUNT INT
EXEC SP_CURSOROPEN @P1 OUTPUT,@SQL,@SCROLLOPT=1,@CCOPT=1,@ROWCOUNT=@RECORDCOUNT OUT
SET @PAGECOUNT = CEILING(1.0*@RECORDCOUNT/@PAGESIZE)
IF @PAGESIZE<1
SET @PAGESIZE=10
IF @CURRENTPAGE<1
SET @CURRENTPAGE=1
IF @CURRENTPAGE> @PAGECOUNT
SET @CURRENTPAGE=@PAGECOUNT
SELECT @RECORDCOUNT AS RECORDCOUNT,
@PAGECOUNT AS PAGECOUNT,
@CURRENTPAGE AS CURRENTPAGE,
@PAGESIZE AS PAGESIZE
SET @CURRENTPAGE=(@CURRENTPAGE-1)*@PAGESIZE+1
EXEC SP_CURSORFETCH @P1,16,@CURRENTPAGE,@PAGESIZE
EXEC SP_CURSORCLOSE @P1
SET NOCOUNT OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO