CREATE PROCEDURE (Transact-SQL)

發行項
01/25/2023

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

在 SQL Server、Azure SQL 資料庫和分析平臺系統 (PDW) 中建立 Transact-SQL 或 Common Language Runtime (CLR) 預存程式。預存程序類似於其他程式設計語言中的程序，這些程序可以：

接受輸入參數，並以輸出參數的形式將多個數值傳回呼叫程序或批次處理。
包含可在資料庫中執行作業的程式陳述式，包括呼叫其他程序。
將狀態值傳回呼叫程序或批次處理，以指示成功或失敗 (及失敗原因)。

使用此語句在目前資料庫中建立永久程式，或資料庫中的暫存程式 tempdb 。

注意

本主題將討論如何將 .NET Framework CLR 整合至 SQL Server。 CLR 整合不適用於 Azure SQL Database。

若要略過語法詳細資料，並了解基本預存程序的簡單範例，可跳到簡單範例。

Transact-SQL 語法慣例

Syntax

SQL Server 和 Azure SQL 資料庫中預存程式的 Transact-SQL 語法：

CREATE [ OR ALTER ] { PROC | PROCEDURE }
    [schema_name.] procedure_name [ ; number ]
    [ { @parameter_name [ type_schema_name. ] data_type }
        [ VARYING ] [ NULL ] [ = default ] [ OUT | OUTPUT | [READONLY]
    ] [ ,...n ]
[ WITH <procedure_option> [ ,...n ] ]
[ FOR REPLICATION ]
AS { [ BEGIN ] sql_statement [;] [ ...n ] [ END ] }
[;]

<procedure_option> ::=
    [ ENCRYPTION ]
    [ RECOMPILE ]
    [ EXECUTE AS Clause ]

CLR 預存程式的 Transact-SQL 語法：

CREATE [ OR ALTER ] { PROC | PROCEDURE }
    [schema_name.] procedure_name [ ; number ]
    [ { @parameter_name [ type_schema_name. ] data_type }
        [ = default ] [ OUT | OUTPUT ] [READONLY]
    ] [ ,...n ]
[ WITH EXECUTE AS Clause ]
AS { EXTERNAL NAME assembly_name.class_name.method_name }
[;]

原生編譯預存程式的 Transact-SQL 語法：

CREATE [ OR ALTER ] { PROC | PROCEDURE } [schema_name.] procedure_name
    [ { @parameter data_type } [ NULL | NOT NULL ] [ = default ]
        [ OUT | OUTPUT ] [READONLY]
    ] [ ,... n ]
  WITH NATIVE_COMPILATION, SCHEMABINDING [ , EXECUTE AS clause ]
AS
{
  BEGIN ATOMIC WITH ( <set_option> [ ,... n ] )
sql_statement [;] [ ... n ]
[ END ]
}
[;]

<set_option> ::=
    LANGUAGE = [ N ] 'language'
  | TRANSACTION ISOLATION LEVEL = { SNAPSHOT | REPEATABLE READ | SERIALIZABLE }
  | [ DATEFIRST = number ]
  | [ DATEFORMAT = format ]
  | [ DELAYED_DURABILITY = { OFF | ON } ]

Azure Synapse Analytics 和 Parallel Data Warehouse 中預存程式的 Transact-SQL 語法：

CREATE { PROC | PROCEDURE } [ schema_name.] procedure_name
    [ { @parameter data_type } [ OUT | OUTPUT ] ] [ ,...n ]
AS
{
  [ BEGIN ] sql_statement [;][ ,...n ] [ END ]
}
[;]

注意

若要檢視 SQL Server 2014 與更早版本的 Transact-SQL 語法，請參閱舊版文件。

引數

OR ALTER

適用于：Azure SQL Database，從 SQL Server 2016 (13.x) SP1) 開始SQL Server (。

如果程序已經存在，即會將它改變。

schema_name

程序所屬之結構描述的名稱。程序是以結構描述繫結的。如果在建立程式時未指定架構名稱，則會自動指派正在建立程式之使用者的預設架構。

procedure_name

程序的名稱。程序名稱必須符合識別碼的規則，在結構描述內也必須是唯一的。

警告

避免在命名程式時使用 sp_ 前置詞。 SQL Server會使用此前置詞來指定系統程式。如果有相同名稱的系統程序，使用前置詞可能造成應用程式的程式碼中斷。

若要建立本機或全域暫存程序，您可以在 procedure_name 前面使用一個數字記號 (#) 來建立本機暫存程序 ( #procedure_name)；使用兩個數字記號來建立全域暫存程序 ( ##procedure_name)。只有建立本機暫存程序的連線可以看到它，而且關閉連線時就會卸除該程序。全域暫存程序適用於所有連線，而且在最後一個工作階段結束時，會使用程序卸除。 CLR 程式無法指定暫存名稱。

程式或全域臨時程式的完整名稱，包括 ## ，不能超過 128 個字元。本機暫存程式的完整名稱，包括 # 不能超過 116 個字元。

; 數量

適用于：SQL Server 2008 (10.0.x) 和更新版本，以及 Azure SQL Database。

用來將同名程序分組的選擇性整數。您可以利用一個 DROP PROCEDURE 陳述式一併卸除這些分組的程序。

注意

未來的 Microsoft SQL Server 版本將移除這項功能。請避免在新的開發工作中使用這項功能，並規劃修改目前使用這項功能的應用程式。

編號程式無法使用 xml 或 CLR 使用者定義型別，而且無法在計劃指南中使用。

@parameter_name

在程序中宣告的參數。將 at 記號 ( @ ) 作為第一個字元來指定參數名稱。參數名稱必須符合識別碼的規則。對程序而言，參數必須是本機參數；相同的參數名稱可以用在其他程序中。

您可以宣告一個或多個參數，最大值為 2,100。除非定義了參數的預設值或將值設為等於其他參數，否則，在呼叫程序時，使用者必須提供每個已宣告參數的值。如果程序包含資料表值參數，但呼叫中缺少此參數，則會傳入空白資料表。參數只能取代常數運算式;不能使用它們，而不是資料表名稱、資料行名稱或其他資料庫物件的名稱。如需詳細資訊，請參閱 EXECUTE (Transact-SQL) 。

如果指定 FOR REPLICATION，就無法宣告參數。

[ type_schema_name. ] data_type

參數資料類型及該資料類型所屬的結構描述。

Transact-SQL 程式的指導方針：

所有 Transact-SQL 資料類型都可以當做參數使用。
您可以使用使用者定義資料表類型建立資料表值參數。資料表值參數只能是 INPUT 參數，而且必須與 READONLY 關鍵字一起使用。如需詳細資訊，請參閱使用 Table-Valued 參數 (Database Engine)
cursor 資料類型只能是 OUTPUT 參數，而且必須與 VARYING 關鍵字一起使用。

CLR 程序的指導方針：

在 Managed 程式碼中具有對等專案的所有原生SQL Server資料類型都可以當做參數使用。如需 CLR 類型和SQL Server系統資料類型之間的對應詳細資訊，請參閱對應 CLR 參數資料。如需SQL Server系統資料類型及其語法的詳細資訊，請參閱 (Transact-SQL) 資料類型。
資料表值或 資料指標 資料類型不能當做參數使用。
如果參數的資料類型是 CLR 使用者定義型別，您必須具有在該類型上的 EXECUTE 權限。

VARYING

指定支援做為輸出參數的結果集。這個參數由程序動態建構，可能會有不同的內容。只適用於 cursor 參數。此選項對 CLR 程式無效。

預設值

參數的預設值。如果定義了參數的預設值，不必指定該參數的值就可以執行程序。預設值必須是常數，或者，可以是 NULL。常數值可以採用萬用字元格式，讓您可以在將參數傳入程序時使用 LIKE 關鍵字。

預設值只會記錄在 sys.parameters.default CLR 程式的資料行中。該資料行是 Transact-SQL 程式參數的 Null。

OUT | OUTPUT

指出這個參數是輸出參數。您可以利用 OUTPUT 參數將值傳回程序的呼叫者。除非程式是 CLR 程式，否則 text、 Ntext和 image 參數不能當做 OUTPUT 參數使用。除非此程序是一個 CLR 程序，否則輸出參數可以當做資料指標預留位置使用。資料表值資料類型無法指定為程式的 OUTPUT 參數。

READONLY

表示無法在程式主體內更新或修改參數。如果參數類型是資料表值類型，就必須指定 READONLY。

RECOMPILE

表示 Database Engine 不會快取此程式的查詢計劃，並強制每次執行時編譯它。如需為何會強制重新編譯的詳細資訊，請參閱重新編譯預存程序。當指定 FOR REPLICATION 或 CLR 程式時，無法使用此選項。

若要指示 Database Engine 捨棄程式中個別查詢的查詢計劃，請在查詢的定義中使用 RECOMPILE 查詢提示。如需詳細資訊，請參閱查詢提示 (Transact-SQL)。

ENCRYPTION

適用于：SQL Server ( SQL Server 2008 (10.0.x) 及更新版本) ，Azure SQL Database。

表示SQL Server將 CREATE PROCEDURE 語句的原始文字轉換成模糊化格式。混淆的輸出不會直接顯示在SQL Server的任何目錄檢視中。無法存取系統資料表或資料庫檔案的使用者無法擷取模糊化的文字。不過，如果是特殊權限使用者 (可以透過 DAC 通訊埠存取系統資料表，或直接存取資料庫檔案)，則可使用該文字。另外，可將偵錯工具附加至伺服器處理序的使用者，還可以在執行階段從記憶體擷取解密程序。如需如何存取系統中繼資料的詳細資訊，請參閱中繼資料可見性組態。

此選項對 CLR 程式無效。

使用此選項建立的程式無法發佈為SQL Server複寫的一部分。

EXECUTE AS 子句

指定執行程序時所在的安全性內容。

針對原生編譯預存程式，從 2016 SQL Server 2016 (13.x) 和 Azure SQL Database 中，EXECUTE AS 子句沒有任何限制。在 SQL Server 2014 (12.x) SELF、OWNER 和'user_name'子句支援原生編譯預存程式。

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

FOR REPLICATION

適用于：SQL Server ( SQL Server 2008 (10.0.x) 及更新版本) ，Azure SQL Database。

指定針對複寫建立的程序。因此，無法在訂閱者上執行。利用 FOR REPLICATION 選項建立的程序會當做程序篩選來使用，而且只有在複寫期間才會執行它。如果指定 FOR REPLICATION，就無法宣告參數。無法為 CLR 程式指定 FOR REPLICATION。使用 FOR REPLICATION 建立的程序，會忽略 RECOMPILE 選項。

程式 FOR REPLICATION 在和 sys.procedures 中 sys.objects 具有物件類型RF。

{ [ BEGIN ] sql_statement [;] [ ...n ] [ END ] }

包含程式主體的一或多個 Transact-SQL 語句。您可以使用選用的 BEGIN 和 END 關鍵字來括住陳述式。如需詳細資訊，請參閱以下的＜最佳作法＞、＜一般備註＞以及＜限制事項＞這幾節。

外部名稱 assembly_name。class_name。method_name

適用于：SQL Server 2008 (10.0.x) 及更新版本，SQL Database。

指定要參考之 CLR 程式的.NET Framework元件方法。 class_name必須是有效的SQL Server識別碼，而且必須以元件中的類別的形式存在。如果該類別具有命名空間限定的名稱，且該名稱使用句號 ( . ) 來分隔命名空間的各個部分，您就必須使用方括弧 ( [] ) 或引號 ( "" ) 來分隔類別名稱。指定的方法必須是類別的靜態方法。

根據預設，SQL Server無法執行 CLR 程式碼。您可以建立、修改和卸載參考 Common Language Runtime 模組的資料庫物件;不過，在啟用clr enabled 選項之前，您無法在 SQL Server中執行這些參考。若要啟用這個選項，請使用 sp_configure。

注意

自主資料庫不支援 CLR 程序。

ATOMIC WITH

適用于：SQL Server 2014 (12.x) 和更新版本，以及 Azure SQL Database。

表示不可部分完成的預存程序執行。變更會全部認可或透過擲回例外狀況全部回復。原生編譯預存程序需要 ATOMIC WITH 區塊。

如果此程序傳回 (透過 RETURN 陳述式明確傳回或透過完成執行隱含傳回)，則會認可程序所執行的工作。如果程序擲回，則會回復程序所執行的工作。

XACT_ABORT預設為 ON 在不可部分完成的區塊內，且無法變更。 XACT_ABORT指定當 Transact-SQL 語句引發執行階段錯誤時，SQL Server是否自動回復目前的交易。

下列 SET 選項一律為 ATOMIC 區塊中的 ON，且無法變更。

CONCAT_NULL_YIELDS_NULL
QUOTED_IDENTIFIER, ARITHABORT
NOCOUNT
ANSI_NULLS
ANSI_WARNINGS

SET 選項無法在 ATOMIC 區塊內變更。使用者會話中的 SET 選項不會用於原生編譯預存程式的範圍。這些選項在編譯時間是固定的。

BEGIN、ROLLBACK 和 COMMIT 作業無法在不可部分完成的區塊內使用。

在程序的外部範圍，每個原生編譯預存程序有一個 ATOMIC 區塊。區塊不能巢狀化。如需 ATOMIC 區塊的詳細資訊，請參閱原生編譯的預存程序。

NULL | NOT NULL

判斷參數中是否允許 Null 值。預設值是 NULL。

NATIVE_COMPILATION

適用于：SQL Server 2014 (12.x) 和更新版本，以及 Azure SQL Database。

表示程序是原生編譯的。 NATIVE_COMPILATION、SCHEMABINDING 和 EXECUTE AS 可以依照任何順序來指定。如需詳細資訊，請參閱原生編譯的預存程序。

SCHEMABINDING

適用于：SQL Server 2014 (12.x) 和更新版本，以及 Azure SQL Database。

確保無法卸載或改變程式所參考的資料表。原生編譯預存程序需要 SCHEMABINDING。 (如需詳細資訊，請參閱原生編譯的預存程序。)SCHEMABINDING 限制和使用者定義函式的相關限制相同。如需詳細資訊，請參閱 CREATE FUNCTION (Transact-SQL) 中的 SCHEMABINDING 一節。

LANGUAGE = [N] 'language'

適用于：SQL Server 2014 (12.x) 和更新版本，以及 Azure SQL Database。

相當於 SET LANGUAGE (Transact-SQL) 會話選項。需要 LANGUAGE = [N] 'language'。

TRANSACTION ISOLATION LEVEL

適用于：SQL Server 2014 (12.x) 和更新版本，以及 Azure SQL Database。

原生編譯預存程序所需的。指定預存程序的交易隔離等級。選項如下：

如需這些選項的詳細資訊，請參閱 SET TRANSACTION ISOLATION LEVEL (Transact-SQL) 。

REPEATABLE READ

指定語句無法讀取已修改但尚未由其他交易認可的資料。如果其他交易對目前交易已讀取的資料進行修改，則目前交易會失敗。

SERIALIZABLE

指定下列項目：

語句無法讀取已修改但尚未由其他交易認可的資料。
如果其他交易對目前交易已讀取的資料進行修改，則目前交易會失敗。
如果其他交易插入了新資料列，且其索引鍵值落在目前交易之任何陳述式已讀取的索引鍵範圍中，則目前交易會失敗。

SNAPSHOT

指定交易中任何陳述式所讀取的資料都會與交易開始時就存在的資料版本一致。

DATEFIRST = number

適用于：SQL Server 2014 (12.x) 和更新版本，以及 Azure SQL Database。

將每週的第一天指定為 1 到 7 的數字。 DATEFIRST 是選擇性的。如果未指定，則會從指定的語言推斷設定。

如需詳細資訊，請參閱 SET DATEFIRST (Transact-SQL) 。

DATEFORMAT = format

適用于：SQL Server 2014 (12.x) 和更新版本，以及 Azure SQL Database。

指定用來解譯 date、Smalldatetime、datetime、datetime2 和 datetimeoffset 字元字串的月、日和年日期部分的順序。 DATEFORMAT 是選擇性的。如果未指定，則會從指定的語言推斷設定。

如需詳細資訊，請參閱 SET DATEFORMAT (Transact-SQL) 。

DELAYED_DURABILITY = { OFF | ON }

適用于：SQL Server 2014 (12.x) 和更新版本，以及 Azure SQL Database。

SQL Server交易認可哥以是完全持久、預設值或延遲的持久。

如需詳細資訊，請參閱控制交易持久性。

簡單範例

為了協助您開始使用，以下是兩個簡單的範例：SELECT DB_NAME() AS ThisDB; 會傳回目前資料庫的名稱。您可以將該陳述式包裝在預存程序中，例如：

CREATE PROC What_DB_is_this
AS
SELECT DB_NAME() AS ThisDB;

使用下列陳述式呼叫預存程序：EXEC What_DB_is_this;

稍微複雜一點的方法，是提供讓程序更有彈性的輸入參數。例如：

CREATE PROC What_DB_is_that @ID INT
AS
SELECT DB_NAME(@ID) AS ThatDB;

在您呼叫程序時，提供資料庫識別碼。例如 EXEC What_DB_is_that 2; 會傳回 tempdb。

如需更多範例，請參閱本文結尾的範例。

最佳做法

雖然這不是詳盡的最佳做法清單，但這些建議可能會改善程式效能。

使用 SET NOCOUNT ON 陳述式做為程序主體中的第一個陳述式。亦即，將該陳述式放在 AS 關鍵字正後方。這會在執行任何 SELECT、INSERT、UPDATE、MERGE 和 DELETE 子句之後，關閉SQL Server傳送回用戶端的訊息。這樣能讓所產生的輸出為最小，以保持清晰。但在現今硬體上沒有可測量的效能優勢。如需詳細資訊，請參閱 SET NOCOUNT (Transact-SQL) 。
建立或參考程序中的資料庫物件時，請使用結構描述名稱。如果不需要搜尋多個架構，Database Engine 解析物件名稱需要較少的處理時間。它也能防止在不指定結構描述的情況下建立物件時，因指派使用者預設結構描述所引起的權限和存取問題。
避免在 WHERE 和 JOIN 子句中指定的資料行周圍使用包裝函數。這麼做會使資料行變成非決定性，而且會使查詢處理器無法使用索引。
避免在 SELECT 陳述式中使用傳回許多資料列的純量函數。純量函數必須套用到每個資料列，因此，所產生的行為類似以資料列為主的處理，而且會降低效能。
請避免使用 SELECT *。請改為指定所需的資料行名稱。這可能會防止停止程式執行的一些 Database Engine 錯誤。例如，假設 SELECT * 陳述式會從 12 欄式資料表傳回資料並將該資料插入 12 欄式暫存資料表，那麼，直到資料表中資料行的數目或順序變更時，該陳述式才會成功。
請避免處理或傳回太多資料。盡早將程序程式碼中的結果範圍縮小，讓該程序所執行的所有後續作業都可以使用最小的資料集完成。只將基本資料傳送到用戶端應用程式。此作法比透過網路傳送額外資料並強制用戶端應用程式處理過大的結果集更有效率。
透過 BEGIN/COMMIT TRANSACTION 使用明確的交易，並讓交易越短越好。交易越久表示記錄鎖定越久，而且發生死結的可能性也就越大。
使用 Transact-SQL TRY...程式內錯誤處理的 CATCH 功能。嘗試。。。CATCH 可以封裝 Transact-SQL 語句的完整區塊。這樣不但會使效能負擔較小，而且還會使用更少的程式讓錯誤報告更精確。
在程式主體中 CREATE TABLE 或 ALTER TABLE Transact-SQL 語句所參考的所有資料表資料行上使用 DEFAULT 關鍵字。這可防止將 Null 傳遞至不允許 Null 值的資料行。
在暫存資料表中的每個資料行使用 NULL 或 NOT NULL。當 CREATE TABLE 或 ALTER TABLE 語句中未指定這些屬性時，ANSI_DFLT_ON和ANSI_DFLT_OFF選項可控制 Database Engine 將 Null 或 NOT Null 屬性指派給資料行的方式。如果某個連接執行程序時所用的選項設定，不同於建立程序的連接所用設定，針對第二個連接建立的資料表資料行，可以有不同的 Null 屬性，且可以展現不同的行為。如果針對每個資料行明確陳述 NULL 或 NOT NULL，就會利用執行程序之所有連接的相同 Null 屬性來建立暫存資料表。
使用轉換 null 的修改陳述式，然後使用查詢中的 null 值加入排除資料列的邏輯。請注意，在 Transact-SQL 中，Null 不是空值或「無」值。它是未知值的預留位置，而且可能造成非預期的行為，特別是在查詢結果集或使用 AGGREGATE 函數時。
除非不同的值有特定需要，否則請使用 UNION ALL 運算子代替 UNION 或 OR 運算子。 UNION ALL 運算子需要較少的處理額外負荷，因為重複專案不會篩選掉結果集。

備註

程序沒有預先定義的大小上限。

程序中指定的變數可以是使用者自訂變數或系統變數，如 @@SPID。

第一次執行程序時，會編譯它來決定擷取資料的最佳存取計畫。如果程式仍在 Database Engine 的計畫快取中，後續執行程式可能會重複使用已經產生的計畫。

SQL Server啟動時，可以自動執行一或多個程式。程式必須由資料庫中的 master 系統管理員建立，並在 系統管理員 固定伺服器角色下執行，作為背景進程。程式不能有任何輸入或輸出參數。如需詳細資訊，請參閱執行預存程序。

當某個程式呼叫另一個程式或藉由參考 CLR 常式、類型或匯總來執行 Managed 程式碼時，程式會巢狀化。程序和 Managed 程式碼參考的巢狀結構最多可有 32 個層級。當被呼叫的程序或 Managed 程式碼參考開始執行時，巢狀層級會加一；當被呼叫的程序或 Managed 程式碼參考執行完畢時，巢狀層級會減一。從 Managed 程式碼內叫用的方法不會計入巢狀層級限制。但當 CLR 預存程序透過 SQL Server Managed 提供者執行資料存取作業時，會在 Managed 程式碼轉換成 SQL 的過程中，加入一個額外的巢狀層級。

企圖超越最大巢狀層級將會導致整個呼叫鏈結失敗。您可以使用 @@NESTLEVEL 函數傳回目前預存程序執行的巢狀層級。

互通性

Database Engine 會在建立或修改 Transact-SQL 程式時儲存 SET QUOTED_IDENTIFIER 和 SET ANSI_NullS的設定。這些原始設定是在執行程序時使用的。因此，當程序正在執行時，SET QUOTED_IDENTIFIER 和 SET ANSI_NULLS 的任何用戶端工作階段設定，都會被忽略。

建立或修改程式時，不會儲存其他 SET 選項，例如 SET ARITHABORT、SET ANSI_WARNINGS或 SET ANSI_PADDINGS。如果程序的邏輯取決於特定設定，請在程序的開頭併入 SET 陳述式，以確保可以有適當的設定。如果從程序中執行 SET 陳述式，設定的有效性只能維持到程序完成執行。之後，該設定就會還原為程序被呼叫時所具有的值。這可讓個別用戶端設定本身想要的選項，而不影響程序的邏輯。

除了 SET SHOWPLAN_TEXT 和 SET SHOWPLAN_ALL 以外，其他所有 SET 陳述式都可以在程序中指定。這些陳述式必須是批次中唯一的陳述式。所選 SET 選項在程序執行期間仍然有效，然後會還原為先前的設定。

注意

當在程序或使用者定義函數中傳遞參數時，或在批次陳述式中宣告和設定變數時，未接受 SET ANSI_WARNINGS。例如，如果將變數定義為 char(3) ，然後設定為大於三個字元的值，資料就會被截斷成定義的大小，而 INSERT 或 UPDATE 陳述式會執行成功。

限制事項

CREATE PROCEDURE 語句無法與單一批次中的其他 Transact-SQL 語句結合。

下列語句不能用於預存程式主體中的任何位置。

CREATE	SET	USE
CREATE AGGREGATE	SET SHOWPLAN_TEXT	USE database_name
CREATE DEFAULT	SET SHOWPLAN_XML
CREATE RULE	SET PARSEONLY
CREATE SCHEMA	SET SHOWPLAN_ALL
CREATE 或 ALTER TRIGGER
CREATE 或 ALTER FUNCTION
CREATE 或 ALTER PROCEDURE
CREATE 或 ALTER VIEW

程式可以參考尚未存在的資料表。在建立時，只會執行語法檢查。程式在第一次執行之前不會編譯。只有在編譯期間才會解析程序中參考的所有物件。因此，可以成功建立參考不存在之資料表的語法正確程式;不過，如果參考的資料表不存在，則程式在執行時間失敗。

您無法在執行程式時，將函式名稱指定為參數預設值或傳遞至參數的值。但您可以變數形式傳遞函數，如下列範例所示。

-- Passing the function value as a variable.
DECLARE @CheckDate DATETIME = GETDATE();
EXEC dbo.uspGetWhereUsedProductID 819, @CheckDate;
GO

如果程式在遠端實例上SQL Server進行變更，就無法回復變更。遠端程式不會參與交易。

若要讓 Database Engine 在.NET Framework中多載時參考正確的方法，EXTERNAL NAME 子句中指定的方法必須具有下列特性：

宣告為靜態方法。
接收與程序的參數數目相同的參數數目。
使用與SQL Server程式對應參數資料類型相容的參數類型。如需將SQL Server資料類型與.NET Framework資料類型相符的資訊，請參閱對應 CLR 參數資料。

中繼資料

下表列出可用於傳回預存程序之詳細資訊的目錄檢視和動態管理檢視。

檢視	描述
sys.sql_modules	傳回 Transact-SQL 程式的定義。使用 ENCRYPTION 選項建立的程式文字無法使用目錄檢視來檢視 `sys.sql_modules` 。
sys.assembly_modules	傳回 CLR 程序的詳細資訊。
sys.parameters	傳回程序所定義之參數的詳細資訊。
sys.sql_expression_dependencies sys.dm_sql_referenced_entities sys.dm_sql_referencing_entities	傳回程序所參考的物件。

若要預估已編譯的程序大小，請使用下列效能監視器計數器。

效能監視器物件名稱	效能監視器計數器名稱
SQLServer：計畫快取物件	快取點擊率
	快取頁面
	快取物件計數 ¹

¹ 這些計數器適用于各種快取物件類別，包括臨機操作 Transact-SQL、備妥的 Transact-SQL、程式、觸發程式等等。如需詳細資訊，請參閱 SQL Server 的 Plan Cache 物件。

權限

CREATE PROCEDURE需要資料庫中的許可權，以及 ALTER 建立程式之架構的許可權，或需要db_ddladmin固定資料庫角色的成員資格。

針對 CLR 預存程式，需要外部 NAME 子句中所參考之元件的擁有權，或 REFERENCES 該元件的許可權。

CREATE PROCEDURE 和記憶體優化資料表

從傳統和原生編譯的預存程序存取經記憶體最佳化資料表，都可以達到最高效率。在大部分情況下，原生程序是更有效率的方式。如需詳細資訊，請參閱原生編譯的預存程序。

下列範例示範如何建立原生編譯的預存程序，以存取經記憶體最佳化的資料表 dbo.Departments：

CREATE PROCEDURE dbo.usp_add_kitchen @dept_id INT, @kitchen_count INT NOT NULL
WITH EXECUTE AS OWNER, SCHEMABINDING, NATIVE_COMPILATION
AS
BEGIN ATOMIC WITH (TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = N'us_english')

UPDATE dbo.Departments
SET kitchen_count = ISNULL(kitchen_count, 0) + @kitchen_count
WHERE ID = @dept_id
END;
GO

在沒有NATIVE_COMPILATION的情況下建立的程式無法變更為原生編譯的預存程式。

如需原生編譯預存程序的可程式性、支援的查詢介面區及運算子的說明，請參閱原生編譯的 T-SQL 模組支援的功能。

範例

類別	代表性語法元素
基本語法	CREATE PROCEDURE
傳遞參數	@parameter = default OUTPUT 資料表值參數類型資料指標不同
使用預存程序修改資料	UPDATE
錯誤處理	TRY...CATCH
模糊化程序定義	WITH ENCRYPTION
強制程序重新編譯	WITH RECOMPILE
設定安全性內容	EXECUTE AS

基本語法

本節的範例使用所需的最少語法示範 CREATE PROCEDURE 陳述式的基本功能。

A. 建立 Transact-SQL 程式

下列範例會建立預存程式，從 AdventureWorks2019 資料庫中的檢視傳回所有員工 (提供之名字和姓氏) 、職稱及其部門名稱。此程式不會使用任何參數。其會示範執行程序的三種方法。

CREATE PROCEDURE HumanResources.uspGetAllEmployees
AS
    SET NOCOUNT ON;
    SELECT LastName, FirstName, JobTitle, Department
    FROM HumanResources.vEmployeeDepartment;
GO

SELECT * FROM HumanResources.vEmployeeDepartment;

您可以利用下列方式執行 uspGetEmployees 程序：

EXECUTE HumanResources.uspGetAllEmployees;
GO
-- Or
EXEC HumanResources.uspGetAllEmployees;
GO
-- Or, if this procedure is the first statement within a batch:
HumanResources.uspGetAllEmployees;

B. 傳回多個結果集

下列程序會傳回兩個結果集。

CREATE PROCEDURE dbo.uspMultipleResults
AS
SELECT TOP(10) BusinessEntityID, Lastname, FirstName FROM Person.Person;
SELECT TOP(10) CustomerID, AccountNumber FROM Sales.Customer;
GO

C. 建立 CLR 預存程式

下列範例會建立 GetPhotoFromDB 程序，以參考 HandlingLOBUsingCLR 組件中 LargeObjectBinary 類別的 GetPhotoFromDB 方法。建立程序之前，會先在本機資料庫中註冊 HandlingLOBUsingCLR 組件。

適用于：SQL Server 2008 (10.0.x) 及更新版本，如果使用從 assembly_bits 建立的元件，SQL Database (。

CREATE ASSEMBLY HandlingLOBUsingCLR
FROM '\\MachineName\HandlingLOBUsingCLR\bin\Debug\HandlingLOBUsingCLR.dll';
GO
CREATE PROCEDURE dbo.GetPhotoFromDB
(
    @ProductPhotoID INT
    , @CurrentDirectory NVARCHAR(1024)
    , @FileName NVARCHAR(1024)
)
AS EXTERNAL NAME HandlingLOBUsingCLR.LargeObjectBinary.GetPhotoFromDB;
GO

傳遞參數

本節的範例會示範如何使用輸入和輸出參數，在預存程序之間來回傳遞值。

D. 使用輸入參數建立程式

下列範例所建立的預存程序，會傳遞特定員工的名字和姓氏值，藉以傳回該員工的資訊。此程序只會接受與所傳遞參數完全相符的項目。

IF OBJECT_ID ( 'HumanResources.uspGetEmployees', 'P' ) IS NOT NULL
    DROP PROCEDURE HumanResources.uspGetEmployees;
GO
CREATE PROCEDURE HumanResources.uspGetEmployees
    @LastName NVARCHAR(50),
    @FirstName NVARCHAR(50)
AS

    SET NOCOUNT ON;
    SELECT FirstName, LastName, JobTitle, Department
    FROM HumanResources.vEmployeeDepartment
    WHERE FirstName = @FirstName AND LastName = @LastName;
GO

您可以利用下列方式執行 uspGetEmployees 程序：

EXECUTE HumanResources.uspGetEmployees N'Ackerman', N'Pilar';
-- Or
EXEC HumanResources.uspGetEmployees @LastName = N'Ackerman', @FirstName = N'Pilar';
GO
-- Or
EXECUTE HumanResources.uspGetEmployees @FirstName = N'Pilar', @LastName = N'Ackerman';
GO
-- Or, if this procedure is the first statement within a batch:
HumanResources.uspGetEmployees N'Ackerman', N'Pilar';

E. 搭配萬用字元參數使用程式

下列範例所建立的預存程序，會傳遞一些員工的全名或部分姓名值，藉以傳回這些員工的資訊。這個程序模式會與所傳遞的參數相符，或者，如果沒有提供參數的話，該模式會使用預設值 (字母 D 開頭的姓氏)。

IF OBJECT_ID ( 'HumanResources.uspGetEmployees2', 'P' ) IS NOT NULL
    DROP PROCEDURE HumanResources.uspGetEmployees2;
GO
CREATE PROCEDURE HumanResources.uspGetEmployees2
    @LastName NVARCHAR(50) = N'D%',
    @FirstName NVARCHAR(50) = N'%'
AS
    SET NOCOUNT ON;
    SELECT FirstName, LastName, JobTitle, Department
    FROM HumanResources.vEmployeeDepartment
    WHERE FirstName LIKE @FirstName AND LastName LIKE @LastName;

您可以利用多種組合執行 uspGetEmployees2 程序。此處僅示範其中幾種可能的組合。

EXECUTE HumanResources.uspGetEmployees2;
-- Or
EXECUTE HumanResources.uspGetEmployees2 N'Wi%';
-- Or
EXECUTE HumanResources.uspGetEmployees2 @FirstName = N'%';
-- Or
EXECUTE HumanResources.uspGetEmployees2 N'[CK]ars[OE]n';
-- Or
EXECUTE HumanResources.uspGetEmployees2 N'Hesse', N'Stefen';
-- Or
EXECUTE HumanResources.uspGetEmployees2 N'H%', N'S%';

F. 使用 OUTPUT 參數

下列範例會建立 uspGetList 程序。此程式會傳回價格未超過指定金額的產品清單。這個範例顯示多個 SELECT 陳述式和多個 OUTPUT 參數的用法。 OUTPUT 參數可讓外部程式、批次或多個 Transact-SQL 語句存取程式執行期間所設定的值。

IF OBJECT_ID ( 'Production.uspGetList', 'P' ) IS NOT NULL
    DROP PROCEDURE Production.uspGetList;
GO
CREATE PROCEDURE Production.uspGetList @Product VARCHAR(40)
    , @MaxPrice MONEY
    , @ComparePrice MONEY OUTPUT
    , @ListPrice MONEY OUT
AS
    SET NOCOUNT ON;
    SELECT p.[Name] AS Product, p.ListPrice AS 'List Price'
    FROM Production.Product AS p
    JOIN Production.ProductSubcategory AS s
      ON p.ProductSubcategoryID = s.ProductSubcategoryID
    WHERE s.[Name] LIKE @Product AND p.ListPrice < @MaxPrice;
-- Populate the output variable @ListPprice.
SET @ListPrice = (SELECT MAX(p.ListPrice)
    FROM Production.Product AS p
    JOIN Production.ProductSubcategory AS s
      ON p.ProductSubcategoryID = s.ProductSubcategoryID
    WHERE s.[Name] LIKE @Product AND p.ListPrice < @MaxPrice);
-- Populate the output variable @compareprice.
SET @ComparePrice = @MaxPrice;
GO

執行 uspGetList 以傳回 Adventure Works 產品清單， (自行車) 成本低於 $700 。您可以搭配流程控制語言使用 OUTPUT 參數 @Cost 和 @ComparePrices，以在 [訊息] 視窗中傳回訊息。

注意

建立程序以及使用變數時，都必須定義 OUTPUT 變數。參數名稱與變數名稱不一定要相符；不過，除非使用 @ListPrice = variable，否則資料類型與參數定位必須相符。

DECLARE @ComparePrice MONEY, @Cost MONEY;
EXECUTE Production.uspGetList '%Bikes%', 700,
    @ComparePrice OUT,
    @Cost OUTPUT
IF @Cost <= @ComparePrice
BEGIN
    PRINT 'These products can be purchased for less than
    $'+RTRIM(CAST(@ComparePrice AS VARCHAR(20)))+'.'
END
ELSE
    PRINT 'The prices for all products in this category exceed
    $'+ RTRIM(CAST(@ComparePrice AS VARCHAR(20)))+'.';

部分結果集如下：

Product                     List Price
--------------------------  ----------
Road-750 Black, 58          539.99
Mountain-500 Silver, 40     564.99
Mountain-500 Silver, 42     564.99
...
Road-750 Black, 48          539.99
Road-750 Black, 52          539.99

(14 row(s) affected)

These items can be purchased for less than $700.00.

G. 使用資料表值參數

下列範例會使用資料表值參數類型，將多個資料列插入資料表中。此範例會建立此參數類型、宣告資料表變數進行參考、填入參數清單，然後將值傳遞給預存程序。預存程序會使用這些值，將多個資料列插入資料表中。

/* Create a table type. */
CREATE TYPE LocationTableType AS TABLE
( LocationName VARCHAR(50)
, CostRate INT );
GO

/* Create a procedure to receive data for the table-valued parameter. */
CREATE PROCEDURE usp_InsertProductionLocation
    @TVP LocationTableType READONLY
    AS
    SET NOCOUNT ON
    INSERT INTO [AdventureWorks2012].[Production].[Location]
       ([Name]
       , [CostRate]
       , [Availability]
       , [ModifiedDate])
    SELECT *, 0, GETDATE()
    FROM @TVP;
GO

/* Declare a variable that references the type. */
DECLARE @LocationTVP
AS LocationTableType;

/* Add data to the table variable. */
INSERT INTO @LocationTVP (LocationName, CostRate)
    SELECT [Name], 0.00
    FROM
    [AdventureWorks2012].[Person].[StateProvince];

/* Pass the table variable data to a stored procedure. */
EXEC usp_InsertProductionLocation @LocationTVP;
GO

H. 使用 OUTPUT 資料指標參數

下列範例會使用 OUTPUT 資料指標參數，將某個資料指標 (對程序而言，其為本機資料指標) 傳遞回呼叫的批次、程序或觸發程序。

首先，建立宣告的程序，然後在 Currency 資料表上開啟資料指標：

CREATE PROCEDURE dbo.uspCurrencyCursor
    @CurrencyCursor CURSOR VARYING OUTPUT
AS
    SET NOCOUNT ON;
    SET @CurrencyCursor = CURSOR
    FORWARD_ONLY STATIC FOR
      SELECT CurrencyCode, Name
      FROM Sales.Currency;
    OPEN @CurrencyCursor;
GO

接著，執行宣告本機資料指標變數的批次、執行程序將資料指標指派給本機變數，然後從資料指標提取資料列。

DECLARE @MyCursor CURSOR;
EXEC dbo.uspCurrencyCursor @CurrencyCursor = @MyCursor OUTPUT;
WHILE (@@FETCH_STATUS = 0)
BEGIN;
    FETCH NEXT FROM @MyCursor;
END;
CLOSE @MyCursor;
DEALLOCATE @MyCursor;
GO

使用預存程式修改資料

本節範例將示範如何在程序的定義中包含資料操作語言 (DML) 陳述式，藉以在資料表或檢視表中插入或修改資料。

I. 在預存程式中使用 UPDATE

下列範例會在預存程序中使用 UPDATE 陳述式。此程序會採用一個輸入參數 @NewHours 和一個輸出參數 @RowCount。 UPDATE 陳述式內會使用 @NewHours 參數值，以更新資料表 HumanResources.Employee 中的資料行 VacationHours。 @RowCount 輸出參數是用來將受影響的資料列數目傳回給區域變數。 SET 子句會使用 CASE 運算式，以條件方式判斷針對 VacationHours 所設定的值。按照時數支付薪資給員工時 (SalariedFlag = 0)，VacationHours 會設定為目前的時數加上 @NewHours 中指定的值，否則 VacationHours 會設定為 @NewHours 中指定的值。

CREATE PROCEDURE HumanResources.Update_VacationHours
@NewHours SMALLINT, @Rowcount INT OUTPUT
AS
SET NOCOUNT ON;
UPDATE HumanResources.Employee
SET VacationHours =
    ( CASE
        WHEN SalariedFlag = 0 THEN VacationHours + @NewHours
        ELSE @NewHours
        END
    )
WHERE CurrentFlag = 1;
SET @Rowcount = @@rowcount;

GO
DECLARE @Rowcount INT
EXEC HumanResources.Update_VacationHours 40, @Rowcount OUTPUT
PRINT @Rowcount;

錯誤處理

本節範例將示範如何處理在執行預存程序時可能會發生的錯誤。

J. 使用 TRY...抓住

下列範例使用 TRY...CATCH 建構傳回預存程序執行期間所攔截到的錯誤資訊。

CREATE PROCEDURE Production.uspDeleteWorkOrder ( @WorkOrderID INT )
AS
SET NOCOUNT ON;
BEGIN TRY
  BEGIN TRANSACTION
  -- Delete rows from the child table, WorkOrderRouting, for the specified work order.
    DELETE FROM Production.WorkOrderRouting
    WHERE WorkOrderID = @WorkOrderID;
  -- Delete the rows from the parent table, WorkOrder, for the specified work order.
    DELETE FROM Production.WorkOrder
    WHERE WorkOrderID = @WorkOrderID;
  COMMIT
END TRY

BEGIN CATCH
  -- Determine if an error occurred.
  IF @@TRANCOUNT > 0
    ROLLBACK

  -- Return the error information.
  DECLARE @ErrorMessage NVARCHAR(4000), @ErrorSeverity INT;
  SELECT @ErrorMessage = ERROR_MESSAGE(),@ErrorSeverity = ERROR_SEVERITY();
  RAISERROR(@ErrorMessage, @ErrorSeverity, 1);
END CATCH;

GO
EXEC Production.uspDeleteWorkOrder 13;
GO
/* Intentionally generate an error by reversing the order in which rows
   are deleted from the parent and child tables. This change does not
   cause an error when the procedure definition is altered, but produces
   an error when the procedure is executed.
*/
ALTER PROCEDURE Production.uspDeleteWorkOrder ( @WorkOrderID INT )
AS

BEGIN TRY
  BEGIN TRANSACTION
  -- Delete the rows from the parent table, WorkOrder, for the specified work order.
    DELETE FROM Production.WorkOrder
    WHERE WorkOrderID = @WorkOrderID;

  -- Delete rows from the child table, WorkOrderRouting, for the specified work order.
    DELETE FROM Production.WorkOrderRouting
    WHERE WorkOrderID = @WorkOrderID;
  COMMIT TRANSACTION
END TRY

BEGIN CATCH
  -- Determine if an error occurred.
  IF @@TRANCOUNT > 0
    ROLLBACK TRANSACTION

  -- Return the error information.
  DECLARE @ErrorMessage NVARCHAR(4000), @ErrorSeverity INT;
  SELECT @ErrorMessage = ERROR_MESSAGE(),@ErrorSeverity = ERROR_SEVERITY();
  RAISERROR(@ErrorMessage, @ErrorSeverity, 1);
END CATCH;
GO
-- Execute the altered procedure.
EXEC Production.uspDeleteWorkOrder 15;
GO
DROP PROCEDURE Production.uspDeleteWorkOrder;

混淆程式定義

本節範例將示範如何模糊化預存程序的定義。

K. 使用 WITH ENCRYPTION 選項

下列範例會建立 HumanResources.uspEncryptThis 程序。

適用于：SQL Server 2008 (10.0.x) 和更新版本，以及 Azure SQL Database。

CREATE PROCEDURE HumanResources.uspEncryptThis
WITH ENCRYPTION
AS
    SET NOCOUNT ON;
    SELECT BusinessEntityID, JobTitle, NationalIDNumber,
        VacationHours, SickLeaveHours
    FROM HumanResources.Employee;
GO

WITH ENCRYPTION 選項會在查詢系統目錄或使用中繼資料函數時模糊化程序的定義，如下列範例所示。

執行 sp_helptext：

EXEC sp_helptext 'HumanResources.uspEncryptThis';

以下為結果集。

The text for object 'HumanResources.uspEncryptThis' is encrypted.

直接查詢 sys.sql_modules 目錄檢視：

SELECT definition FROM sys.sql_modules
WHERE object_id = OBJECT_ID('HumanResources.uspEncryptThis');

以下為結果集。

definition
--------------------------------
NULL

注意

Azure Synapse Analytics 不支援系統預存程式 sp_helptext 。請改用 sys.sql_modules 物件目錄檢視。

強制重新編譯程式

本節範例將會使用 WITH RECOMPILE 子句強制程序在每次執行時重新編譯。

L. 使用 WITH RECOMPILE 選項

WITH RECOMPILE當提供給程式的參數不是一般，以及不應該快取或儲存在記憶體中的新執行計畫時，子句會很有説明。

IF OBJECT_ID ( 'dbo.uspProductByVendor', 'P' ) IS NOT NULL
    DROP PROCEDURE dbo.uspProductByVendor;
GO
CREATE PROCEDURE dbo.uspProductByVendor @Name VARCHAR(30) = '%'
WITH RECOMPILE
AS
    SET NOCOUNT ON;
    SELECT v.Name AS 'Vendor name', p.Name AS 'Product name'
    FROM Purchasing.Vendor AS v
    JOIN Purchasing.ProductVendor AS pv
      ON v.BusinessEntityID = pv.BusinessEntityID
    JOIN Production.Product AS p
      ON pv.ProductID = p.ProductID
    WHERE v.Name LIKE @Name;

設定安全性內容

本節範例將使用 EXECUTE AS 子句設定用以執行預存程序的安全性內容。

M. 使用 EXECUTE AS 子句

下列範例顯示利用 EXECUTE AS 子句來指定可執行程序的安全性內容。在這個範例中，選項 CALLER 指定可以在呼叫程序之使用者的內容中執行程序。

CREATE PROCEDURE Purchasing.uspVendorAllInfo
WITH EXECUTE AS CALLER
AS
    SET NOCOUNT ON;
    SELECT v.Name AS Vendor, p.Name AS 'Product name',
      v.CreditRating AS 'Rating',
      v.ActiveFlag AS Availability
    FROM Purchasing.Vendor v
    INNER JOIN Purchasing.ProductVendor pv
      ON v.BusinessEntityID = pv.BusinessEntityID
    INNER JOIN Production.Product p
      ON pv.ProductID = p.ProductID
    ORDER BY v.Name ASC;
GO

N. 建立自訂許可權集合

下列範例會使用 EXECUTE AS 建立資料庫作業的自訂權限。某些作業，例如 TRUNCATE TABLE，沒有可授與的許可權。將 TRUNCATE TABLE 陳述式併入預存程序，並且將該程序指定成以有權修改資料表的使用者身分執行，即可針對您授與程序之 EXECUTE 權限的使用者，擴充截斷資料表的權限。

CREATE PROCEDURE dbo.TruncateMyTable
WITH EXECUTE AS SELF
AS TRUNCATE TABLE MyDB..MyTable;

範例：Azure Synapse Analytics and Analytics Platform System (PDW)

O. 建立執行 SELECT 語句的預存程式

這個範例會說明建立和執行程序的基本語法。當執行批次時，CREATE PROCEDURE 必須是第一個陳述式。例如，若要在 AdventureWorksPDW2022中建立下列預存程式，請先設定資料庫內容，然後執行 CREATE PROCEDURE 語句。

-- Uses AdventureWorksDW database

--Run CREATE PROCEDURE as the first statement in a batch.
CREATE PROCEDURE Get10TopResellers
AS
BEGIN
    SELECT TOP (10) r.ResellerName, r.AnnualSales
    FROM DimReseller AS r
    ORDER BY AnnualSales DESC, ResellerName ASC;
END
;
GO

--Show 10 Top Resellers
EXEC Get10TopResellers;

CREATE PROCEDURE (Transact-SQL)

Syntax

引數

OR ALTER

schema_name

procedure_name

; 數量

@parameter_name

[ type_schema_name. ] data_type

VARYING

預設值

OUT | OUTPUT

READONLY

RECOMPILE

ENCRYPTION

EXECUTE AS 子句

FOR REPLICATION

{ [ BEGIN ] sql_statement [;] [ ...n ] [ END ] }

外部名稱 assembly_name。class_name。method_name

ATOMIC WITH

NULL | NOT NULL

NATIVE_COMPILATION

SCHEMABINDING

LANGUAGE = [N] 'language'

TRANSACTION ISOLATION LEVEL

REPEATABLE READ

SERIALIZABLE

SNAPSHOT

DATEFIRST = number

DATEFORMAT = format

DELAYED_DURABILITY = { OFF | ON }

簡單範例

最佳做法

備註

互通性

限制事項

中繼資料

權限

CREATE PROCEDURE 和記憶體優化資料表

範例

基本語法

A. 建立 Transact-SQL 程式

B. 傳回多個結果集

C. 建立 CLR 預存程式

傳遞參數

D. 使用輸入參數建立程式

E. 搭配萬用字元參數使用程式

F. 使用 OUTPUT 參數

G. 使用資料表值參數

H. 使用 OUTPUT 資料指標參數

使用預存程式修改資料

I. 在預存程式中使用 UPDATE

錯誤處理

J. 使用 TRY...抓住

混淆程式定義

K. 使用 WITH ENCRYPTION 選項

強制重新編譯程式

L. 使用 WITH RECOMPILE 選項

設定安全性內容

M. 使用 EXECUTE AS 子句

N. 建立自訂許可權集合

範例：Azure Synapse Analytics and Analytics Platform System (PDW)

O. 建立執行 SELECT 語句的預存程式

另請參閱

其他資源

其他資源