分页存储过程

IF ( EXISTS ( SELECT * FROM sysobjects WHERE name = 'sp_KNO_Query_Recycle_kledge' ) )
    DROP PROCEDURE sp_KNO_Query_Recycle_kledge
GO

set ansi_nulls on 
go

create procedure sp_Recycle                        @AccountID    int, --企业的id号
                                                @UserID        int=0,    --访客的标识
                                                @beginTime    datetime='',    --开始时间
                                                @endTime    datetime='',    --结束时间
                                                @key        nvarchar(100)='',    --关键词
                                                @PageSize    int,    --每页行数
                                                @PageIndex    int,    --第x页
                                                @RowCount    int out,    --总行数
                                                @TotalPages    int out    --总页数
with encryption 
AS
   SET NOCOUNT ON

   DECLARE   @PROCEDURE_ID   INT   --存储过程编号
   SELECT    @PROCEDURE_ID = -600620000

DECLARE @strSql varchar(8000)
 --定义一个存储SQL语句的变量DECLARE @recordBegin INT

DECLARE @columnClause VARCHAR(1000)
DECLARE @whereClause VARCHAR(1000)

SET @RowCount = 0
SET @TotalPages =0

    IF( DBO.fn_KNO_CheckFunctionRight( @AccountID,
                                           @UserID,
                                           200
                                           ) < 0)
        RETURN @PROCEDURE_ID - 100

IF @PageSize <=0 or @PageIndex <0
BEGIN
    RAISERROR ('传入的页码或每页记录数大小参数错误!', 16, 1)
    RETURN @PROCEDURE_ID - 2100
END

SET @whereClause = ' WHERE C.AccountID = '+ CONVERT( CHAR, @AccountID ) + ' AND A.status = 2 AND C.privateFlag = 0 '

