SQL Server 動態 SQL 預存程序實戰：條件式查詢與日期範圍過濾

業務需求

依使用者 ID、站點 ID、時間區間動態查詢 UserSiteNowDataVIEW 視圖。參數可能為 0/空值代表「不限制」。

預存程序完整實作

CREATE PROCEDURE UserSiteNowDataVIEW_List
    @UserID    INT,
    @Type      INT,             -- 保留擴充：0=今日 1=昨日 2=本月
    @StartTime VARCHAR(25),     -- 格式 'YYYY-MM-DD HH:MI:SS'
    @EndTime   VARCHAR(25),
    @SiteID    INT
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @SQL NVARCHAR(MAX) = N'
        SELECT *
        FROM dbo.UserSiteNowDataVIEW
        WHERE VisitTime BETWEEN @StartTime AND @EndTime';

    -- 動態附加條件
    IF @UserID <> 0
        SET @SQL += N' AND UserID = @UserID';
    IF @SiteID <> 0
        SET @SQL += N' AND SiteID = @SiteID';

    -- 使用 sp_executesql 參數化執行（防注入、計畫重用）
    EXEC sp_executesql @SQL,
        N'@StartTime VARCHAR(25), @EndTime VARCHAR(25), @UserID INT, @SiteID INT',
        @StartTime, @EndTime, @UserID, @SiteID;
END;

關鍵技術點解析

• sp_executesql vs EXEC()：sp_executesql 支援參數標記，避免字串拼接注入風險，且相同查詢結構可重用執行計畫。
• 日期參數型別：傳入 VARCHAR 後在 SQL 中隱式轉換為 DATETIME；建議直接用 DATETIME 參數型別避免格式歧義。
• BETWEEN 邊界包含：BETWEEN '2024-01-01' AND '2024-01-31' 只包含至 00:00:00；跨日查詢建議用 VisitTime >= @StartTime AND VisitTime < DATEADD(DAY, 1, @EndTime)。
• 註解掉的 Type 邏輯：可改用 CASE 或查詢前預先計算日期範圍，再傳入 @StartTime/@EndTime，保持預存程序單一職責。

效能優化建議

1. 確保 VisitTime、UserID、SiteID 有適當索引（複合索引 (VisitTime, UserID, SiteID) 可支援多種條件組合）。
2. 若資料量極大，考慮分頁回傳（參考 GetRecordByPage 模式）。
3. 避免在 WHERE 子句對欄位使用函數（如 CONVERT(VARCHAR, VisitTime, 120) = ...），導致索引失效。

擴充：支援 IN 清單參數

若 @UserID 需支援多值（如 '1,5,9'），可搭配 STRING_SPLIT（SQL 2016+）或自訂分割函數：

IF @UserIDList IS NOT NULL
    SET @SQL += N' AND UserID IN (SELECT value FROM STRING_SPLIT(@UserIDList, '',''))';