執行預存程序

適用於：SQL Server Azure SQL 資料庫 Azure SQL 受控執行個體 Azure Synapse Analytics Analytics Platform System (PDW)

本文描述如何使用 SQL Server Management Studio 或 Transact-SQL，在 SQL Server 中執行預存程序。

有幾種不同的方法可執行預存程序。 第一種是最常用的方法，可讓應用程式或使用者呼叫程序。 另一種方法是設定讓預存程序在 SQL Server 執行個體啟動時自動執行。

當應用程式或使用者呼叫程序時，Transact-SQL EXECUTE 或 EXEC 關鍵字會在呼叫中明確陳述。 如果程序是 Transact-SQL 批次中的第一個陳述式，則呼叫和執行程序時可以不用 EXEC 關鍵字。

限制事項

比對系統程序名稱時，會使用呼叫資料庫定序。 因此，您務必在程序呼叫中使用大小寫完全相符的系統程序名稱。 例如，此程式碼如果在有區分大小寫定序的資料庫內容中執行將會失敗：

EXEC SP_heLP; -- Fails to resolve because SP_heLP doesn't equal sp_help  

若要顯示完全相符的系統程序名稱，請查詢 sys.system_objects 和 sys.system_parameters 目錄檢視。

如果使用者定義程序與系統程序的名稱相同，則使用者定義程序可能永遠不會執行。

建議

使用下列建議來執行預存程序。

系統預存程序

系統程序會以 sp_ 前置詞開頭。 由於系統程序會以邏輯的方式出現在所有使用者與系統定義的資料庫中，因此可以從任何資料庫中執行，而不必完整限定程序名稱。 不過，最好使用結構描述將所有系統程序名稱限定為 sys 結構描述名稱，以避免名稱衝突。 下列範例說明呼叫系統程序的建議方法。

EXEC sys.sp_who;  

使用者定義的預存程序

當執行使用者定義的程序時，最好以結構描述名稱限定程序名稱。 這種作法可稍微提升效能，因為資料庫引擎不必搜尋多個結構描述。 如果資料庫在多個結構描述中有相同名稱的程序，使用結構描述名稱還可以避免執行錯誤的程序。

下列範例示範執行使用者定義程序的建議方法。 此程序接受兩個輸入參數。 如需指定輸入和輸出參數的相關資訊，請參閱在預存程序中指定參數。

EXECUTE SalesLT.uspGetCustomerCompany @LastName = N'Cannon', @FirstName = N'Chris';
GO

或：

EXEC SalesLT.uspGetCustomerCompany N'Cannon', N'Chris';
GO  

如果指定的是非限定使用者定義程序，則資料庫引擎會以下列順序搜尋該程序：

1. 目前資料庫的 sys 結構描述。

2. 如果程序在批次或動態 SQL 中執行，則為呼叫端的預設結構描述。 如果非限定程序名稱出現在其他程序定義的主體內，則接下來會掃描包含這個其他程序的結構描述。

3. 目前資料庫中的 dbo 結構描述。

安全性

如需安全性資訊，請參閱 EXECUTE AS (Transact-SQL) 和 EXECUTE AS 子句 (Transact-SQL)。

權限

如需權限資訊，請參閱 EXECUTE (Transact-SQL) 中的 權限。

預存程序執行

您可以使用 SQL Server Management Studio (SSMS) 使用者介面或 SSMS 查詢視窗中的 Transact-SQL 來執行預存程序。 請務必使用最新版本的 SSMS。

使用 SQL Server Management Studio

1. 在 [物件總管] 中，連線到 SQL Server 或 Azure SQL 資料庫的執行個體，展開該執行個體，然後展開 [資料庫]。

2. 依序展開您要的資料庫、[可程式性] 和 [預存程序]。

3. 以滑鼠右鍵按一下您要執行的預存程序，然後選取 [執行預存程序]。

4. 在 [執行程序] 對話框中，[參數] 指示每個參數的名稱，[資料類型] 指示其資料類型，[輸出參數] 指示其是否為輸出參數。

   對於每個參數：

   • 在 [值] 底下，輸入要用於該參數的值。
   • 在 [傳遞 Null 值] 底下，選取是否要傳遞 NULL 做為該參數的值。

5. 按一下 [確定] 以執行預存程序。 如果預存程序沒有任何參數，只需按一下 [確定]。

   預存程序隨即執行，結果會出現在 [結果] 窗格中。

   例如，若要執行 [建立預存程序] 文章中的 SalesLT.uspGetCustomerCompany 預存程序，請為 @LastName 參數輸入 Cannon，併為 @FirstName 參數輸入 Chris，然後按一下 [確定]。 程序傳回 FirstName Chris、LastName Cannon 和 CompanyName Outdoor Sporting Goods。

在查詢視窗中使用 Transact-SQL

1. 在 SSMS 中，連線到 SQL Server 或 Azure SQL 資料庫的執行個體。

2. 從工具列中選取 [新增查詢]。

