SQL Server 存儲過程完整入門指南

| SQL | 3 Reads

Copyright Notice: This article is an original work licensed under the CC 4.0 BY-NC-ND license. If you wish to repost this article, please include the original source link and this copyright notice.
Source link: https://v2know.com/article/1276

在企業級應用中，**存儲過程（Stored Procedure）**是一個非常常見的工具。它能將多條 SQL 語句封裝成一個可重複呼叫的單位，支援參數、輸出值與交易控制，大大提高開發效率與程式的可維護性。本文將以 SQL Server 為例，帶你一步步了解存儲過程的語法與實戰技巧。

1. 存儲過程的基本語法

CREATE PROCEDURE procedure_name
    @param1 datatype = default_value,   -- 輸入參數，可有預設值
    @param2 datatype OUTPUT             -- 輸出參數（可選）
AS
BEGIN
    -- SQL邏輯
END

呼叫方式：

EXEC procedure_name @param1 = 值, @param2 = 值 OUTPUT;

2. 基本範例

範例 1：不帶參數

CREATE PROCEDURE GetAllEmployees
AS
BEGIN
    SELECT * FROM Employees;
END

呼叫：

EXEC GetAllEmployees;

範例 2：帶輸入參數

CREATE PROCEDURE GetEmployeeById
    @EmployeeId INT
AS
BEGIN
    SELECT * FROM Employees
    WHERE EmployeeID = @EmployeeId;
END

呼叫：

EXEC GetEmployeeById @EmployeeId = 101;

範例 3：帶輸出參數（重點）

CREATE PROCEDURE GetEmployeeName
    @EmployeeId INT,
    @EmployeeName NVARCHAR(100) OUTPUT
AS
BEGIN
    SELECT @EmployeeName = Name
    FROM Employees
    WHERE EmployeeID = @EmployeeId;
END

呼叫：

DECLARE @Name NVARCHAR(100);
EXEC GetEmployeeName @EmployeeId = 101, @EmployeeName = @Name OUTPUT;
PRINT @Name;

👉 關鍵點：
這裡的 SELECT @EmployeeName = Name FROM Employees ... 是 SQL Server 的一種特殊語法，可以 同時查詢並將欄位值賦給變數。
在傳統程式語言中，必須先查詢再賦值，而在 T-SQL 中一步就能完成，非常方便。

範例 4：帶交易控制

CREATE PROCEDURE UpdateEmployeeSalary
    @EmployeeId INT,
    @NewSalary DECIMAL(10,2)
AS
BEGIN
    BEGIN TRY
        BEGIN TRANSACTION;

        UPDATE Employees
        SET Salary = @NewSalary
        WHERE EmployeeID = @EmployeeId;

        COMMIT TRANSACTION;
    END TRY
    BEGIN CATCH
        ROLLBACK TRANSACTION;
        PRINT '錯誤: ' + ERROR_MESSAGE();
    END CATCH
END

這樣能保證更新失敗時，自動回滾。

3. 變數賦值的三種常見寫法

在存儲過程中，常常需要把查詢結果存入變數，有三種寫法：

  1. 直接 SELECT 賦值（簡便）

    SELECT @var = column
FROM Table
WHERE Id = 1;

  2. 使用 SET（更直觀）

    SET @var = (SELECT column FROM Table WHERE Id = 1);

  3. 多筆查詢時的注意

    • 如果 SELECT 回傳多筆，SQL Server 會取最後一筆的值。

    • 如果沒有符合條件，變數保持 NULL。

4. 單筆 vs 多筆處理

  • 單筆記錄 → 用變數存值

    SELECT @Name = Name
FROM Employees
WHERE EmployeeID = 101;

  • 多筆記錄 → 直接回傳結果集

    SELECT Name
FROM Employees
WHERE DepartmentId = 5;

  • 更進階 → 使用游標 (Cursor)（少用，效能較低）

    DECLARE cur CURSOR FOR
SELECT Name FROM Employees;

OPEN cur;
FETCH NEXT FROM cur INTO @Name;
WHILE @@FETCH_STATUS = 0
BEGIN
    PRINT @Name;
    FETCH NEXT FROM cur INTO @Name;
END
CLOSE cur;
DEALLOCATE cur;

5. 注意事項

  1. 參數必須帶 @ 符號

  2. 輸出參數必須明確標記 OUTPUT

  3. 除錯建議用 PRINT 或 SELECT

  4. 多筆查詢時小心變數只取最後一筆

  5. 複雜邏輯建議加上交易控制

6. 總結

  • 存儲過程能封裝 SQL，方便重複使用。

  • 輸入參數處理查詢條件，輸出參數用於回傳單值。

  • SELECT @var = column 是 SQL Server 的特有簡便寫法，可以同時查詢並賦值。

  • 單筆查詢用變數，多筆查詢直接 SELECT 回傳即可。

  • 複雜邏輯務必加交易控制以保證資料一致性。

💡 延伸建議：
如果你正在開發後台管理系統，可以把 分頁查詢（PageIndex, PageSize） 寫成存儲過程，避免在應用程式中寫複雜 SQL，既能保證效能，也方便後期維護。

This article was last edited at