分頁存儲過程實例剖析心得

作者：WILLPAN 2011-10-10 16:44:37

最近修改了個分頁存儲過程，作為菜鳥，還是從中獲益良多，這里就開始今天的分頁之旅了。

1.開始還原

下面先看一下原來的分頁存儲過程。

ALTER PROCEDURE [dbo].[sp_Sql_Paging] 
( 
    @SqlDataTable        NVARCHAR(4000),        -- 表名 
    @PrimaryKey            NVARCHAR(4000),        -- 主鍵名稱 
    @Fields                NVARCHAR(4000),        -- 要返回的字段 
    @pageSize            INT,                -- 頁尺寸 
    @pageIndex            INT,                -- 頁碼 
    @recordCount        INT    OUTPUT,            -- 記錄總數 
    @strOrderBy            NVARCHAR(4000),        -- 排序 
    @strWhere            NVARCHAR(4000)        -- 查詢條件 
) 
AS 
BEGIN 
    SET NOCOUNT ON 
    DECLARE @strSQL1    NVARCHAR(4000)        -- SQL語句1 
    DECLARE @strSQL2    NVARCHAR(4000)        -- SQL語句2 
 
    -- 創建臨時表 
    -- 用來保存表的編號以及主鍵 
    CREATE TABLE #Temp_Paging 
    ( 
        Temp_Paging_Id INT, 
        RowNumber INT 
    ) 
    SET @strSQL1 = 'INSERT INTO [#Temp_Paging](Temp_Paging_Id, RowNumber) SELECT ' + @PrimaryKey + ', ROW_NUMBER() OVER (' + @strOrderBy + ') AS RowNumber FROM ' + @SqlDataTable + ' ' + @strWhere  
    EXEC SP_EXECUTESQL @strSQL1 
    SET @recordCount = @@ROWCOUNT -- 取得總記錄數 
      
    -- 判斷頁索引 
    IF @pageIndex > @recordCount * 1.0 / @pageSize + 1.0 OR @recordCount <= @pageSize 
        BEGIN 
            SET @pageIndex = 1 
        END 
      
    -- 分頁查詢 
    SET @strSQL2 = 'SELECT ' + @Fields + ' FROM ' + @SqlDataTable + ' WHERE ' + @PrimaryKey + ' IN (SELECT Temp_Paging_Id FROM [#Temp_Paging] WHERE RowNumber BETWEEN ' + Str((@pageIndex - 1) * @pageSize + 1) + ' AND ' + Str(@pageIndex * @pageSize) + ') ' + @strOrderBy 
    EXEC SP_EXECUTESQL @strSQL2 
    DROP TABLE #Temp_Paging -- 刪除臨時表 
END

從原分頁存儲過程很容易看出，這里運用了臨時表保存編號，然后在通過pageIndex和pageSize計算所得，進行分頁。

因為這里還以主鍵作為查詢條件，故臨時表中也保存了主鍵值。

很顯然，這里的臨時表無法做到通用，因為主鍵的類型不一定是上面定義的INT型，也可以是其它的類型，比如：uniqueidentifier(全球唯一標識)。

這樣的話，這個存儲過程就碰到了問題，所以必須進行改進。

2.思路一

思路一很簡單，那就把這個類型聲明成一個變量，然后通過系統表獲取表的主鍵類型，再賦給變量不就可以了嗎。看起來很美妙，不妨試一試先。

我們可以插入下面一段代碼：

DECLARE @colType NVARCHAR(50)  --主鍵列類型 
DECLARE @strSQL3 NVARCHAR(500) --創建臨時表語句 
 
SET @colType = (SELECT typ.name as 數據類型 
                FROM sys.columns col  
                left join sys.types typ  
                on (col.system_type_id = typ.system_type_id AND col.user_type_id = typ.user_type_id) 
                WHERE col.object_id = (SELECT object_id FROM sys.tables WHERE name = @SqlDataTable) 
                and exists  
                ( SELECT 1 FROM sys.indexes idx  
                    join sys.index_columns idxCol  
                      on (idx.object_id = idxCol.object_id) 
                    WHERE idx.object_id = col.object_id 
                      AND idxCol.index_column_id = col.column_id  
                      AND idx.is_primary_key = 1 
                )) 
 
