共用方式為

執行預存程序

  • 發行項

本主題描述如何使用 SQL Server Management Studio 或 Transact-SQL，在 SQL Server 2014 中執行預存程式。

有兩種不同的方法可執行預存程序。 第一種是最常用的方法，可讓應用程式或使用者呼叫程序。 第二種方法是設定讓程序在 SQL Server 執行個體啟動時自動執行。 當應用程式或使用者呼叫程序時，Transact-SQL EXECUTE 或 EXEC 關鍵字會在呼叫中明確陳述。 或者，如果程式是 Transact-SQL 批次中的第一個語句，則可以呼叫並執行程式，而不使用 關鍵字。

本主題內容

開始之前

限制事項

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

    EXEC SP_heLP; -- Will fail to resolve because SP_heLP does not equal sp_help

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

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

建議

  • 執行系統預存程序

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

    EXEC sys.sp_who;

  • 執行使用者自訂預存程序

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

    下列範例示範執行使用者定義程序的建議方法。 您會發現程序接受一個輸入參數。 如需指定輸入和輸出參數的相關資訊，請參閱 指定參數。

    USE AdventureWorks2012;  
GO  
EXEC dbo.uspGetEmployeeManagers @BusinessEntityID = 50;

    -或-

    EXEC AdventureWorks2012.dbo.uspGetEmployeeManagers 50;  
GO

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

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

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

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

  • 自動執行預存程序

    每次SQL Server啟動時，都會執行標示為自動執行的程式，並在該啟動程式期間復原master資料庫。 設定自動執行程序在執行資料庫維護作業或讓程序做為背景處理序連續執行時相當實用。 自動執行的另一個用處就是讓程序執行 tempdb中的系統或維護工作，如建立全域的暫存資料表。 這可確保在啟動SQL Server期間重新建立tempdb時，一律會存在這類臨時表。

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

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

    提示

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

  • 設定、清除和控制自動執行

    只有系統管理員 (sa) 可以將程序標示為自動執行。 此外，該程序必須位於 master 資料庫中，由 sa所擁有，並且不能有輸入或輸出參數。

    使用 sp_procoption 可以：

    1. 指定一個現有的程序做為啟動程序。

    2. 停止某個程序在 SQL Server 啟動時執行。

安全性

如需詳細資訊，請參閱 EXECUTE AS (Transact-SQL) 和 EXECUTE AS 子句 (Transact-SQL)。

權限

如需詳細資訊，請參閱 EXECUTE (Transact-SQL) 中＜權限＞區段。

使用 SQL Server Management Studio

若要執行預存程序

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

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

  3. 以滑鼠右鍵按一下您要的使用者定義預存程序，然後按一下 [執行預存程序]。

  4. 在 [執行程序] 對話方塊中，指定每個參數的值，以及是否應傳遞 null 值。

    參數
    指出參數的名稱。

    資料類型
    指出參數的資料類型。

    輸出參數
    指出這是否為輸出參數。

    傳遞 Null 值
    傳遞 NULL 作為參數的值。

    ReplTest1
    呼叫程序時輸入參數的值。

  5. 若要執行預存程序，請按一下 [確定] 。

使用 TRANSACT-SQL

若要執行預存程序

  1. 連線至資料庫引擎。

  2. 在標準列中，按一下 [新增查詢] 。

  3. 複製下列範例並將其貼到查詢視窗中，然後按一下 [執行] 。 此範例示範如何執行需要一個參數的預存程序。 此範例會 uspGetEmployeeManagers 使用指定為 @EmployeeID 參數的值 6 來執行預存程式。

USE AdventureWorks2012;  
GO  
EXEC dbo.uspGetEmployeeManagers 6;  
GO

若要設定或清除自動執行的程序

  1. 連線至資料庫引擎。

  2. 在標準列中，按一下 [新增查詢] 。

  3. 複製下列範例並將其貼到查詢視窗中，然後按一下 [執行] 。 此範例示範如何使用 sp_procoption 設定自動執行程序。

USE AdventureWorks2012;  
GO  
EXEC sp_procoption @ProcName = '<procedure name>'   
    , @OptionName = ] 'startup'   
    , @OptionValue = 'on';

若要停止自動執行程序

  1. 連線至資料庫引擎。

  2. 在標準列中，按一下 [新增查詢] 。

  3. 複製下列範例並將其貼到查詢視窗中，然後按一下 [執行] 。 此範例示範如何使用 sp_procoption 設定停止自動執行程序。

USE AdventureWorks2012;  
GO  
EXEC sp_procoption @ProcName = '<procedure name>'   
    , @OptionValue = 'off';

範例 (Transact-SQL)

另請參閱

指定參數
設定 scan for startup procs 伺服器組態選項
EXECUTE (Transact-SQL)
CREATE PROCEDURE (Transact-SQL)
預存程序 (Database Engine)