CloudInsight Logo
CloudInsight
返回首頁SQL

SQL Stored Procedure 教學：預存程序建立與最佳實踐

17 min 分鐘閱讀

SQL Stored Procedure 教學：預存程序建立與最佳實踐

SQL Stored Procedure 教學：預存程序建立與最佳實踐【完整指南】

預存程序（Stored Procedure）是 SQL Server 中最強大的功能之一，能將複雜的 SQL 邏輯封裝成可重複使用的程式單元。本文將從基礎語法開始，深入介紹參數使用、錯誤處理、效能優化，並透過實戰範例幫助您掌握預存程序的開發技巧。

預存程序概念與優勢

預存程序是預先編譯並儲存在資料庫中的 SQL 語句集合，可透過名稱呼叫執行。

什麼是預存程序？

簡單來說，預存程序就像是資料庫中的「函式」或「子程式」：

-- 呼叫預存程序（就像呼叫函式一樣簡單）
EXEC GetEmployeeById @EmployeeId = 1001;

使用預存程序的優勢

優勢說明
效能提升預先編譯的執行計畫，減少每次執行的編譯開銷
程式碼重用一次撰寫，多處呼叫，避免重複程式碼
安全性增強使用者只需執行權限，無需直接存取資料表
維護便利邏輯集中管理，修改一處即可全面生效
減少網路流量只傳遞參數與結果，不需傳送完整 SQL 語句
交易控制可在程序內實作完整的交易邏輯

預存程序 vs 直接執行 SQL

-- 方式一：直接執行 SQL（每次都要傳送完整語句）
SELECT e.Name, e.Email, d.DepartmentName
FROM Employees e
JOIN Departments d ON e.DepartmentId = d.Id
WHERE e.Status = 'Active' AND d.Location = 'Taipei';

-- 方式二：呼叫預存程序（只傳參數，更安全高效）
EXEC GetActiveEmployeesByLocation @Location = 'Taipei';

想了解更多 SQL 進階功能？請參考 SQL 進階應用實戰技巧。

CREATE PROCEDURE 語法

基本語法結構

建立預存程序的基本語法如下：

CREATE PROCEDURE 程序名稱
    @參數1 資料型別,
    @參數2 資料型別 = 預設值
AS
BEGIN
    SET NOCOUNT ON;

    -- SQL 語句
    SELECT * FROM TableName WHERE Column = @參數1;
END
GO

完整範例：建立第一個預存程序

-- 建立取得員工資料的預存程序
CREATE PROCEDURE usp_GetEmployeeById
    @EmployeeId INT
AS
BEGIN
    SET NOCOUNT ON;

    SELECT
        EmployeeId,
        Name,
        Email,
        Department,
        HireDate
    FROM Employees
    WHERE EmployeeId = @EmployeeId;
END
GO

-- 執行預存程序
EXEC usp_GetEmployeeById @EmployeeId = 1001;

命名慣例

良好的命名慣例有助於程式碼維護：

前綴用途範例
usp_使用者預存程序（User Stored Procedure）usp_GetEmployee
sp_避免使用（系統保留）-
rpt_報表相關程序rpt_MonthlySales
etl_資料處理程序etl_ImportOrders

重要提醒：避免使用 sp_ 前綴，因為 SQL Server 會先搜尋 master 資料庫，影響效能。

修改與刪除預存程序

-- 修改現有的預存程序
ALTER PROCEDURE usp_GetEmployeeById
    @EmployeeId INT,
    @IncludeInactive BIT = 0  -- 新增參數
AS
BEGIN
    SET NOCOUNT ON;

    SELECT EmployeeId, Name, Email, Department
    FROM Employees
    WHERE EmployeeId = @EmployeeId
      AND (@IncludeInactive = 1 OR Status = 'Active');
END
GO

-- 刪除預存程序
DROP PROCEDURE IF EXISTS usp_GetEmployeeById;

參數使用

預存程序支援輸入參數、輸出參數與預設值，提供靈活的資料傳遞方式。

輸入參數 INPUT

輸入參數用於將資料傳入預存程序：

CREATE PROCEDURE usp_SearchEmployees
    @Department NVARCHAR(50),
    @MinSalary DECIMAL(10,2),
    @MaxSalary DECIMAL(10,2)