SET @whereClause = @whereClause + ' AND A.opTime >= '+ '''' + CONVERT( CHAR(8), @beginTime, 112 ) + ''''
SET @whereClause = @whereClause + ' AND A.opTime <= '+ '''' + CONVERT( CHAR(8), DATEADD ( dd , 1, @endTime ), 112 ) + ''''
IF( @key <> '' AND len( @key ) > 1 )
    BEGIN
        SELECT @whereClause=@whereClause+' AND ( charIndex('' '+@key+' '''+','' ''+A.[key]+'' '''+')>0 OR charIndex('''+@key+''',A.[title])>0 OR charIndex('''+@key+''',A.[content])>0) '
    END

IF( @RowCount <= 0 )
BEGIN
     CREATE table #rowC( 
                rw int null
     )

     EXEC( 'INSERT #rowC 
            SELECT ISNULL( COUNT(*), 0 ) 
            FROM kledge AS A WITH(NOLOCK)
               INNER JOIN dir as C with(nolock) on C.dirID = A.dirID 
                    ' + @whereClause )
    SELECT  @RowCount = rw
    FROM    #rowC
    DROP TABLE #rowC
END

IF( @RowCount IS NULL OR @RowCount=0)
     RETURN @PROCEDURE_ID - 1050

SET @columnClause = ' A.kID,C.dirID,C.dirName,A.title,A.[content], A.[key], A.autoReply, convert( char(19), A.newTime, 120 ) as newTime,
                     A.way,A.hide,E.name,A.opTime '

--总页数
SET @TotalPages=CASE WHEN @RowCount%@PageSize=0 THEN  @RowCount/@PageSize ELSE @RowCount/@PageSize+1 END


--如果是第一页，直接处理
IF(@PageIndex=0)
BEGIN
    SET @strSql = 'SELECT TOP '+CONVERT(VARCHAR(10),@PageSize)+' '+ @columnClause+' FROM kledge AS A WITH(NOLOCK)'
    SET @strSql = @strSql+' LEFT JOIN dir as C with(nolock) on C.dirID = A.dirID 
                            LEFT JOIN [User] as E with(nolock) on E.UserID = A.submitUserID
                                '
    SET @strSql = @strSql+@whereClause+' ORDER BY A.opTime DESC '
END
ELSE
BEGIN
    
    IF @PageIndex+1>=@TotalPages OR @PageIndex=-1 
    BEGIN
        SET @strSql='SELECT TOP '+CONVERT(VARCHAR(15),@PageSize)+' '+ @columnClause+'
        FROM (
            SELECT TOP '+CONVERT(VARCHAR(15),CASE WHEN @RowCount%@PageSize=0 THEN @PageSize ELSE @RowCount%@PageSize END ) + ' A.opTime ,A.kID
            FROM kledge AS A WITH(NOLOCK) '
            SET @strSql = @strSql+' LEFT JOIN dir as C with(nolock) on C.dirID = A.dirID  '
        SET @strSql = @strSql +@whereClause+' order by A.opTime ASC '
        SET  @strSql = @strSql +') as b LEFT JOIN kledge AS A WITH(NOLOCK) ON A.kID=B.kID 
                                LEFT JOIN dir as C with(nolock) on C.dirID = A.dirID
                                LEFT JOIN [User] as E with(nolock) on E.UserID = A.submitUserID 
        ORDER BY A.opTime DESC '
    END
    ELSE
    BEGIN
        --中间页（上）
        IF  @PageIndex+1<@TotalPages/2+1 AND @PageIndex+1>1
        BEGIN
            SET @strSql='
            SELECT TOP '+CONVERT(VARCHAR(15),@PageSize)+' '+ @columnClause+'
            FROM (
                SELECT TOP '+CONVERT(VARCHAR(15),@PageSize)+' B.kID ,B.opTime
                FROM (
                    SELECT TOP '+CONVERT(VARCHAR(15),@PageSize*(@PageIndex+1))+' A.kID,A.opTime
                    FROM kledge AS A WITH(NOLOCK) '            

                SET @strSql = @strSql+' LEFT JOIN dir as C with(nolock) on C.dirID = A.dirID '

            SET @strSql = @strSql +@whereClause                
            SET @strSql = @strSql +' ORDER BY A.opTime DESC
                           )  AS B                        
                ORDER BY B.opTime ASC
                       ) AS B'
            SET @strSql = @strSql+'    LEFT JOIN kledge AS A WITH(NOLOCK) ON A.kID=B.kID 
                                LEFT JOIN dir as C with(nolock) on C.dirID = A.dirID
                                LEFT JOIN [User] as E with(nolock) on E.UserID = A.submitUserID  
                            ORDER BY A.opTime DESC '
        END
            ELSE
        --中间页（下）
            BEGIN
             SET @strSql='
             SELECT TOP '+CONVERT(VARCHAR(15),@PageSize)+' '+ @columnClause+'
             FROM (
                 SELECT TOP '+CONVERT(VARCHAR(15),@PageSize)+'B.kID ,B.opTime

                 FROM (             
                     SELECT TOP '+CONVERT(VARCHAR(15),@RowCount - ( @PageIndex *@PageSize) )+' A.kID, A.opTime
                     FROM kledge AS A WITH(NOLOCK) '

                SET @strSql = @strSql+' LEFT JOIN dir as C with(nolock) on C.dirID = A.dirID '
                    
             SET @strSql = @strSql +@whereClause
             SET @strSql = @strSql +' ORDER BY A.opTime ASC
                                    )  AS B                        
                         ORDER BY B.opTime DESC 
                        ) AS B'
             SET @strSql = @strSql+'    LEFT JOIN kledge AS A WITH(NOLOCK) ON A.kID=B.kID 
                                LEFT JOIN dir as C with(nolock) on C.dirID = A.dirID
                                LEFT JOIN [User] as E with(nolock) on E.UserID = A.submitUserID 
                            ORDER BY A.opTime DESC '    
            END
        END
END

--PRINT @strSql
EXEC(@strSql)
return @TotalPages
go