SQL Server 開發備忘錄：執行順序、存儲過程模板、分頁、表值參數與實用技巧大全

P1：SQL 語句邏輯執行順序（必背）

理解順序是寫出高性能查詢的前提：

1. FROM / JOIN → 產生虛擬表 VT1（笛卡爾積）
2. ON → 篩選 VT1 → VT2
3. OUTER JOIN → 添加外部行 → VT3
4. WHERE → 篩選 VT3 → VT4
5. GROUP BY → 分組 VT4 → VT5
6. CUBE | ROLLUP → 超組 → VT6
7. HAVING → 篩選組 → VT7
8. SELECT → 選列 → VT8
9. DISTINCT → 去重 → VT9
10. ORDER BY → 排序 → VC10（游標）
11. TOP / OFFSET-FETCH → 取子集 → VT11（最終結果）

核心啟示：SELECT 列表最後執行，別名不可在 WHERE/GROUP BY/HAVING 中使用；FROM 最先執行，駕駛表選擇關鍵。

P2：帶 TRY...CATCH 的存儲過程標準模板

CREATE PROCEDURE [dbo].[usp_BusinessAction]
    @Param1 INT,
    @Param2 NVARCHAR(100)
AS
BEGIN
    SET NOCOUNT ON;          -- 關閉 DONE_IN_PROC 消息，提升性能
    SET XACT_ABORT ON;       -- 運行時錯誤自動回滾事務
    DECLARE @StartTime DATETIME2 = SYSDATETIME(); -- 統一時間基準

    BEGIN TRY
        BEGIN TRANSACTION;
        -- === 核心業務邏輯 ===
        -- INSERT/UPDATE/DELETE ...
        COMMIT TRANSACTION;
    END TRY
    BEGIN CATCH
        IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION;
        DECLARE @ErrMsg NVARCHAR(4000) = ERROR_MESSAGE(),
                @ErrSev INT = ERROR_SEVERITY(),
                @ErrState INT = ERROR_STATE();
        RAISERROR(@ErrMsg, @ErrSev, @ErrState); -- 重拋異常給調用方
        RETURN -1;
    END CATCH
END;

P3：帶顯式事務控制的存儲過程模板

同上，但在 TRY 塊中顯式管理 BEGIN/COMMIT/ROLLBACK TRANSACTION，適用於多語句必須原子提交的場景。

P8：分組取 Top N（ROW_NUMBER 經典模式）

SELECT *
FROM (
    SELECT ROW_NUMBER() OVER (PARTITION BY ProductNO ORDER BY CreateDate DESC) AS rn, *
    FROM dbo.ItemInfo
) t
WHERE rn <= 3; -- 每組前 3 條

P9：用戶定義表類型—— 存儲過程傳表的正規軍

創建類型

CREATE TYPE [dbo].[GiftCardBatch] AS TABLE (
    RowNum INT NULL,
    GiftCardNO VARCHAR(500) NULL,
    UsedDate DATETIME NULL,
    CustomerName VARCHAR(50) NULL,
    ReduceAmount DECIMAL(18,2) NULL,
    Amount DECIMAL(18,2) NULL
);

調用端（C# / 應用層）

// C# 偽碼
var dt = new DataTable();
dt.Columns.Add("RowNum", typeof(int));
dt.Columns.Add("GiftCardNO", typeof(string));
// ... 填充行
using (var cmd = new SqlCommand("usp_ProcessGiftCards", conn) { CommandType = CommandType.StoredProcedure })
{
    var p = cmd.Parameters.AddWithValue("@Batch", dt);
    p.SqlDbType = SqlDbType.Structured;
    p.TypeName = "dbo.GiftCardBatch";
    cmd.ExecuteNonQuery();
}

存儲過程端接收

CREATE PROCEDURE usp_ProcessGiftCards
    @Batch GiftCardBatch READONLY  -- 必須 READONLY
AS
BEGIN
    SET NOCOUNT ON;
    INSERT INTO dbo.GiftCardLog (CardNO, Customer, Amount, UsedDate)
    SELECT GiftCardNO, CustomerName, Amount, UsedDate
    FROM @Batch;
END;

優勢

• 強類型、編譯期檢查、避免 XML/字符串拼接解析開銷。
• 內部可像臨時表一樣 JOIN、INDEX（隱式）、統計信息。
• TVP 參數不支持 OUTPUT，僅輸入。

實用片段集錦