AS
BEGIN
    SET NOCOUNT ON;

    SELECT EmployeeId, Name, Department, Salary
    FROM Employees
    WHERE Department = @Department
      AND Salary BETWEEN @MinSalary AND @MaxSalary
    ORDER BY Salary DESC;
END
GO

-- 呼叫方式
EXEC usp_SearchEmployees
    @Department = 'Engineering',
    @MinSalary = 50000,
    @MaxSalary = 100000;

輸出參數 OUTPUT

輸出參數用於從預存程序傳回資料：

CREATE PROCEDURE usp_GetEmployeeCount
    @Department NVARCHAR(50),
    @EmployeeCount INT OUTPUT,
    @AverageSalary DECIMAL(10,2) OUTPUT
AS
BEGIN
    SET NOCOUNT ON;

    SELECT
        @EmployeeCount = COUNT(*),
        @AverageSalary = AVG(Salary)
    FROM Employees
    WHERE Department = @Department;
END
GO

-- 呼叫並接收輸出值
DECLARE @Count INT, @AvgSalary DECIMAL(10,2);

EXEC usp_GetEmployeeCount
    @Department = 'Engineering',
    @EmployeeCount = @Count OUTPUT,
    @AverageSalary = @AvgSalary OUTPUT;

SELECT @Count AS EmployeeCount, @AvgSalary AS AverageSalary;

預設值設定

為參數設定預設值，使其成為可選參數：

CREATE PROCEDURE usp_GetEmployees
    @Department NVARCHAR(50) = NULL,      -- 可選，預設 NULL
    @Status NVARCHAR(20) = 'Active',      -- 可選，預設 Active
    @PageSize INT = 50,                   -- 可選，預設 50
    @PageNumber INT = 1                   -- 可選，預設 1
AS
BEGIN
    SET NOCOUNT ON;

    SELECT EmployeeId, Name, Department, Status
    FROM Employees
    WHERE (@Department IS NULL OR Department = @Department)
      AND Status = @Status
    ORDER BY EmployeeId
    OFFSET (@PageNumber - 1) * @PageSize ROWS
    FETCH NEXT @PageSize ROWS ONLY;
END
GO

-- 使用預設值呼叫
EXEC usp_GetEmployees;

-- 覆寫部分參數
EXEC usp_GetEmployees @Department = 'Sales', @PageSize = 100;

錯誤處理

完善的錯誤處理是企業級預存程序的關鍵要素。

TRY CATCH 區塊

SQL Server 提供結構化的錯誤處理機制：

CREATE PROCEDURE usp_TransferFunds
    @FromAccountId INT,
    @ToAccountId INT,
    @Amount DECIMAL(18,2)
AS
BEGIN
    SET NOCOUNT ON;

    BEGIN TRY
        BEGIN TRANSACTION;

        -- 扣款
        UPDATE Accounts
        SET Balance = Balance - @Amount
        WHERE AccountId = @FromAccountId;

        -- 檢查餘額
        IF (SELECT Balance FROM Accounts WHERE AccountId = @FromAccountId) < 0
        BEGIN
            THROW 50001, '餘額不足，無法完成轉帳。', 1;
        END

        -- 入款
        UPDATE Accounts
        SET Balance = Balance + @Amount
        WHERE AccountId = @ToAccountId;

        COMMIT TRANSACTION;

        SELECT 'SUCCESS' AS Status, '轉帳成功' AS Message;
    END TRY
    BEGIN CATCH
        IF @@TRANCOUNT > 0
            ROLLBACK TRANSACTION;

        SELECT
            'ERROR' AS Status,
            ERROR_MESSAGE() AS Message,
            ERROR_NUMBER() AS ErrorNumber,
            ERROR_LINE() AS ErrorLine;
    END CATCH
END
GO

錯誤資訊函數

在 CATCH 區塊中可使用以下函數取得錯誤詳情：

函數說明
ERROR_NUMBER()錯誤代碼
ERROR_MESSAGE()錯誤訊息
ERROR_SEVERITY()嚴重性等級
ERROR_STATE()錯誤狀態
ERROR_LINE()發生錯誤的行號
ERROR_PROCEDURE()發生錯誤的程序名稱

RAISERROR vs THROW

兩種拋出錯誤的方式比較：

-- RAISERROR（較舊的方式）
RAISERROR('自訂錯誤訊息', 16, 1);

