SQL Server 的預存程序（Stored Procedure）是一種在數據庫中預先定義並儲存的 SQL 語句集。它們被用來封裝一組操作，可以被多次調用，並可以接受參數來執行不同的操作。預存程序有助於提高數據庫操作的效率、可重用性和安全性。

預存程序的基本概念

1. 預存程序 (Stored Procedure)：
   • 定義：預存程序是一段存儲在數據庫中的 SQL 程序碼，它可以包含一系列 SQL 語句和控制流邏輯（如條件判斷和循環）。
   • 用途：用於封裝複雜的業務邏輯，執行常見的數據庫操作，提高重用性和維護性。
   • 參數：預存程序可以接受輸入參數、輸出參數和返回值參數，用來靈活控制其行為。

預存程序的特色和優點

• 重用性：一旦創建，預存程序可以在多個應用程序或查詢中重用，避免重複編寫相同的 SQL 語句。
• 性能：由於預存程序在數據庫中已經預編譯，執行時可以更快，減少了解析和編譯的開銷。
• 安全性：可以通過授權來控制對預存程序的訪問權限，保護底層數據表不被直接訪問。
• 維護：修改預存程序比修改多個應用程序中的 SQL 語句更容易，有助於集中管理業務邏輯。

預存程序的種類

1. 標準預存程序：

   • 定義了常見的數據操作，如查詢、插入、更新、刪除等。

   • 範例：

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

2. 系統預存程序：

   • SQL Server 提供的一些內建預存程序，用於系統管理和維護，如創建、修改和刪除數據庫對象。

   • 範例：

     EXEC sp_help 'Employees'; -- 顯示 Employees 表的結構和相關信息
     
     

3. 複雜預存程序：

   • 包含業務邏輯、條件判斷、循環等複雜控制流程的預存程序。

   • 範例：

     CREATE PROCEDURE UpdateEmployeeSalary
         @EmployeeID INT,
         @NewSalary DECIMAL(10, 2)
     AS
     BEGIN
         IF EXISTS (SELECT 1 FROM Employees WHERE EmployeeID = @EmployeeID)
         BEGIN
             UPDATE Employees
             SET Salary = @NewSalary
             WHERE EmployeeID = @EmployeeID;
         END
         ELSE
         BEGIN
             RAISERROR ('Employee not found', 16, 1);
         END
     END;
     
     

如何使用預存程序

1. 創建預存程序： 使用 CREATE PROCEDURE 語句來創建預存程序。

   CREATE PROCEDURE ProcedureName
       @Parameter1 DataType,
       @Parameter2 DataType
   AS
   BEGIN
       -- SQL 語句
   END;
   
   

2. 執行預存程序： 使用 EXEC 或 EXECUTE 語句來調用預存程序。

   EXEC ProcedureName @Parameter1 = Value1, @Parameter2 = Value2;
   
   

3. 修改預存程序： 使用 ALTER PROCEDURE 語句來修改已存在的預存程序。

   ALTER PROCEDURE ProcedureName
       @Parameter1 DataType
   AS
   BEGIN
       -- 修改後的 SQL 語句
   END;
   
   

4. 刪除預存程序： 使用 DROP PROCEDURE 語句來刪除預存程序。

   DROP PROCEDURE ProcedureName;
   
   

使用者自訂預存程序

• 創建和管理：使用者可以根據需求創建和管理自己的預存程序，將業務邏輯封裝在數據庫層中。
• 測試和調試：可以在開發和測試環境中測試預存程序的功能，確保其按預期工作。