ASP.NET 分頁預存程序優化：從臨時表到子查詢的演進實戰

原版分頁預存程序使用 #Temp_Paging 臨時表儲存主鍵與行號，但主鍵型別固定為 INT，無法支援 uniqueidentifier 等類型。本文記錄兩次重構嘗試與最終解決方案。

原版缺陷

CREATE TABLE #Temp_Paging (Temp_Paging_Id INT, RowNumber INT)
-- 主鍵型別寫死為 INT，不通用

嘗試一：動態建立臨時表（失敗）

透過系統表查詢主鍵型別，動態組裝 CREATE TABLE：

DECLARE @colType NVARCHAR(50)
SELECT @colType = typ.name FROM sys.columns col ...
SET @strSQL3 = 'CREATE TABLE #Temp_Paging (Temp_Paging_Id ' + @colType + ', RowNumber INT)'
EXEC(@strSQL3)

失敗原因： EXEC() 開啟新會話，會話結束時區域臨時表 (#) 即被自動刪除，後續語句找不到表。

最終方案：子查詢替代臨時表（成功）

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), @strSQL2 NVARCHAR(4000), @strSQL3 NVARCHAR(4000)

    -- 取得主鍵與行號的子查詢
    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 INT OUTPUT', @recordCount = @recordCount OUTPUT

    -- 頁碼修正
    IF @pageIndex > @recordCount * 1.0 / @pageSize + 1.0 OR @recordCount <= @pageSize
        SET @pageIndex = 1

    -- 分頁查詢：IN 子查詢直接引用 @strSQL1
    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

關鍵點總結

• 移除臨時表，改用 SELECT ... FROM (subquery) TempTable 衍生表，天然支援任意主鍵型別。
• 使用 SP_EXECUTESQL 參數化輸出 @recordCount，避免字串拼接風險。
• ROW_NUMBER() OVER (ORDER BY ...) 為 SQL Server 2005+ 標準分頁寫法。

此版本通用、效能穩定，適合作為專案標準分頁元件。