-- THROW（SQL Server 2012+ 建議使用）
THROW 50001, '自訂錯誤訊息', 1;

差異比較：

特性RAISERRORTHROW
版本支援所有版本SQL Server 2012+
語法需指定嚴重性較簡潔
結束語句不需分號前面需要分號
重新拋出需重新建構直接 THROW;

交易回滾最佳實踐

CREATE PROCEDURE usp_ProcessOrder
    @OrderId INT,
    @Result NVARCHAR(100) OUTPUT
AS
BEGIN
    SET NOCOUNT ON;
    SET XACT_ABORT ON;  -- 錯誤時自動回滾

    BEGIN TRY
        BEGIN TRANSACTION;

        -- 更新訂單狀態
        UPDATE Orders SET Status = 'Processing' WHERE OrderId = @OrderId;

        -- 扣減庫存
        UPDATE Inventory
        SET Quantity = Quantity - oi.Quantity
        FROM Inventory i
        JOIN OrderItems oi ON i.ProductId = oi.ProductId
        WHERE oi.OrderId = @OrderId;

        -- 記錄交易
        INSERT INTO TransactionLog (OrderId, Action, Timestamp)
        VALUES (@OrderId, 'Order Processed', GETDATE());

        COMMIT TRANSACTION;
        SET @Result = 'SUCCESS';
    END TRY
    BEGIN CATCH
        IF @@TRANCOUNT > 0
            ROLLBACK TRANSACTION;

        SET @Result = 'ERROR: ' + ERROR_MESSAGE();

        -- 記錄錯誤日誌
        INSERT INTO ErrorLog (ProcedureName, ErrorMessage, Timestamp)
        VALUES ('usp_ProcessOrder', ERROR_MESSAGE(), GETDATE());
    END CATCH
END
GO

效能優化建議

預存程序的效能優化對系統整體效能至關重要。

SET NOCOUNT ON

每個預存程序開頭都應加入此設定：

CREATE PROCEDURE usp_Example
AS
BEGIN
    SET NOCOUNT ON;  -- 不回傳受影響的資料列數

    -- 您的 SQL 邏輯
END

效益：減少網路流量，避免傳送不必要的 "xx rows affected" 訊息。

避免參數嗅探問題

參數嗅探（Parameter Sniffing）可能導致執行計畫不適用於某些參數值：

-- 問題情境：第一次執行時參數值影響後續所有執行
CREATE PROCEDURE usp_GetOrdersByDate
    @StartDate DATE,
    @EndDate DATE
AS
BEGIN
    SET NOCOUNT ON;

    -- 方法一：使用區域變數
    DECLARE @LocalStart DATE = @StartDate;
    DECLARE @LocalEnd DATE = @EndDate;

    SELECT OrderId, CustomerId, OrderDate, TotalAmount
    FROM Orders
    WHERE OrderDate BETWEEN @LocalStart AND @LocalEnd;
END
GO

-- 方法二：使用 OPTION (RECOMPILE)
CREATE PROCEDURE usp_GetOrdersByDate_V2
    @StartDate DATE,
    @EndDate DATE
AS
BEGIN
    SET NOCOUNT ON;

    SELECT OrderId, CustomerId, OrderDate, TotalAmount
    FROM Orders
    WHERE OrderDate BETWEEN @StartDate AND @EndDate
    OPTION (RECOMPILE);  -- 每次重新編譯
END
GO

適當使用暫存表

處理大量資料時，暫存表可提升效能：

CREATE PROCEDURE usp_GenerateReport
    @Year INT
AS
BEGIN
    SET NOCOUNT ON;

    -- 使用暫存表儲存中間結果
    CREATE TABLE #MonthlySales (
        Month INT,
        TotalSales DECIMAL(18,2),
        OrderCount INT
    );

    -- 填入資料
    INSERT INTO #MonthlySales
    SELECT
        MONTH(OrderDate),
        SUM(TotalAmount),
        COUNT(*)
    FROM Orders
    WHERE YEAR(OrderDate) = @Year
    GROUP BY MONTH(OrderDate);

    -- 使用暫存表進行後續計算
    SELECT
        m.Month,
        m.TotalSales,
        m.OrderCount,
        m.TotalSales / NULLIF(m.OrderCount, 0) AS AvgOrderValue
    FROM #MonthlySales m
    ORDER BY m.Month;

    -- 暫存表會在程序結束時自動刪除
