SP_PaginationByCursor.sql

USE [JHLKCRM]
GO
/****** Object:  StoredProcedure [dbo].[SP_PaginationByCursor]    Script Date: 05/12/2017 15:57:13 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
 
ALTER  PROC [dbo].[SP_PaginationByCursor]
@HandlerSql NVARCHAR(MAX),    --要执行的 SQL 语句
@PageIndex  INT = 1,          --要显示的页码，从 1 开始
@PageSize   INT = 10,         --每页的大小，-1 则表示获取全部
@RowsCount  INT OUTPUT        --总记录数
AS  
 
SET NOCOUNT ON
DECLARE @p1 int , @PageCount int
IF(@PageSize = -1)
    BEGIN
        EXEC(@HandlerSql)
        SET @RowsCount = @@rowcount
        RETURN
    END
ELSE
    BEGIN
        EXEC sp_cursoropen
            @cursor = @p1 OUTPUT ,
            @stmt = @HandlerSql,
            @scrollopt = 1,
            @ccopt = 1,
            @rowcount = @RowsCount OUTPUT
        IF ISNULL(@PageSize,0) < 1
            SET @PageSize = 10
 
        SELECT @PageCount = @RowsCount
        SET @PageCount = (@PageCount + @PageSize - 1)/@PageSize
   
        IF ISNULL(@PageIndex,0) < 1 OR ISNULL(@PageIndex,0) > @PageCount
            SET @PageIndex = 1
        ELSE
            SET @PageIndex = (@PageIndex - 1)*@PageSize + 1
 
        EXEC sp_cursorfetch @p1, 16, @PageIndex, @PageSize
        EXEC sp_cursorclose @p1
    END