SET @strSQL3 = 'CREATE TABLE #Temp_Paging 
                ( 
                    Temp_Paging_Id '+ @colType+', 
                    RowNumber INT  
                )' 
PRINT @strSQL3 
--EXEC(@strSQL3)

打印結果：

CREATE TABLE #Temp_Paging 
                ( 
                    Temp_Paging_Id uniqueidentifier, 
                    RowNumber INT  
                )

很顯然我們得到了所需要的臨時表。這時我很開心，因為得到了我想要的東西。但似乎還沒有結束，我要執行下該存儲過程。

壞結果往往在這時出現：

這里就出現奇怪的事了，按打印出來的明明是正確的創建臨時表語句，而且也執行了，為什么接下來對臨時表的操作又是無效的了？

找資料問同事，終于明白，原來臨時表分本地臨時表和全局臨時表。本地臨時表需要注意實際刪除的時間。

這里說得簡單一點：當在用EXEC(@strSQL3) 創建臨時表的時候，同時已經刪除了臨時表。因為EXEC這個過程的會話已經結束，臨時表就被刪除了。

這里有一篇博文做了更具體的解釋，大家可以參考：點滴在心頭_SQL臨時表

#p#

3.思路二

上面的思路似乎行不通，那不如再換個思路，干脆不要建立臨時表，用Select * from (select * from table) as temptable 這種思路代替創建臨時表。

代碼如下：

ALTER PROCEDURE [dbo].[sp_Sql_Paging] 
( 
    @SqlDataTable        NVARCHAR(4000),        -- 表名 
    @PrimaryKey            NVARCHAR(4000),        -- 主鍵名稱 
    @Fields                NVARCHAR(4000),        -- 要返回的字段 
    @pageSize            INT,                -- 頁尺寸 
    @pageIndex            INT,                -- 頁碼 
    @recordCount        INT    OUTPUT,            -- 記錄總數 
    @strOrderBy            NVARCHAR(4000),        -- 排序 
    @strWhere            NVARCHAR(4000)        -- 查詢條件 
) 
AS 
BEGIN 
    SET NOCOUNT ON 
    DECLARE @strSQL1    NVARCHAR(4000)        -- SQL語句1 
    DECLARE @strSQL2    NVARCHAR(4000)        -- SQL語句2 
    DECLARE @strSQL3    NVARCHAR(4000)        -- SQL語句3 
 
    SET @strSQL1 = 'SELECT ' + @PrimaryKey + ', ROW_NUMBER() OVER (' + @strOrderBy +  ') AS RowNumber FROM ' + @SqlDataTable + ' ' + @strWhere  
     
    --獲取總記錄數 
    SET @strSQL3 = 'SELECT @recordCount = COUNT(*) FROM ' + @SqlDataTable + ' ' + @strWhere 
    EXEC SP_EXECUTESQL  
            @stmt = @strSQL3, 
            @params = N'@recordCount AS INT OUTPUT', 
            @recordCount = @recordCount OUTPUT 
 
    --分頁查詢 
    IF @pageIndex > @recordCount * 1.0 / @pageSize + 1.0 OR @recordCount <= @pageSize 
        BEGIN 
            SET @pageIndex = 1 
        END 
    SET @strSQL2 = 'SELECT ' + @Fields + ' FROM ' + @SqlDataTable + ' WHERE ' + @PrimaryKey + ' IN (SELECT '+@PrimaryKey+' FROM ('+@strSQL1+') TempTable WHERE RowNumber BETWEEN ' + Str((@pageIndex - 1) * @pageSize + 1) + ' AND ' + Str(@pageIndex * @pageSize) + ') ' + @strOrderBy 
    EXEC SP_EXECUTESQL @strSQL2 
END

這里有個小知識點，注意EXEC SP_EXECUTESQL的寫法以及和EXEC(@strsql)的區別。大家可以去找資料了解下。

關于分頁的一些事就寫到這了，僅供參考。

原文鏈接：http://www.cnblogs.com/willpan/archive/2011/10/10/Page.html

【編輯推薦】

常見數據庫的分頁實現方案
Oracle分頁小談
談談Java調用SQL Server分頁存儲過程
淺述asp.net海量分頁數據存儲過程
證明DataReader分頁的可行性

責任編輯：艾婧來源： WILLPAN的博客

分頁數據庫