END
GO

其他效能建議

  1. 避免在 WHERE 子句中對欄位使用函數

    -- 差：無法使用索引
WHERE YEAR(OrderDate) = 2024

-- 好：可使用索引
WHERE OrderDate >= '2024-01-01' AND OrderDate < '2025-01-01'

  2. 使用 EXISTS 取代 IN（處理大型子查詢時）

  3. 限制回傳欄位（避免 SELECT *）

更多效能優化技巧，請參考 SQL 效能優化完整指南。

實戰範例：報表產生器

以下是一個完整的報表產生器預存程序範例：

CREATE PROCEDURE usp_GenerateSalesReport
    @StartDate DATE,
    @EndDate DATE,
    @Department NVARCHAR(50) = NULL,
    @MinAmount DECIMAL(18,2) = 0,
    @TotalSales DECIMAL(18,2) OUTPUT,
    @TotalOrders INT OUTPUT
AS
BEGIN
    SET NOCOUNT ON;

    BEGIN TRY
        -- 驗證參數
        IF @StartDate > @EndDate
        BEGIN
            THROW 50001, '開始日期不能大於結束日期', 1;
        END

        -- 建立暫存表儲存報表資料
        CREATE TABLE #ReportData (
            OrderId INT,
            OrderDate DATE,
            CustomerName NVARCHAR(100),
            Department NVARCHAR(50),
            TotalAmount DECIMAL(18,2),
            ItemCount INT
        );

        -- 填入報表資料
        INSERT INTO #ReportData
        SELECT
            o.OrderId,
            o.OrderDate,
            c.CustomerName,
            e.Department,
            o.TotalAmount,
            (SELECT COUNT(*) FROM OrderItems WHERE OrderId = o.OrderId)
        FROM Orders o
        JOIN Customers c ON o.CustomerId = c.CustomerId
        JOIN Employees e ON o.SalesRepId = e.EmployeeId
        WHERE o.OrderDate BETWEEN @StartDate AND @EndDate
          AND o.TotalAmount >= @MinAmount
          AND (@Department IS NULL OR e.Department = @Department);

        -- 計算統計值
        SELECT
            @TotalSales = ISNULL(SUM(TotalAmount), 0),
            @TotalOrders = COUNT(*)
        FROM #ReportData;

        -- 回傳明細資料
        SELECT
            OrderId,
            OrderDate,
            CustomerName,
            Department,
            TotalAmount,
            ItemCount
        FROM #ReportData
        ORDER BY OrderDate DESC, TotalAmount DESC;

        -- 回傳部門彙總
        SELECT
            Department,
            COUNT(*) AS OrderCount,
            SUM(TotalAmount) AS DepartmentTotal,
            AVG(TotalAmount) AS AvgOrderValue
        FROM #ReportData
        GROUP BY Department
        ORDER BY DepartmentTotal DESC;

    END TRY
    BEGIN CATCH
        SET @TotalSales = 0;
        SET @TotalOrders = 0;

        SELECT
            'ERROR' AS Status,
            ERROR_MESSAGE() AS Message;
    END CATCH
END
GO

-- 使用範例
DECLARE @Sales DECIMAL(18,2), @Orders INT;

EXEC usp_GenerateSalesReport
    @StartDate = '2024-01-01',
    @EndDate = '2024-12-31',
    @Department = 'Sales',
    @MinAmount = 1000,
    @TotalSales = @Sales OUTPUT,
    @TotalOrders = @Orders OUTPUT;

SELECT @Sales AS TotalSales, @Orders AS TotalOrders;

常見問題與除錯

除錯技巧

  1. 使用 PRINT 輸出偵錯訊息

    PRINT '目前處理的 OrderId: ' + CAST(@OrderId AS VARCHAR(10));

  2. 檢視執行計畫

    SET STATISTICS IO ON;
SET STATISTICS TIME ON;
EXEC usp_YourProcedure @Param = 'value';

  3. 使用 SQL Server Profiler 追蹤

常見錯誤排解

