分页存储过程

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