SET vs SELECT 賦值：為什麼標量子查詢必須用 SET 避免靜默多行錯誤

核心差異：單行保證 vs 靜默截取

實證腳本：重現靜默 Bug

CREATE TABLE dbo.SetTest (PK INT PRIMARY KEY, Name VARCHAR(30));
INSERT dbo.SetTest VALUES (1,'Alice'), (2,'Bob'), (3,'Alice'); -- 兩個 Alice
GO

-- 1. SELECT 賦值：靜默取最後一行 PK=3
DECLARE @V1 INT;
SELECT @V1 = PK FROM dbo.SetTest WHERE Name = 'Alice';
PRINT 'SELECT result: ' + CAST(@V1 AS VARCHAR(10)); -- 輸出 3，業務邏輯可能預期 1 或報錯

-- 2. SET 賦值：直接報錯，暴露數據質量問題
DECLARE @V2 INT;
BEGIN TRY
    SET @V2 = (SELECT PK FROM dbo.SetTest WHERE Name = 'Alice');
END TRY
BEGIN CATCH
    PRINT 'SET Error: ' + ERROR_MESSAGE(); -- Msg 512, Level 16, Subquery returned more than 1 value
END CATCH
GO

DROP TABLE dbo.SetTest;
GO

最佳實踐規範

1. 標量賦值一律用 SET：`SET @Var = (SELECT SingleCol FROM ... WHERE PK = @Key);`
2. 多行聚合用 SELECT + 聚合函數：`SELECT @Var = MAX(Col) FROM ...` / `SELECT @Var = COUNT(*) FROM ...` — 明確語義，保證單行。
3. 需檢查存在性時：`IF EXISTS (SELECT 1 FROM ...)` 或 `SET @Var = (SELECT TOP 1 Col FROM ... ORDER BY ...)` — 明確取第一行。
4. 代碼審查清單：掃描所有 `SELECT @Var =` 模式，確認是否為聚合/TOP 1/唯一鍵查詢，否則改為 `SET`。

進階：動態 SQL 中的陷阱

-- 錯誤模式：動態 SQL 內部 SELECT 賦值給外部變量 (作用域隔離，實際無效)
DECLARE @Outer INT;
EXEC sp_executesql N'SELECT @Inner = PK FROM dbo.SetTest WHERE Name = ''Alice''', N'@Inner INT OUTPUT', @Outer OUTPUT;
-- @Outer 仍為 NULL，因為 @Inner 是內部變量

-- 正確模式：sp_executesql 輸出參數
DECLARE @Outer INT;
EXEC sp_executesql N'SET @Outer = (SELECT PK FROM dbo.SetTest WHERE Name = ''Alice'')', N'@Outer INT OUTPUT', @Outer OUTPUT;
-- 此處若多行會正確報錯

總結

`SET` 是**防禦性編程**的體現：用編譯期/運行期報錯替換靜默數據損壞。在關鍵業務邏輯 (計費、庫存、權限) 中，任何標量子查詢賦值**必須**使用 `SET`，並配合 `TRY/CATCH` 處理異常，將數據質量問題在最早環節攔截。