分頁存儲過程

分頁存儲過程：（查詢，支持物理表、視圖、臨時表和查詢表達式）

ALTER PROC [dbo].[QueryByPage]
    @Capacity INT,    --每頁的行數
    @PageNumber INT,  --查詢頁的索引
    @Total INT OUTPUT,--返回總數
    @Query NVARCHAR(MAX),--查詢，支持物理表、視圖、臨時表和查詢表達式
    @OrderColumns NVARCHAR(MAX),     --排序欄位,請用逗號隔開    
    @Desc BIT                 --1:降序;
                              --0:升序                        
                         

AS
    DECLARE @queryStr NVARCHAR(MAX);
    DECLARE @newline AS NVARCHAR(2);
    DECLARE @TempSQL NVARCHAR(MAX);
    DECLARE @TempTable NVARCHAR(MAX);
    DECLARE @TempTable2 NVARCHAR(MAX);
    DECLARE @Delimeter NVARCHAR(10);
BEGIN
    
    SET NOCOUNT ON;
    SET @newline = NCHAR(13) + NCHAR(10);
    SET @TempTable=QUOTENAME('##'+CAST(NEWID() AS NVARCHAR(100)));
    SET @TempTable2=QUOTENAME('##'+CAST(NEWID() AS NVARCHAR(100)));
    SET @Delimeter=',';
    
    IF COALESCE(OBJECT_ID(@Query, N'U'),
            OBJECT_ID(@Query, N'V'),OBJECT_ID('tempdb.dbo.'+@Query)) IS NOT NULL
    BEGIN
       SET @queryStr = N'SELECT * FROM ' + @Query;
    END
    ELSE
    BEGIN
        SET @queryStr=@Query;
    END

    -- Make the query a derived table
    SET @queryStr = N'( ' + @queryStr + @newline + N'      ) AS Query';
    
    SET @TempSQL='SELECT @Total=COUNT(*) FROM '+@queryStr
    EXECUTE sp_executesql @TempSQL,N'@Total INT OUTPUT',@Total OUTPUT;

    IF (@Capacity > 0)
    BEGIN    
        IF ISNULL(@PageNumber,0) < 1 
        BEGIN
            SET @PageNumber = 1;
        END
        ELSE IF (@PageNumber > CEILING(@Total * 1.0 / @Capacity))
        BEGIN
            SET @PageNumber = CEILING(@Total * 1.0 / @Capacity);
        END;
        
        SET @TempSQL= 'SELECT TOP(@Capacity*@PageNumber) *' 
                      +' INTO '+@TempTable
                      +' FROM '+@queryStr
                      +' ORDER BY '+@OrderColumns
                      +CASE WHEN @Desc=1 THEN ' DESC' ELSE ' ASC 'END

        EXECUTE sp_executesql @TempSQL,N'@Capacity INT,@PageNumber INT',@Capacity,@PageNumber;
        
        SET @TempSQL =N'SELECT ROW_NUMBER() OVER(ORDER BY '+@OrderColumns
        +CASE WHEN @Desc=1 THEN ' DESC'        ELSE '        ASC 'END +'  ) AS RowNum,* INTO '
        +@TempTable2+' FROM '+@TempTable+' ORDER BY RowNum';
        
        EXECUTE sp_executesql @TempSQL,N'@Capacity INT,@PageNumber INT',@Capacity,@PageNumber;
        
        SET @TempSQL ='SELECT TOP (@Capacity) p.* FROM '+@TempTable2
        +' AS p WHERE p.RowNum>@Capacity*(@PageNumber-1) ORDER BY RowNum';
    
        EXECUTE sp_executesql @TempSQL,N'@Capacity INT,@PageNumber INT',@Capacity,@PageNumber;

        EXEC ('TRUNCATE TABLE '+@TempTable);
        EXEC ('DROP TABLE '+@TempTable);
            
        EXEC ('TRUNCATE TABLE '+@TempTable2);
        EXEC ('DROP TABLE '+@TempTable2);
    END
    ELSE
    BEGIN
        SET @TempSQL= 'SELECT *' 
                      +' FROM '+@queryStr
                      +' ORDER BY '+@OrderColumns
                      +CASE WHEN @Desc=1 THEN ' DESC' ELSE ' ASC 'END

        EXECUTE sp_executesql @TempSQL;                
    END;
END