3. 在查詢視窗中輸入具有下列語法的 EXECUTE 陳述式，以提供所有預期參數的值：

   EXECUTE <ProcedureName> N'<Parameter 1 value>, N'<Parameter x value>;  
   GO  
   

   例如，下列 Transact-SQL 陳述式執行 uspGetCustomerCompany 預存程序，並以 Cannon 做為 @LastName 參數值，以 Chris 做為 @FirstName 參數值：

   EXEC SalesLT.uspGetCustomerCompany N'Cannon', N'Chris';
   GO  
   

4. 從工具列中選取 [執行]。 預存程序隨即執行。

參數值的選項

有多種方式可在預存程序 EXECUTE 陳述式中提供參數和值。 下列範例顯示 EXECUTE 陳述式的數個不同選項。

• 如果您提供參數值的順序與預存程序中定義參數值的順序相同，則不需要說明參數名稱。 例如：

  EXEC SalesLT.uspGetCustomerCompany N'Cannon', N'Chris';
  

• 如果您在 @parameter_name=value 模式中提供參數名稱，則指定參數名稱和值的順序不必與定義時的順序相同。 例如，下列任一陳述式都是有效的：

  EXEC SalesLT.uspGetCustomerCompany @FirstName = N'Chris', @LastName = N'Cannon';
  

  or：

  EXEC SalesLT.uspGetCustomerCompany @LastName = N'Cannon', @FirstName = N'Chris';
  

• 如果您針對任何參數使用 @parameter_name=value 形式，則必須為該陳述式中的所有後續參數使用該形式。 例如，您無法使用 EXEC SalesLT.uspGetCustomerCompany1 @FirstName = N'Chris', N'Cannon';。

啟動時自動執行

適用於：SQL Server

在 SQL Server 中，sysadmin 伺服器角色的成員可以使用 sp_procoption 來設定或清除啟動時自動執行的程序。 啟動程序必須在 master 資料庫中，必須為 sa 所有，而且不能有輸入或輸出參數。 如需詳細資訊，請參閱 sp_procoption (Transact-SQL)。

標記為啟動時自動執行的程序會在每次 SQL Server 啟動時執行，且 master 資料庫會在該啟動處理序期間復原。 設定自動執行程序在執行資料庫維護作業或讓程序做為背景處理序連續執行時相當實用。

自動執行的另一個用處就是讓程序執行 tempdb中的系統或維護工作，如建立全域的暫存資料表。 自動執行可確保在 SQL Server 啟動期間重新建立 tempdb 時，這個暫存資料表一定存在。

自動執行的程序運作時所使用的權限與 sysadmin 固定伺服器角色的成員相同。 程序所產生的任何錯誤訊息都會寫入 SQL Server 錯誤記錄檔中。

對於您能擁有的啟動程序數量並沒有限制，但每一啟動程序執行時都會佔用掉一個工作者執行緒。 如果您在啟動時需要執行多個程序，但並不需要同時執行它們，那麼您可以讓某一個程序成為啟動程序並且讓它叫用其他的程序。 這種方法只會使用一個工作者執行緒。

提示

請不要從自動執行的程序傳回任何結果集。 因為這類程序是由 SQL Server 而非應用程式或使用者執行，所以結果集會無處可去。

注意

Azure SQL 資料庫的設計目的是讓功能不須依賴 master 資料庫。 因此，設定伺服器層級選項的 Transact-SQL 陳述式無法在 Azure SQL 中使用。 通常可以從其他 Azure 服務找到適當的替代方案，例如彈性工作 或 Azure 自動化。

設定程序在啟動時自動執行

只有系統管理員 (sa) 可以將程序標示為自動執行。

1. 在 SSMS 中，連線到資料庫引擎。

2. 在標準工具列上，選取 [新增查詢]。

3. 輸入下列 sp_procoption 命令，將預存程序設定為在 SQL Server 啟動時自動執行。

   EXEC sp_procoption @ProcName = N'<stored procedure name>'   
       , @OptionName = 'startup'   
       , @OptionValue = 'on';
   GO
   

4. 在工具列中，選取 [執行]。

停止程序在啟動時自動執行

sysadmin 可以使用 sp_procoption 來停止程序在 SQL Server 啟動時自動執行。

1. 在 SSMS 中，連線到資料庫引擎。

2. 在標準工具列上，選取 [新增查詢]。

3. 在查詢視窗中，輸入下列命令︰

   EXEC sp_procoption @ProcName = N'<stored procedure name>'      
       , @OptionName = 'startup'
       , @OptionValue = 'off';
   GO
   

4. 在工具列中，選取 [執行]。

相關內容

• 預存程序 (Database Engine)
• EXECUTE (Transact-SQL)
• 建立預存程序
• CREATE PROCEDURE (Transact-SQL)
• 在預存程序中指定參數
• sp_procoption (Transact-SQL)
• 設定啟動處理器的掃描 (伺服器組態選項)