SQL暫存資料表加分頁操作

標籤：style   blog   color   io   re   c   

常用的分頁儲存

    DECLARE @SQL NVARCHAR(2000)    SET @SQL = ‘SELECT @TotalCount=COUNT(1) FROM #temptable‘ + @Condition    EXEC sp_executesql @SQL, N‘@TotalCount INT OUTPUT‘, @TotalCount OUTPUT     DECLARE @ExeSQL NVARCHAR(4000)      SET @ExeSQL = ‘   WITH temp              AS ( SELECT TOP ‘ + @LastRow        + ‘                            #temptable.* ,                            ROW_NUMBER() OVER ( ORDER BY #temptable.MeetID ) AS PX                   FROM     #temptable ‘ + @Condition + ‘                   ORDER BY #temptable.MeetID                 )        SELECT  *        FROM    temp        WHERE   PX >=‘ + @FirstRow     EXEC (@ExeSQL)    

建立暫存資料表

IF OBJECT_ID(‘tempdb..#tempMettings‘) IS NOT NULL         DROP TABLE    #tempMettings    SELECT  a.Id ,            a.Metting_DateBet ,            a.Meeting_DateEnd ,            a.Metting_Date ,            a.Metting_Time ,            a.Meeting_FTime ,            a.Site ,            a.SiteType    INTO    #tempMettings    FROM    ( SELECT    Id ,                        Metting_DateBet ,                        Meeting_DateEnd ,                        Metting_Date ,                        Meeting_FDate Metting_Time ,                        Meeting_FTime ,                        Site ,                        SiteType              FROM      Mobile_Manager_Mettings              WHERE     SiteType = ‘2‘                        AND Status NOT IN ( 0, 7, 2 )            ) a