錯誤可能原因解決方法
權限不足缺少 EXECUTE 權限GRANT EXECUTE ON usp_xxx TO UserRole
參數型別不符傳入值與宣告型別不符檢查參數資料型別
物件不存在資料表或欄位名稱錯誤確認物件名稱與結構
交易未關閉TRY/CATCH 未正確處理確保 ROLLBACK 在 CATCH 中執行

結論

預存程序是 SQL Server 開發中不可或缺的工具，善用預存程序可以：

  1. ✅ 提升應用程式效能
  2. ✅ 增強資料庫安全性
  3. ✅ 簡化程式碼維護
  4. ✅ 實作複雜的商業邏輯
  5. ✅ 確保交易一致性

掌握本文介紹的語法、參數使用、錯誤處理與效能優化技巧，您將能夠開發出高品質的企業級預存程序。

想進一步學習進階查詢技巧？請參考 SQL Window Functions 教學。

CloudInsight 文末 CTA

想提升團隊的 SQL 開發能力？

CloudInsight 提供專業的進階 SQL 開發培訓課程，課程內容包含：

  • 預存程序設計模式與最佳實踐
  • 進階錯誤處理與交易控制
  • 效能調校與執行計畫分析
  • 實戰專案演練與程式碼審查

了解企業培訓課程方案 →

常見問題 FAQ

預存程序和函數有什麼差別？

預存程序（Stored Procedure）和使用者定義函數（User-Defined Function）的主要差異：

特性預存程序函數
回傳值可選，透過 OUTPUT 參數或結果集必須回傳值
在 SELECT 中使用不可以可以（純量函數）
修改資料可以（INSERT/UPDATE/DELETE）不可以
交易控制可以使用不可以
錯誤處理TRY CATCH 完整支援限制較多

選擇建議：需要修改資料或執行複雜邏輯時使用預存程序；需要在查詢中計算值時使用函數。

預存程序會自動使用索引嗎？

預存程序本身不會「建立」索引，但會自動使用資料表上已存在的適當索引。SQL Server 的查詢優化器會在編譯預存程序時，根據統計資料選擇最佳的執行計畫（包含索引使用策略）。

確保索引被使用的技巧：

  1. 確保相關欄位有建立索引
  2. 避免對索引欄位使用函數（如 WHERE YEAR(Date) = 2024）
  3. 定期更新統計資料（UPDATE STATISTICS）
  4. 使用 SSMS 檢視執行計畫確認索引使用狀況

延伸閱讀

參考資料

  1. Microsoft SQL Server 官方文件 - CREATE PROCEDURE
  2. SQL Server 預存程序最佳實踐指南
  3. SQL Server 錯誤處理模式

插圖：預存程序執行流程圖

場景描述： 流程圖展示「應用程式呼叫 → 預存程序執行 → 資料庫操作 → 回傳結果」的完整流程，強調預編譯與執行計畫快取的優勢

視覺重點：

  • 主要內容清晰呈現

必須出現的元素：

  • 依據描述中的關鍵元素

需要顯示的中文字： 無

顏色調性： 專業、清晰

避免元素： 抽象圖形、齒輪、發光特效

Slug: stored-procedure-execution-flow

需要專業的雲端建議？

無論您正在評估雲平台、優化現有架構，或尋找節費方案，我們都能提供協助

預約免費諮詢

相關文章

SQL

雲端 SQL 資料庫比較：Azure SQL、Cloud SQL、AWS RDS 完整評比【2025】

全面比較三大雲端 SQL 資料庫服務：Azure SQL Database、Google Cloud SQL、AWS RDS。涵蓋功能特色、高可用性、定價模式、遷移策略，幫助企業選擇最適合的雲端資料庫方案。

SQL

SQL CRUD 操作完整指南：INSERT、UPDATE、DELETE 語法教學【2025】

系統性介紹 SQL 資料操作語言（DML）核心指令。涵蓋 INSERT 新增、UPDATE 更新、DELETE 刪除的完整語法，以及 TRUNCATE 差異比較、交易控制、常見錯誤提醒。

SQL

SQL 函數完整指南：字串、日期、數學、聚合函數總整理【2025】

SQL 內建函數完整參考手冊。涵蓋字串函數（CONCAT、SUBSTRING、REPLACE）、日期函數（DATEADD、DATEDIFF）、數學函數（ROUND、CEILING）、聚合函數（COUNT、SUM、AVG）的語法與範例。