SQL Server 時間統計預存程序進階實作：臨時表、動態 SQL、游標與 sp_executesql 多參數輸出

業務目標

統計指定站點（@SiteID）、時間區間（@StartTime ~ @EndTime）內，每小時（0~23 時）的資料總量 TotalNum，回傳 24 列結果供前端圖表繪製。

核心實作：迴圈 + 動態 SQL + 參數化輸出

CREATE PROCEDURE UserSiteNowDataHourTotalVIEW_List
    @StartTime VARCHAR(25),  -- 'YYYY-MM-DD HH:MI:SS'
    @EndTime   VARCHAR(25),
    @SiteID    INT
AS
BEGIN
    SET NOCOUNT ON;

    -- 正規化日期格式
    SET @StartTime = REPLACE(@StartTime, '-', '.');
    SET @EndTime   = REPLACE(@EndTime, '-', '.');

    -- 建立暫存表收集 24 小時結果
    CREATE TABLE #Hour_Temp (
        ID INT IDENTITY(1,1) PRIMARY KEY,
        HourNum INT NOT NULL,   -- 0~23
        TotalNum INT NOT NULL
    );

    DECLARE @i INT = 0;
    DECLARE @SQL NVARCHAR(MAX);
    DECLARE @TotalNum INT;

    WHILE @i < 24
    BEGIN
        -- 動態建構單小時查詢
        SET @SQL = N'
            SELECT @TotalNum = SUM(TotalNum)
            FROM UserSiteNowDataHourTotalVIEW
            WHERE VisitTime BETWEEN @StartTime AND @EndTime
              AND [Hour] = @Hour';

        -- sp_executesql 參數化執行，取得 OUTPUT 參數
        EXEC sp_executesql @SQL,
            N'@StartTime VARCHAR(25), @EndTime VARCHAR(25), @Hour INT, @TotalNum INT OUTPUT',
            @StartTime, @EndTime, @i, @TotalNum OUTPUT;

        IF @TotalNum IS NULL SET @TotalNum = 0;

        INSERT INTO #Hour_Temp (HourNum, TotalNum) VALUES (@i, @TotalNum);
        SET @i = @i + 1;
    END

    SELECT HourNum, TotalNum FROM #Hour_Temp ORDER BY HourNum;
END;

關鍵技術點深度解析

1. sp_executesql 多參數 OUTPUT 模式

-- 範例：同時輸出多個值
DECLARE @Hour INT = 15, @TableName NVARCHAR(200) = N'UserSiteNowDataHourTotalVIEW';
DECLARE @SQL NVARCHAR(MAX) = N'SELECT @Value = TotalNum FROM ' + @TableName + N' WHERE [Hour] = @Hour';
DECLARE @Value INT;

EXEC sp_executesql @SQL,
    N'@Hour INT, @Value INT OUTPUT',
    @Hour = 12, @Value = @Value OUTPUT;  -- 命名參數傳遞，順序無關

PRINT @Value;

• 參數定義字串：N'@p1 type1, @p2 type2 OUTPUT, ...'
• 實際參數傳遞：@p1 = val1, @p2 = @var OUTPUT，支援命名繫結。
• 優於 EXEC(@sql)：計畫重用、型別安全、防注入。

2. 臨時表 vs 表變數

• #Hour_Temp（區域暫存表）：可建立索引、統計資料、支援平行查詢、大資料量效能佳。
• @Hour_Temp（表變數）：無統計資料、無法建立索引（SQL 2014+ 可加主鍵/唯一索引）、小資料量（< 1000 列）較佳。
• 本案 24 列固定，兩者皆可；習慣用暫存表以便除錯 SELECT * FROM #Hour_Temp。

3. 日期格式正規化

REPLACE(@StartTime, '-', '.') 將 YYYY-MM-DD 轉為 YYYY.MM.DD，確保 BETWEEN 在隱式轉換時不受語言/日期格式設定影響（SET DATEFORMAT）。更穩健做法：參數直接用 DATETIME 型別，避免字串轉換。

4. 效能優化建議

1. 單查詢聚合替代迴圈：若資料量大，24 次查詢可合併為一次 GROUP BY DATEPART(HOUR, VisitTime)，再用條件聚合或 PIVOT 轉橫向。
   

   SELECT DATEPART(HOUR, VisitTime) AS HourNum, SUM(TotalNum) AS TotalNum
   FROM UserSiteNowDataHourTotalVIEW
   WHERE VisitTime BETWEEN @StartTime AND @EndTime
   GROUP BY DATEPART(HOUR, VisitTime)
   ORDER BY HourNum;

2. 確保 VisitTime、Hour 有適當索引。
3. 若必須迴圈，考慮將動態 SQL 移出迴圈，僅在迴圈內執行 sp_executesql 並更換 @Hour 參數值。