SQL Server 儲存程序入門：從游標遍歷到動態 SQL 實戰範例

儲存程序基礎結構

CREATE PROCEDURE dbo.GetTableSpaceInfo
AS
BEGIN
    SET NOCOUNT ON
    -- 程序本體
END

SET NOCOUNT ON 關閉「受影響列數」訊息，減少網路往返、提升效能。

實戰範例：所有使用者表空間統計

CREATE PROCEDURE dbo.GetTableSpaceInfo
AS
BEGIN
    SET NOCOUNT ON

    -- 1. 建立結果暫存表
    IF OBJECT_ID('tempdb..#TableSpaceInfo') IS NOT NULL
        DROP TABLE #TableSpaceInfo
    CREATE TABLE #TableSpaceInfo (
        TableName SYSNAME,
        RowCount BIGINT,
        ReservedSpace VARCHAR(20),
        DataSpace VARCHAR(20),
        IndexSpace VARCHAR(20),
        UnusedSpace VARCHAR(20)
    )

    -- 2. 宣告游標遍歷所有使用者表
    DECLARE @TableName SYSNAME
    DECLARE TableCursor CURSOR LOCAL FAST_FORWARD FOR
        SELECT QUOTENAME(SCHEMA_NAME(schema_id)) + '.' + QUOTENAME(name)
        FROM sys.tables
        WHERE is_ms_shipped = 0
        ORDER BY name

    OPEN TableCursor
    FETCH NEXT FROM TableCursor INTO @TableName

    WHILE @@FETCH_STATUS = 0
    BEGIN
        -- 3. 動態執行 sp_spaceused
        DECLARE @Cmd NVARCHAR(MAX) = N'INSERT #TableSpaceInfo EXEC sp_spaceused @tbl'
        EXEC sp_executesql @Cmd, N'@tbl NVARCHAR(500)', @TableName

        FETCH NEXT FROM TableCursor INTO @TableName
    END

    CLOSE TableCursor
    DEALLOCATE TableCursor

    -- 4. 回傳結果
    SELECT * FROM #TableSpaceInfo ORDER BY TableName
END

關鍵技術點解析

現代改寫：集合導向替代游標

CREATE PROCEDURE dbo.GetTableSpaceInfo_V2
AS
BEGIN
    SET NOCOUNT ON

    SELECT 
        QUOTENAME(SCHEMA_NAME(t.schema_id)) + '.' + QUOTENAME(t.name) AS TableName,
        p.rows AS RowCount,
        CAST(SUM(a.total_pages) * 8. / 1024 AS DECIMAL(12,2)) AS ReservedMB,
        CAST(SUM(CASE WHEN a.type = 1 THEN a.total_pages ELSE 0 END) * 8. / 1024 AS DECIMAL(12,2)) AS DataMB,
        CAST(SUM(CASE WHEN a.type != 1 THEN a.total_pages ELSE 0 END) * 8. / 1024 AS DECIMAL(12,2)) AS IndexMB
    FROM sys.tables t
    JOIN sys.partitions p ON t.object_id = p.object_id AND p.index_id IN (0,1)
    JOIN sys.allocation_units a ON p.partition_id = a.container_id
    WHERE t.is_ms_shipped = 0
    GROUP BY t.schema_id, t.name, p.rows
    ORDER BY TableName
END

優點：單查詢、無游標開銷、平行化執行、易維護。新代碼首選。

儲存程序最佳實踐清單

1. 參數化：所有輸入用參數，絕不字串拼接
2. SET NOCOUNT ON：預設開啟
3. TRY...CATCH：統一錯誤處理，THROW 重拋
4. 交易控制：XACT_ABORT ON、明確 COMMIT/ROLLBACK
5. 避免 SELECT *：明確列出欄位
6. 命名規範：dbo.<動詞><名詞>，如 GetTableSpaceInfo
7. 註解完整：用途、參數、回傳、修改記錄
8. 版本控管：.sql 檔案納入 Git，部署用 CI/CD

常見陷阱

• 游標未 DEALLOCATE 導致記憶體洩漏
• 動態 SQL 未參數化 → 注入風險、計畫快取膨脹
• 暫存表名稱衝突 → 用 ## 或 #
• 權限不足：呼叫者需有基礎表權限或簽署憑證
• 參數嗅探導致計畫不穩 → OPTION (RECOMPILE) / OPTIMIZE FOR UNKNOWN