CREATE PROCEDURE (Transact-SQL)CREATE PROCEDURE (Transact-SQL)

適用於: 是SQL Server 是Azure SQL Database 是Azure SQL 資料倉儲 是平行處理資料倉儲 APPLIES TO: yesSQL Server yesAzure SQL Database yesAzure SQL Data Warehouse yesParallel Data Warehouse

SQL ServerSQL ServerAzure SQL DatabaseAzure SQL Database、Azure SQL 資料倉儲和平行處理資料倉儲中,建立 Transact-SQLTransact-SQL 或通用語言執行平台 (CLR) 預存程序。Creates a Transact-SQLTransact-SQL or common language runtime (CLR) stored procedure in SQL ServerSQL Server, Azure SQL DatabaseAzure SQL Database, Azure SQL Data Warehouse and Parallel Data Warehouse. 預存程序類似於其他程式設計語言中的程序,這些程序可以:Stored procedures are similar to procedures in other programming languages in that they can:

  • 接受輸入參數,並以輸出參數的形式將多個數值傳回呼叫程序或批次處理。Accept input parameters and return multiple values in the form of output parameters to the calling procedure or batch.

  • 包含可在資料庫中執行作業的程式陳述式,包括呼叫其他程序。Contain programming statements that perform operations in the database, including calling other procedures.

  • 將狀態值傳回呼叫程序或批次處理,以指示成功或失敗 (及失敗原因)。Return a status value to a calling procedure or batch to indicate success or failure (and the reason for failure).

使用此陳述式在目前的資料庫中建立永久程序,或在 tempdb 資料庫中建立暫存程序。Use this statement to create a permanent procedure in the current database or a temporary procedure in the tempdb database.

注意

本主題將討論如何將 .NET Framework CLR 整合至 SQL Server。The integration of .NET Framework CLR into SQL Server is discussed in this topic. CLR 整合不適用於 Azure SQL DatabaseSQL DatabaseCLR integration does not apply to Azure SQL DatabaseSQL Database.

若要略過語法詳細資料,並了解基本預存程序的簡單範例,可跳到簡單範例Jump to Simple Examples to skip the details of the syntax and get to a quick example of a basic stored procedure.

主題連結圖示 Transact-SQL 語法慣例Topic link icon Transact-SQL Syntax Conventions

語法Syntax

-- Transact-SQL Syntax for Stored Procedures in SQL Server and Azure SQL Database  
  
CREATE [ OR ALTER ] { PROC | PROCEDURE } 
    [schema_name.] procedure_name [ ; number ]   
    [ { @parameter [ type_schema_name. ] data_type }  
        [ VARYING ] [ = default ] [ OUT | OUTPUT | [READONLY]  
    ] [ ,...n ]   
[ WITH <procedure_option> [ ,...n ] ]  
[ FOR REPLICATION ]   
AS { [ BEGIN ] sql_statement [;] [ ...n ] [ END ] }  
[;]  
  
<procedure_option> ::=   
    [ ENCRYPTION ]  
    [ RECOMPILE ]  
    [ EXECUTE AS Clause ]  
-- Transact-SQL Syntax for CLR Stored Procedures  
  
CREATE [ OR ALTER ] { PROC | PROCEDURE } 
    [schema_name.] procedure_name [ ; number ]   
    [ { @parameter [ 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 Syntax for Natively Compiled Stored Procedures  
  
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 } ]  
-- Transact-SQL Syntax for Stored Procedures in Azure SQL Data Warehouse
-- and Parallel Data Warehouse  
  
-- Create a stored procedure   
CREATE { PROC | PROCEDURE } [ schema_name.] procedure_name  
    [ { @parameterdata_type } [ OUT | OUTPUT ] ] [ ,...n ]  
AS { [ BEGIN ] sql_statement [;][ ,...n ] [ END ] }  
[;]  

引數Arguments

OR ALTEROR ALTER
適用於:Azure SQL DatabaseSQL Database SQL ServerSQL Server (從 SQL Server 2016 (13.x)SQL Server 2016 (13.x) SP1 開始)。Applies to: Azure SQL DatabaseSQL Database, SQL ServerSQL Server (starting with SQL Server 2016 (13.x)SQL Server 2016 (13.x) SP1).

如果程序已經存在,即會將它改變。Alters the procedure if it already exists.

schema_nameschema_name
程序所屬之結構描述的名稱。The name of the schema to which the procedure belongs. 程序是以結構描述繫結的。Procedures are schema-bound. 如果在建立程序時未指定結構描述名稱,就會自動指派建立程序之使用者的預設結構描述。If a schema name is not specified when the procedure is created, the default schema of the user who is creating the procedure is automatically assigned.

procedure_nameprocedure_name
程序的名稱。The name of the procedure. 程序名稱必須符合識別碼的規則,在結構描述內也必須是唯一的。Procedure names must comply with the rules for identifiers and must be unique within the schema.

為程序命名時,請避免使用 sp_ 前置詞。Avoid the use of the sp_ prefix when naming procedures. 這個前置詞是供 SQL ServerSQL Server 指定系統程序時使用。This prefix is used by SQL ServerSQL Server to designate system procedures. 如果有相同名稱的系統程序,使用前置詞可能造成應用程式的程式碼中斷。Using the prefix can cause application code to break if there is a system procedure with the same name.

若要建立本機或全域暫存程序,您可以在 procedure_name 前面使用一個數字記號 (#) 來建立本機暫存程序 ( #procedure_name);使用兩個數字記號來建立全域暫存程序 ( ##procedure_name)。Local or global temporary procedures can be created by using one number sign (#) before procedure_name (#procedure_name) for local temporary procedures, and two number signs for global temporary procedures (##procedure_name). 只有建立本機暫存程序的連線可以看到它,而且關閉連線時就會卸除該程序。A local temporary procedure is visible only to the connection that created it and is dropped when that connection is closed. 全域暫存程序適用於所有連線,而且在最後一個工作階段結束時,會使用程序卸除。A global temporary procedure is available to all connections and is dropped at the end of the last session using the procedure. 無法為 CLR 程序指定暫存名稱。Temporary names cannot be specified for CLR procedures.

程序或全域暫存程序的完整名稱 (包括 ##) 不能超過 128 個字元。The complete name for a procedure or a global temporary procedure, including ##, cannot exceed 128 characters. 本機暫存程序的完整名稱 (包括 #) 不能超過 116 個字元。The complete name for a local temporary procedure, including #, cannot exceed 116 characters.

; number; number
適用於SQL Server 2008SQL Server 2008SQL Server 2017SQL Server 2017 以及 Azure SQL DatabaseAzure SQL DatabaseApplies to: SQL Server 2008SQL Server 2008 through SQL Server 2017SQL Server 2017 and Azure SQL DatabaseAzure SQL Database.

用來將同名程序分組的選擇性整數。An optional integer that is used to group procedures of the same name. 您可以利用一個 DROP PROCEDURE 陳述式一併卸除這些分組的程序。These grouped procedures can be dropped together by using one DROP PROCEDURE statement.

注意

這項功能處於維護模式,並可能在 Microsoft SQL Server 的未來版本中移除。This feature is in maintenance mode and may be removed in a future version of Microsoft SQL Server. 請避免在新的開發工作中使用這項功能,並規劃修改目前使用這項功能的應用程式。Avoid using this feature in new development work, and plan to modify applications that currently use this feature.

編號程序無法使用 xml 或 CLR 使用者定義的類型,亦無法用於計劃指南。Numbered procedures cannot use the xml or CLR user-defined types and cannot be used in a plan guide.

@ parameter@ parameter
在程序中宣告的參數。A parameter declared in the procedure. 將 at 記號 ( @ ) 作為第一個字元來指定參數名稱。Specify a parameter name by using the at sign (@) as the first character. 參數名稱必須符合識別碼的規則。The parameter name must comply with the rules for identifiers. 對程序而言,參數必須是本機參數;相同的參數名稱可以用在其他程序中。Parameters are local to the procedure; the same parameter names can be used in other procedures.

您可以宣告一個或多個參數,最大值為 2,100。One or more parameters can be declared; the maximum is 2,100. 除非定義了參數的預設值或將值設為等於其他參數,否則,在呼叫程序時,使用者必須提供每個已宣告參數的值。The value of each declared parameter must be supplied by the user when the procedure is called unless a default value for the parameter is defined or the value is set to equal another parameter. 如果程序包含資料表值參數,但呼叫中缺少此參數,則會傳入空白資料表。If a procedure contains table-valued parameters, and the parameter is missing in the call, an empty table is passed in. 參數只可取代常數運算式,而無法取代資料表名稱、資料行名稱或其他資料庫物件的名稱。Parameters can take the place only of constant expressions; they cannot be used instead of table names, column names, or the names of other database objects. 如需詳細資訊,請參閱 EXECUTE (Transact-SQL)For more information, see EXECUTE (Transact-SQL).

如果指定了 FOR REPLICATION,就不能宣告參數。Parameters cannot be declared if FOR REPLICATION is specified.

[ type_schema_name .[ type_schema_name. ] data_type] data_type
參數資料類型及該資料類型所屬的結構描述。The data type of the parameter and the schema to which the data type belongs.

Transact-SQLTransact-SQL 程序的指導方針Guidelines for Transact-SQLTransact-SQL procedures:

  • 所有 Transact-SQLTransact-SQL 資料類型都可以作為參數使用。All Transact-SQLTransact-SQL data types can be used as parameters.

  • 您可以使用使用者定義資料表類型建立資料表值參數。You can use the user-defined table type to create table-valued parameters. 資料表值參數只能是 INPUT 參數,而且必須與 READONLY 關鍵字一起使用。Table-valued parameters can only be INPUT parameters and must be accompanied by the READONLY keyword. 如需詳細資訊,請參閱使用資料表值參數 (資料庫引擎)For more information, see Use Table-Valued Parameters (Database Engine)

  • cursor 資料類型只能是 OUTPUT 參數,而且必須與 VARYING 關鍵字一起使用。cursor data types can only be OUTPUT parameters and must be accompanied by the VARYING keyword.

CLR 程序的指導方針Guidelines for CLR procedures:

  • 在 Managed 程式碼中具有對等類型的所有原生 SQL ServerSQL Server 資料類型都可以當做參數使用。All of the native SQL ServerSQL Server data types that have an equivalent in managed code can be used as parameters. 如需 CLR 類型與 SQL ServerSQL Server 系統資料類型之間的對應詳細資訊,請參閱對應 CLR 參數資料For more information about the correspondence between CLR types and SQL ServerSQL Server system data types, see Mapping CLR Parameter Data. 如需 SQL ServerSQL Server 系統資料類型及其語法的詳細資訊,請參閱資料類型 (Transact-SQL)For more information about SQL ServerSQL Server system data types and their syntax, see Data Types (Transact-SQL).

  • 資料表值或 cursor 資料類型都無法作為參數使用。Table-valued or cursor data types cannot be used as parameters.

  • 如果參數的資料類型是 CLR 使用者定義型別,您必須具有在該類型上的 EXECUTE 權限。If the data type of the parameter is a CLR user-defined type, you must have EXECUTE permission on the type.

VARYINGVARYING
指定支援做為輸出參數的結果集。Specifies the result set supported as an output parameter. 這個參數由程序動態建構,可能會有不同的內容。This parameter is dynamically constructed by the procedure and its contents may vary. 只適用於 cursor 參數。Applies only to cursor parameters. 這個選項不適用於 CLR 程序。This option is not valid for CLR procedures.

defaultdefault
參數的預設值。A default value for a parameter. 如果定義了參數的預設值,不必指定該參數的值就可以執行程序。If a default value is defined for a parameter, the procedure can be executed without specifying a value for that parameter. 預設值必須是常數,或者,可以是 NULL。The default value must be a constant or it can be NULL. 常數值可以採用萬用字元格式,讓您可以在將參數傳入程序時使用 LIKE 關鍵字。The constant value can be in the form of a wildcard, making it possible to use the LIKE keyword when passing the parameter into the procedure.

sys.parameters.default 資料行中只會記錄 CLR 程序的預設值。Default values are recorded in the sys.parameters.default column only for CLR procedures. 如果是 Transact-SQLTransact-SQL 程序參數,該資料行為 NULL。That column is NULL for Transact-SQLTransact-SQL procedure parameters.

OUT | OUTPUTOUT | OUTPUT
指出這個參數是輸出參數。Indicates that the parameter is an output parameter. 您可以利用 OUTPUT 參數將值傳回程序的呼叫者。Use OUTPUT parameters to return values to the caller of the procedure. 除非程序是 CLR 程序,否則 textntextimage 參數無法作為 OUTPUT 參數使用。text, ntext, and image parameters cannot be used as OUTPUT parameters, unless the procedure is a CLR procedure. 除非此程序是一個 CLR 程序,否則輸出參數可以當做資料指標預留位置使用。An output parameter can be a cursor placeholder, unless the procedure is a CLR procedure. 資料表值資料類型無法指定為程序的 OUTPUT 參數。A table-value data type cannot be specified as an OUTPUT parameter of a procedure.

READONLYREADONLY
指示無法在程序的主體內更新或修改參數。Indicates that the parameter cannot be updated or modified within the body of the procedure. 如果參數類型是資料表值類型,就必須指定 READONLY。If the parameter type is a table-value type, READONLY must be specified.

RECOMPILERECOMPILE
指出 Database EngineDatabase Engine 不會快取這個程序的查詢計劃,以強制在每次執行該程序時進行編譯。Indicates that the Database EngineDatabase Engine does not cache a query plan for this procedure, forcing it to be compiled each time it is executed. 如需為何會強制重新編譯的詳細資訊,請參閱重新編譯預存程序For more information regarding the reasons for forcing a recompile, see Recompile a Stored Procedure. 指定 FOR REPLICATION 時,或是 CLR 程序時,無法使用此選項。This option cannot be used when FOR REPLICATION is specified or for CLR procedures.

若要指示 Database EngineDatabase Engine 捨棄程序內個別查詢的查詢計劃,請使用查詢定義中的 RECOMPILE 查詢提示。To instruct the Database EngineDatabase Engine to discard query plans for individual queries inside a procedure, use the RECOMPILE query hint in the definition of the query. 如需詳細資訊,請參閱查詢提示 (Transact-SQL)For more information, see Query Hints (Transact-SQL).

ENCRYPTIONENCRYPTION
適用於:SQL Server (SQL Server 2008SQL Server 2008SQL Server 2017SQL Server 2017)、Azure SQL DatabaseAzure SQL DatabaseApplies to: SQL Server ( SQL Server 2008SQL Server 2008 through SQL Server 2017SQL Server 2017), Azure SQL DatabaseAzure SQL Database.

指出 SQL ServerSQL Server 會將 CREATE PROCEDURE 陳述式的原始文字轉換為模糊化格式。Indicates that SQL ServerSQL Server converts the original text of the CREATE PROCEDURE statement to an obfuscated format. SQL ServerSQL Server 中,無法直接從任何目錄檢視中看見混亂格式的輸出。The output of the obfuscation is not directly visible in any of the catalog views in SQL ServerSQL Server. 對系統資料表或資料庫檔案沒有存取權的使用者無法擷取模糊化的文字。Users who have no access to system tables or database files cannot retrieve the obfuscated text. 不過,如果是特殊權限使用者 (可以透過 DAC 通訊埠存取系統資料表,或直接存取資料庫檔案),則可使用該文字。However, the text is available to privileged users who can either access system tables over the DAC port or directly access database files. 另外,可將偵錯工具附加至伺服器處理序的使用者,還可以在執行階段從記憶體擷取解密程序。Also, users who can attach a debugger to the server process can retrieve the decrypted procedure from memory at runtime. 如需如何存取系統中繼資料的詳細資訊,請參閱中繼資料可見性組態For more information about accessing system metadata, see Metadata Visibility Configuration.

這個選項不適用於 CLR 程序。This option is not valid for CLR procedures.

使用這個選項建立的程序,不能發行為 SQL ServerSQL Server 複寫的一部分。Procedures created with this option cannot be published as part of SQL ServerSQL Server replication.

EXECUTE AS 子句EXECUTE AS clause
指定執行程序時所在的安全性內容。Specifies the security context under which to execute the procedure.

針對原生編譯的預存程序,從 SQL Server 2016 (13.x)SQL Server 2016 (13.x) 起和 Azure SQL DatabaseAzure SQL Database 的 EXECUTE AS 子句均無限制。For natively compiled stored procedures, starting SQL Server 2016 (13.x)SQL Server 2016 (13.x) and in Azure SQL DatabaseAzure SQL Database, there are no limitations on the EXECUTE AS clause. SQL Server 2014 (12.x)SQL Server 2014 (12.x) 中,SELF、OWNER 和 'user_name' 子句均支援原生編譯的預存程序。In SQL Server 2014 (12.x)SQL Server 2014 (12.x) the SELF, OWNER, and 'user_name' clauses are supported with natively compiled stored procedures.

如需詳細資訊,請參閱 EXECUTE AS 子句 (Transact-SQL)For more information, see EXECUTE AS Clause (Transact-SQL).

FOR REPLICATIONFOR REPLICATION
適用於:SQL Server (SQL Server 2008SQL Server 2008SQL Server 2017SQL Server 2017)、Azure SQL DatabaseAzure SQL DatabaseApplies to: SQL Server ( SQL Server 2008SQL Server 2008 through SQL Server 2017SQL Server 2017), Azure SQL DatabaseAzure SQL Database.

指定針對複寫建立的程序。Specifies that the procedure is created for replication. 因此無法針對訂閱者執行該程序。Consequently, it cannot be executed on the Subscriber. 利用 FOR REPLICATION 選項建立的程序會當做程序篩選來使用,而且只有在複寫期間才會執行它。A procedure created with the FOR REPLICATION option is used as a procedure filter and is executed only during replication. 如果指定了 FOR REPLICATION,就不能宣告參數。Parameters cannot be declared if FOR REPLICATION is specified. 無法為 CLR 程序指定 FOR REPLICATION。FOR REPLICATION cannot be specified for CLR procedures. 使用 FOR REPLICATION 建立的程序,會忽略 RECOMPILE 選項。The RECOMPILE option is ignored for procedures created with FOR REPLICATION.

FOR REPLICATION 程序在 sys.objectssys.procedures 中具有 RF 物件類型。A FOR REPLICATION procedure has an object type RF in sys.objects and sys.procedures.

{ [ BEGIN ] sql_statement [;] [ ...n ] [ END ] }{ [ BEGIN ] sql_statement [;] [ ...n ] [ END ] }
包含程序主體的一個或多個 Transact-SQLTransact-SQL 陳述式。One or more Transact-SQLTransact-SQL statements comprising the body of the procedure. 您可以使用選用的 BEGIN 和 END 關鍵字來括住陳述式。You can use the optional BEGIN and END keywords to enclose the statements. 如需詳細資訊,請參閱以下的<最佳作法>、<一般備註>以及<限制事項>這幾節。For information, see the Best Practices, General Remarks, and Limitations and Restrictions sections that follow.

EXTERNAL NAME assembly_name . class_name . method_nameEXTERNAL NAME assembly_name.class_name.method_name
適用對象SQL Server 2008SQL Server 2008SQL Server 2017SQL Server 2017SQL DatabaseSQL DatabaseApplies to: SQL Server 2008SQL Server 2008 through SQL Server 2017SQL Server 2017, SQL DatabaseSQL Database.

指定 CLR 程序所要參考之 .NET Framework.NET Framework 組件的方法。Specifies the method of a .NET Framework.NET Framework assembly for a CLR procedure to reference. class_name 必須是有效的 SQL ServerSQL Server 識別碼,且必須是組件中的類別。class_name must be a valid SQL ServerSQL Server identifier and must exist as a class in the assembly. 如果該類別具有命名空間限定的名稱,且該名稱使用句號 ( . ) 來分隔命名空間的各個部分,您就必須使用方括弧 ( [] ) 或引號 ( "" ) 來分隔類別名稱。If the class has a namespace-qualified name that uses a period (.) to separate namespace parts, the class name must be delimited by using brackets ([]) or quotation marks (""). 指定的方法必須是類別的靜態方法。The specified method must be a static method of the class.

依預設,SQL ServerSQL Server 不能執行 CLR 程式碼。By default, SQL ServerSQL Server cannot execute CLR code. 您可以建立、修改和卸除參考通用語言執行平台模組的資料庫物件;不過,必須等到您啟用 clr enabled 選項之後,才能在 SQL ServerSQL Server 中執行這些參考。You can create, modify, and drop database objects that reference common language runtime modules; however, you cannot execute these references in SQL ServerSQL Server until you enable the clr enabled option. 若要啟用這個選項,請使用 sp_configureTo enable the option, use sp_configure.

注意

自主資料庫不支援 CLR 程序。CLR procedures are not supported in a contained database.

ATOMIC WITHATOMIC WITH
適用於SQL Server 2014 (12.x)SQL Server 2014 (12.x)SQL Server 2017SQL Server 2017 以及 Azure SQL DatabaseAzure SQL DatabaseApplies to: SQL Server 2014 (12.x)SQL Server 2014 (12.x) through SQL Server 2017SQL Server 2017 and Azure SQL DatabaseAzure SQL Database.

表示不可部分完成的預存程序執行。Indicates atomic stored procedure execution. 變更會全部認可或透過擲回例外狀況全部回復。Changes are either committed or all of the changes rolled back by throwing an exception. 原生編譯預存程序需要 ATOMIC WITH 區塊。The ATOMIC WITH block is required for natively compiled stored procedures.

如果此程序傳回 (透過 RETURN 陳述式明確傳回或透過完成執行隱含傳回),則會認可程序所執行的工作。If the procedure RETURNs (explicitly through the RETURN statement, or implicitly by completing execution), the work performed by the procedure is committed. 如果程序擲回,則會回復程序所執行的工作。If the procedure THROWs, the work performed by the procedure is rolled back.

不可部分完成區塊內的 XACT_ABORT 預設為 ON,且無法變更。XACT_ABORT is ON by default inside an atomic block and cannot be changed. XACT_ABORT 指定當 SQL ServerSQL Server 陳述式產生執行階段錯誤時,Transact-SQLTransact-SQL 是否自動回復目前的交易。XACT_ABORT specifies whether SQL ServerSQL Server automatically rolls back the current transaction when a Transact-SQLTransact-SQL statement raises a run-time error.

ATOMIC 區塊中的下列 SET 選項永遠是 ON,選項無法變更。The following SET options are always ON in the ATOMIC block; the options cannot be changed.

  • CONCAT_NULL_YIELDS_NULLCONCAT_NULL_YIELDS_NULL
  • QUOTED_IDENTIFIER, ARITHABORTQUOTED_IDENTIFIER, ARITHABORT
  • NOCOUNTNOCOUNT
  • ANSI_NULLSANSI_NULLS
  • ANSI_WARNINGSANSI_WARNINGS

ATOMIC 區塊內的 SET 選項無法變更。SET options cannot be changed inside ATOMIC blocks. 在原生編譯預存程序的範圍中不使用使用者工作階段的 SET 選項。The SET options in the user session are not used in the scope of natively compiled stored procedures. 這些選項在編譯時間是固定的。These options are fixed at compile time.

BEGIN、ROLLBACK 和 COMMIT 作業無法使用於不可部分完成區塊內。BEGIN, ROLLBACK, and COMMIT operations cannot be used inside an atomic block.

在程序的外部範圍,每個原生編譯預存程序有一個 ATOMIC 區塊。There is one ATOMIC block per natively compiled stored procedure, at the outer scope of the procedure. 區塊不可以是巢狀的。The blocks cannot be nested. 如需 ATOMIC 區塊的詳細資訊,請參閱原生編譯的預存程序For more information about atomic blocks, see Natively Compiled Stored Procedures.

NULL | NOT NULLNULL | NOT NULL
判斷參數中是否允許 Null 值。Determines whether null values are allowed in a parameter. 預設值是 NULL。NULL is the default.

NATIVE_COMPILATIONNATIVE_COMPILATION
適用於SQL Server 2014 (12.x)SQL Server 2014 (12.x)SQL Server 2017SQL Server 2017 以及 Azure SQL DatabaseAzure SQL DatabaseApplies to: SQL Server 2014 (12.x)SQL Server 2014 (12.x) through SQL Server 2017SQL Server 2017 and Azure SQL DatabaseAzure SQL Database.

表示程序是原生編譯的。Indicates that the procedure is natively compiled. NATIVE_COMPILATION、SCHEMABINDING 和 EXECUTE AS 可以依照任何順序來指定。NATIVE_COMPILATION, SCHEMABINDING, and EXECUTE AS can be specified in any order. 如需詳細資訊,請參閱原生編譯的預存程序For more information, see Natively Compiled Stored Procedures.

SCHEMABINDINGSCHEMABINDING
適用於SQL Server 2014 (12.x)SQL Server 2014 (12.x)SQL Server 2017SQL Server 2017 以及 Azure SQL DatabaseAzure SQL DatabaseApplies to: SQL Server 2014 (12.x)SQL Server 2014 (12.x) through SQL Server 2017SQL Server 2017 and Azure SQL DatabaseAzure SQL Database.

確定程序所參考的資料表無法卸除或改變。Ensures that tables that are referenced by a procedure cannot be dropped or altered. 原生編譯預存程序需要 SCHEMABINDING。SCHEMABINDING is required in natively compiled stored procedures. (如需詳細資訊,請參閱原生編譯的預存程序。)SCHEMABINDING 限制和使用者定義函式的相關限制相同。(For more information, see Natively Compiled Stored Procedures.) The SCHEMABINDING restrictions are the same as they are for user-defined functions. 如需詳細資訊,請參閱 CREATE FUNCTION (Transact-SQL) 中的<SCHEMABINDING>一節。For more information, see the SCHEMABINDING section in CREATE FUNCTION (Transact-SQL).

LANGUAGE = [N] 'language'LANGUAGE = [N] 'language'
適用於SQL Server 2014 (12.x)SQL Server 2014 (12.x)SQL Server 2017SQL Server 2017 以及 Azure SQL DatabaseAzure SQL DatabaseApplies to: SQL Server 2014 (12.x)SQL Server 2014 (12.x) through SQL Server 2017SQL Server 2017 and Azure SQL DatabaseAzure SQL Database.

相當於 SET LANGUAGE (Transact-SQL) 工作階段選項。Equivalent to SET LANGUAGE (Transact-SQL) session option. 需要 LANGUAGE = [N] 'language'。LANGUAGE = [N] 'language' is required.

TRANSACTION ISOLATION LEVELTRANSACTION ISOLATION LEVEL
適用於SQL Server 2014 (12.x)SQL Server 2014 (12.x)SQL Server 2017SQL Server 2017 以及 Azure SQL DatabaseAzure SQL DatabaseApplies to: SQL Server 2014 (12.x)SQL Server 2014 (12.x) through SQL Server 2017SQL Server 2017 and Azure SQL DatabaseAzure SQL Database.

原生編譯預存程序所需的。Required for natively compiled stored procedures. 指定預存程序的交易隔離等級。Specifies the transaction isolation level for the stored procedure. 選項如下:The options are as follows:

如需這些選項的詳細資訊,請參閱 SET TRANSACTION ISOLATION LEVEL (Transact-SQL)For more information about these options, see SET TRANSACTION ISOLATION LEVEL (Transact-SQL).

REPEATABLE READREPEATABLE READ
指定陳述式不能讀取其他交易已修改而尚未認可的資料。Specifies that statements cannot read data that has been modified but not yet committed by other transactions. 如果其他交易對目前交易已讀取的資料進行修改,則目前交易會失敗。If another transaction modifies data that has been read by the current transaction, the current transaction fails.

SERIALIZABLESERIALIZABLE
指定下列項目:Specifies the following:

  • 陳述式無法讀取其他交易已修改但尚未認可的資料。Statements cannot read data that has been modified but not yet committed by other transactions.
  • 如果其他交易對目前交易已讀取的資料進行修改,則目前交易會失敗。If another transactions modifies data that has been read by the current transaction, the current transaction fails.
  • 如果其他交易插入了新資料列,且其索引鍵值落在目前交易之任何陳述式已讀取的索引鍵範圍中,則目前交易會失敗。If another transaction inserts new rows with key values that would fall in the range of keys read by any statements in the current transaction, the current transaction fails.

SNAPSHOTSNAPSHOT
指定交易中任何陳述式所讀取的資料都是交易一致性版本,這些資料從交易開始時就已存在。Specifies that data read by any statement in a transaction is the transactionally consistent version of the data that existed at the start of the transaction.

DATEFIRST = numberDATEFIRST = number
適用於SQL Server 2014 (12.x)SQL Server 2014 (12.x)SQL Server 2017SQL Server 2017 以及 Azure SQL DatabaseAzure SQL DatabaseApplies to: SQL Server 2014 (12.x)SQL Server 2014 (12.x) through SQL Server 2017SQL Server 2017 and Azure SQL DatabaseAzure SQL Database.

將每週的第一天指定為 1 到 7 的數字。Specifies the first day of the week to a number from 1 through 7. DATEFIRST 是選擇性的。DATEFIRST is optional. 如果未指定,則會從指定的語言來推斷設定。If it is not specified, the setting is inferred from the specified language.

如需詳細資訊,請參閱 SET DATEFIRST (Transact-SQL)For more information, see SET DATEFIRST (Transact-SQL).

DATEFORMAT = formatDATEFORMAT = format
適用於SQL Server 2014 (12.x)SQL Server 2014 (12.x)SQL Server 2017SQL Server 2017 以及 Azure SQL DatabaseAzure SQL DatabaseApplies to: SQL Server 2014 (12.x)SQL Server 2014 (12.x) through SQL Server 2017SQL Server 2017 and Azure SQL DatabaseAzure SQL Database.

指定解譯 date、smalldatetime、datetime、datetime2 和 datetimeoffset 字元字串之月份、日期與年份日期部分的順序。Specifies the order of the month, day, and year date parts for interpreting date, smalldatetime, datetime, datetime2 and datetimeoffset character strings. DATEFORMAT 是選擇性的。DATEFORMAT is optional. 如果未指定,則會從指定的語言來推斷設定。If it is not specified, the setting is inferred from the specified language.

如需詳細資訊,請參閱 SET DATEFIRST (Transact-SQL)For more information, see SET DATEFORMAT (Transact-SQL).

DELAYED_DURABILITY = { OFF | ON }DELAYED_DURABILITY = { OFF | ON }
適用於SQL Server 2014 (12.x)SQL Server 2014 (12.x)SQL Server 2017SQL Server 2017 以及 Azure SQL DatabaseAzure SQL DatabaseApplies to: SQL Server 2014 (12.x)SQL Server 2014 (12.x) through SQL Server 2017SQL Server 2017 and Azure SQL DatabaseAzure SQL Database.

SQL ServerSQL Server 交易認可可能是完全持久、預設值或延遲的持久。transaction commits can be either fully durable, the default, or delayed durable.

如需詳細資訊,請參閱控制交易持久性For more information, see Control Transaction Durability.

簡單範例Simple Examples

為了協助您開始使用,以下是兩個簡單的範例:To help you get started, here are two quick examples:
SELECT DB_NAME() AS ThisDB; 會傳回目前資料庫的名稱。SELECT DB_NAME() AS ThisDB; returns the name of the current database.
您可以將該陳述式包裝在預存程序中,例如:You can wrap that statement in a stored procedure, such as:

CREATE PROC What_DB_is_this     
AS   
SELECT DB_NAME() AS ThisDB; 

使用下列陳述式呼叫預存程序:EXEC What_DB_is_this;Call the store procedure with statement: EXEC What_DB_is_this;

稍微複雜一點的方法,是提供讓程序更有彈性的輸入參數。Slightly more complex, is to provide an input parameter to make the procedure more flexible. 例如:For example:

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

在您呼叫程序時,提供資料庫識別碼。Provide a database id number when you call the procedure. 例如 EXEC What_DB_is_that 2; 會傳回 tempdbFor example, EXEC What_DB_is_that 2; returns tempdb.

如需更多範例,請參閱本主題結尾的範例See Examples towards the end of this topic for many more examples.

最佳作法Best Practices

雖然這不是最詳盡的最佳作法清單,但這些建議可能會改善程序效能。Although this is not an exhaustive list of best practices, these suggestions may improve procedure performance.

  • 使用 SET NOCOUNT ON 陳述式做為程序主體中的第一個陳述式。Use the SET NOCOUNT ON statement as the first statement in the body of the procedure. 亦即,將該陳述式放在 AS 關鍵字正後方。That is, place it just after the AS keyword. 這樣會關閉 SQL ServerSQL Server 在執行任何 SELECT、INSERT、UPDATE、MERGE 和 DELETE 陳述式之後,傳回用戶端的訊息。This turns off messages that SQL ServerSQL Server sends back to the client after any SELECT, INSERT, UPDATE, MERGE, and DELETE statements are executed. 這樣能讓所產生的輸出為最小,以保持清晰。This keeps the output generated to a minumum for clarity. 但在現今的硬體上,沒有可測量的效能優勢。There is no measurable performance benefit however on todays hardware. 如需詳細資訊,請參閱 SET NOCOUNT (Transact-SQL)For information, see SET NOCOUNT (Transact-SQL).

  • 建立或參考程序中的資料庫物件時,請使用結構描述名稱。Use schema names when creating or referencing database objects in the procedure. 如果 Database EngineDatabase Engine 不必搜尋多個結構描述,解析物件名稱所需的處理時間會較少。It takes less processing time for the Database EngineDatabase Engine to resolve object names if it does not have to search multiple schemas. 它也能防止在不指定結構描述的情況下建立物件時,因指派使用者預設結構描述所引起的權限和存取問題。It also prevents permission and access problems caused by a user's default schema being assigned when objects are created without specifying the schema.

  • 避免在 WHERE 和 JOIN 子句中指定的資料行周圍使用包裝函數。Avoid wrapping functions around columns specified in the WHERE and JOIN clauses. 這麼做會使資料行變成非決定性,而且會使查詢處理器無法使用索引。Doing so makes the columns non-deterministic and prevents the query processor from using indexes.

  • 避免在 SELECT 陳述式中使用傳回許多資料列的純量函數。Avoid using scalar functions in SELECT statements that return many rows of data. 純量函數必須套用到每個資料列,因此,所產生的行為類似以資料列為主的處理,而且會降低效能。Because the scalar function must be applied to every row, the resulting behavior is like row-based processing and degrades performance.

  • 請避免使用 SELECT *Avoid the use of SELECT *. 請改為指定所需的資料行名稱。Instead, specify the required column names. 這樣可以防止停止程序執行的部分 Database EngineDatabase Engine 錯誤。This can prevent some Database EngineDatabase Engine errors that stop procedure execution. 例如,假設 SELECT * 陳述式會從 12 欄式資料表傳回資料並將該資料插入 12 欄式暫存資料表,那麼,直到資料表中資料行的數目或順序變更時,該陳述式才會成功。For example, a SELECT * statement that returns data from a 12 column table and then inserts that data into a 12 column temporary table succeeds until the number or order of columns in either table is changed.

  • 請避免處理或傳回太多資料。Avoid processing or returning too much data. 盡早將程序程式碼中的結果範圍縮小,讓該程序所執行的所有後續作業都可以使用最小的資料集完成。Narrow the results as early as possible in the procedure code so that any subsequent operations performed by the procedure are done using the smallest data set possible. 只將基本資料傳送到用戶端應用程式。Send just the essential data to the client application. 此作法比透過網路傳送額外資料並強制用戶端應用程式處理過大的結果集更有效率。It is more efficient than sending extra data across the network and forcing the client application to work through unnecessarily large result sets.

  • 透過 BEGIN/COMMIT TRANSACTION 使用明確的交易,並讓交易越短越好。Use explicit transactions by using BEGIN/COMMIT TRANSACTION and keep transactions as short as possible. 交易越久表示記錄鎖定越久,而且發生死結的可能性也就越大。Longer transactions mean longer record locking and a greater potential for deadlocking.

  • 請針對程序內部的錯誤處理使用 Transact-SQLTransact-SQL TRY...CATCH 功能。Use the Transact-SQLTransact-SQL TRY...CATCH feature for error handling inside a procedure. TRY...CATCH 可以封裝 Transact-SQLTransact-SQL 陳述式的整個區塊。TRY...CATCH can encapsulate an entire block of Transact-SQLTransact-SQL statements. 這樣不但會使效能負擔較小,而且還會使用更少的程式讓錯誤報告更精確。This not only creates less performance overhead, it also makes error reporting more accurate with significantly less programming.

  • 在程序主體中 CREATE TABLE 或 ALTER TABLE Transact-SQLTransact-SQL 陳述式所參考的所有資料表資料行上使用 DEFAULT 關鍵字。Use the DEFAULT keyword on all table columns that are referenced by CREATE TABLE or ALTER TABLE Transact-SQLTransact-SQL statements in the body of the procedure. 這會防止將 NULL 傳遞到不允許 null 值的資料行。This prevents passing NULL to columns that do not allow null values.

  • 在暫存資料表中的每個資料行使用 NULL 或 NOT NULL。Use NULL or NOT NULL for each column in a temporary table. 當 CREATE TABLE 或 ALTER TABLE 陳述式中沒有指定 NULL 或 NOT NULL 屬性時,ANSI_DFLT_ON 和 ANSI_DFLT_OFF 選項可控制 Database EngineDatabase Engine 將這些屬性指派給資料行的方式。The ANSI_DFLT_ON and ANSI_DFLT_OFF options control the way the Database EngineDatabase Engine assigns the NULL or NOT NULL attributes to columns when these attributes are not specified in a CREATE TABLE or ALTER TABLE statement. 如果某個連接執行程序時所用的選項設定,不同於建立程序的連接所用設定,針對第二個連接建立的資料表資料行,可以有不同的 Null 屬性,且可以展現不同的行為。If a connection executes a procedure with different settings for these options than the connection that created the procedure, the columns of the table created for the second connection can have different nullability and exhibit different behavior. 如果針對每個資料行明確陳述 NULL 或 NOT NULL,就會利用執行程序之所有連接的相同 Null 屬性來建立暫存資料表。If NULL or NOT NULL is explicitly stated for each column, the temporary tables are created by using the same nullability for all connections that execute the procedure.

  • 使用轉換 null 的修改陳述式,然後使用查詢中的 null 值加入排除資料列的邏輯。Use modification statements that convert nulls and include logic that eliminates rows with null values from queries. 請注意,在 Transact-SQLTransact-SQL 中,NULL 不是空值或 "nothing" 值。Be aware that in Transact-SQLTransact-SQL, NULL is not an empty or "nothing" value. 它是未知值的預留位置,而且可能造成非預期的行為,特別是在查詢結果集或使用 AGGREGATE 函數時。It is a placeholder for an unknown value and can cause unexpected behavior, especially when querying for result sets or using AGGREGATE functions.

  • 除非不同的值有特定需要,否則請使用 UNION ALL 運算子代替 UNION 或 OR 運算子。Use the UNION ALL operator instead of the UNION or OR operators, unless there is a specific need for distinct values. UNION ALL 運算子需要的處理負擔較少,因為不會從結果集中篩選出重複項目。The UNION ALL operator requires less processing overhead because duplicates are not filtered out of the result set.

一般備註General Remarks

程序沒有預先定義的大小上限。There is no predefined maximum size of a procedure.

程序中指定的變數可以是使用者自訂變數或系統變數,如 @@SPID。Variables specified in the procedure can be user-defined or system variables, such as @@SPID.

第一次執行程序時,會編譯它來決定擷取資料的最佳存取計畫。When a procedure is executed for the first time, it is compiled to determine an optimal access plan to retrieve the data. 如果已經產生的計畫仍保留在 Database EngineDatabase Engine 的計畫快取中,則程序的後續執行作業可以重複使用該計畫。Subsequent executions of the procedure may reuse the plan already generated if it still remains in the plan cache of the Database EngineDatabase Engine.

SQL ServerSQL Server 啟動時,會自動執行一個或多個程序。One or more procedures can execute automatically when SQL ServerSQL Server starts. 這些程序必須由 master 資料庫中的系統管理員建立,且必須在 sysadmin 固定伺服器角色下以背景處理序執行。The procedures must be created by the system administrator in the master database and executed under the sysadmin fixed server role as a background process. 這些程序不可以有任何輸入或輸出參數。The procedures cannot have any input or output parameters. 如需詳細資訊,請參閱執行預存程序For more information, see Execute a Stored Procedure.

當某個程序參考 CLR 常式、類型或彙總,藉以呼叫其他程序或執行 Managed 程式碼時,這些程序即稱為巢狀程序。Procedures are nested when one procedure call another or executes managed code by referencing a CLR routine, type, or aggregate. 程序和 Managed 程式碼參考的巢狀結構最多可有 32 個層級。Procedures and managed code references can be nested up to 32 levels. 當被呼叫的程序或 Managed 程式碼參考開始執行時,巢狀層級會加一;當被呼叫的程序或 Managed 程式碼參考執行完畢時,巢狀層級會減一。The nesting level increases by one when the called procedure or managed code reference begins execution and decreases by one when the called procedure or managed code reference completes execution. 從 Managed 程式碼內部叫用的方法不受巢狀層級的限制。Methods invoked from within the managed code do not count against the nesting level limit. 但當 CLR 預存程序透過 SQL Server Managed 提供者執行資料存取作業時,會在 Managed 程式碼轉換成 SQL 的過程中,加入一個額外的巢狀層級。However, when a CLR stored procedure performs data access operations through the SQL Server managed provider, an additional nesting level is added in the transition from managed code to SQL.

企圖超越最大巢狀層級將會導致整個呼叫鏈結失敗。Attempting to exceed the maximum nesting level causes the entire calling chain to fail. 您可以使用 @@NESTLEVEL 函數傳回目前預存程序執行的巢狀層級。You can use the @@NESTLEVEL function to return the nesting level of the current stored procedure execution.

互通性Interoperability

當建立或修改 Database EngineDatabase Engine 程序時,Transact-SQLTransact-SQL 會將 SET QUOTED_IDENTIFIER 和 SET ANSI_NULLS 的設定一併儲存。The Database EngineDatabase Engine saves the settings of both SET QUOTED_IDENTIFIER and SET ANSI_NULLS when a Transact-SQLTransact-SQL procedure is created or modified. 這些原始設定是在執行程序時使用的。These original settings are used when the procedure is executed. 因此,當程序正在執行時,SET QUOTED_IDENTIFIER 和 SET ANSI_NULLS 的任何用戶端工作階段設定,都會被忽略。Therefore, any client session settings for SET QUOTED_IDENTIFIER and SET ANSI_NULLS are ignored when the procedure is running.

當建立或修改程序時,不會儲存其他 SET 選項,例如:SET ARITHABORT、SET ANSI_WARNINGS 或 SET ANSI_PADDINGS。Other SET options, such as SET ARITHABORT, SET ANSI_WARNINGS, or SET ANSI_PADDINGS are not saved when a procedure is created or modified. 如果程序的邏輯取決於特定設定,請在程序的開頭併入 SET 陳述式,以確保可以有適當的設定。If the logic of the procedure depends on a particular setting, include a SET statement at the start of the procedure to guarantee the appropriate setting. 如果從程序中執行 SET 陳述式,設定的有效性只能維持到程序完成執行。When a SET statement is executed from a procedure, the setting remains in effect only until the procedure has finished running. 之後,該設定就會還原為程序被呼叫時所具有的值。The setting is then restored to the value the procedure had when it was called. 這可讓個別用戶端設定本身想要的選項,而不影響程序的邏輯。This enables individual clients to set the options they want without affecting the logic of the procedure.

除了 SET SHOWPLAN_TEXT 和 SET SHOWPLAN_ALL 以外,其他所有 SET 陳述式都可以在程序中指定。Any SET statement can be specified inside a procedure, except SET SHOWPLAN_TEXT and SET SHOWPLAN_ALL. 這些陳述式必須是批次中唯一的陳述式。These must be the only statements in the batch. 所選 SET 選項在程序執行期間仍然有效,然後會還原為先前的設定。The SET option chosen remains in effect during the execution of the procedure and then reverts to its former setting.

注意

當在程序或使用者定義函數中傳遞參數時,或在批次陳述式中宣告和設定變數時,未接受 SET ANSI_WARNINGS。SET ANSI_WARNINGS is not honored when passing parameters in a procedure, user-defined function, or when declaring and setting variables in a batch statement. 例如,如果將變數定義為 char(3) ,然後設定為大於三個字元的值,資料就會被截斷成定義的大小,而 INSERT 或 UPDATE 陳述式會執行成功。For example, if a variable is defined as char(3), and then set to a value larger than three characters, the data is truncated to the defined size and the INSERT or UPDATE statement succeeds.

限制事項Limitations and Restrictions

CREATE PROCEDURE 陳述式無法在單一批次中,與其他 Transact-SQLTransact-SQL 陳述式結合起來。The CREATE PROCEDURE statement cannot be combined with other Transact-SQLTransact-SQL statements in a single batch.

下列陳述式無法在預存程序主體中的任何位置使用。The following statements cannot be used anywhere in the body of a stored procedure.

CREATE AGGREGATECREATE AGGREGATE CREATE SCHEMACREATE SCHEMA SET SHOWPLAN_TEXTSET SHOWPLAN_TEXT
CREATE DEFAULTCREATE DEFAULT CREATE 或 ALTER TRIGGERCREATE or ALTER TRIGGER SET SHOWPLAN_XMLSET SHOWPLAN_XML
CREATE 或 ALTER FUNCTIONCREATE or ALTER FUNCTION CREATE 或 ALTER VIEWCREATE or ALTER VIEW USE database_nameUSE database_name
CREATE 或 ALTER PROCEDURECREATE or ALTER PROCEDURE SET PARSEONLYSET PARSEONLY
CREATE RULECREATE RULE SET SHOWPLAN_ALLSET SHOWPLAN_ALL

程序可以參考尚未存在的資料表。A procedure can reference tables that do not yet exist. 在建立時,只會執行語法檢查。At creation time, only syntax checking is performed. 在第一次執行程序之前,不會編譯該程序。The procedure is not compiled until it is executed for the first time. 只有在編譯期間才會解析程序中參考的所有物件。Only during compilation are all objects referenced in the procedure resolved. 因此,即使程序參考了不存在的資料表,仍可在語意正確的情況下順利建立;不過,如果參考的資料表不存在,該程序就會在執行階段時失敗。Therefore, a syntactically correct procedure that references tables that do not exist can be created successfully; however, the procedure fails at execution time if the referenced tables do not exist.

執行程序時,您無法將函數名稱指定為參數預設值或傳遞至參數的值。You cannot specify a function name as a parameter default value or as the value passed to a parameter when executing a procedure. 但您可以變數形式傳遞函數,如下列範例所示。However, you can pass a function as a variable as shown in the following example.

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

如果此程序變更了 SQL ServerSQL Server 的遠端執行個體,將無法回復這些變更。If the procedure makes changes on a remote instance of SQL ServerSQL Server, the changes cannot be rolled back. 遠端程序不會參與交易。Remote procedures do not take part in transactions.

當正確的方法在 .NET Framework 中多載時,若要讓 Database EngineDatabase Engine 參考正確的方法,EXTERNAL NAME 子句中所指定的方法必須具有下列性質:For the Database EngineDatabase Engine to reference the correct method when it is overloaded in the .NET Framework, the method specified in the EXTERNAL NAME clause must have the following characteristics:

  • 宣告為靜態方法。Be declared as a static method.

  • 接收與程序的參數數目相同的參數數目。Receive the same number of parameters as the number of parameters of the procedure.

  • 使用與 SQL ServerSQL Server 程序之相對應參數的資料類型相容的參數類型。Use parameter types that are compatible with the data types of the corresponding parameters of the SQL ServerSQL Server procedure. 如需如何比對 SQL ServerSQL Server 資料類型與 .NET Framework.NET Framework 資料類型的詳細資訊,請參閱對應 CLR 參數資料For information about matching SQL ServerSQL Server data types to the .NET Framework.NET Framework data types, see Mapping CLR Parameter Data.

中繼資料Metadata

下表列出可用於傳回預存程序之詳細資訊的目錄檢視和動態管理檢視。The following table lists the catalog views and dynamic management views that you can use to return information about stored procedures.

檢視View 描述Description
sys.sql_modulessys.sql_modules 傳回 Transact-SQLTransact-SQL 程序的定義。Returns the definition of a Transact-SQLTransact-SQL procedure. 您無法使用 sys.sql_modules 目錄檢視來檢視以 ENCRYPTION 選項建立的程序文字。The text of a procedure created with the ENCRYPTION option cannot be viewed by using the sys.sql_modules catalog view.
sys.assembly_modulessys.assembly_modules 傳回 CLR 程序的詳細資訊。Returns information about a CLR procedure.
sys.parameterssys.parameters 傳回程序所定義之參數的詳細資訊。Returns information about the parameters that are defined in a procedure
sys.sql_expression_dependencies sys.dm_sql_referenced_entities sys.dm_sql_referencing_entitiessys.sql_expression_dependencies sys.dm_sql_referenced_entities sys.dm_sql_referencing_entities 傳回程序所參考的物件。Returns the objects that are referenced by a procedure.

若要預估已編譯的程序大小,請使用下列效能監視器計數器。To estimate the size of a compiled procedure, use the following Performance Monitor Counters.

效能監視器物件名稱Performance Monitor object name 效能監視器計數器名稱Performance Monitor Counter name
SQLServer:計畫快取物件SQLServer: Plan Cache Object Cache Hit RatioCache Hit Ratio
快取頁面Cache Pages
快取物件計數*Cache Object Counts*

* 這些計數器可供各種類別目錄的快取物件使用,包括隨選 Transact-SQLTransact-SQL、已備妥的 Transact-SQLTransact-SQL、程序、觸發程序等等。*These counters are available for various categories of cache objects including ad hoc Transact-SQLTransact-SQL, prepared Transact-SQLTransact-SQL, procedures, triggers, and so on. 如需詳細資訊,請參閱 SQL Server 的 Plan Cache 物件For more information, see SQL Server, Plan Cache Object.

安全性Security

權限Permissions

需要資料庫的 CREATE PROCEDURE 權限,以及要在其中建立程序之結構描述的 ALTER 權限,或者需要 db_ddladmin 固定資料庫角色的成員資格。Requires CREATE PROCEDURE permission in the database and ALTER permission on the schema in which the procedure is being created, or requires membership in the db_ddladmin fixed database role.

如果是 CLR 預存程序,需要 EXTERNAL NAME 子句中所參考組件的擁有權,或該組件的 REFERENCES 權限。For CLR stored procedures, requires ownership of the assembly referenced in the EXTERNAL NAME clause, or REFERENCES permission on that assembly.

CREATE PROCEDURE 和經記憶體最佳化的資料表CREATE PROCEDURE and Memory-Optimized Tables

從傳統和原生編譯的預存程序存取經記憶體最佳化資料表,都可以達到最高效率。Memory-optimized tables can be accessed through both traditional and natively compiled stored procedures. 在大部分情況下,原生程序是更有效率的方式。Native procedures are in most cases the more efficient way. 如需詳細資訊,請參閱原生編譯的預存程序For more information, see Natively Compiled Stored Procedures.

下列範例示範如何建立原生編譯的預存程序,以存取經記憶體最佳化的資料表 dbo.DepartmentsThe following sample shows how to create a natively compiled stored procedure that accesses a memory-optimized table 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 建立的程序,無法更改為原生編譯預存程序。A procedure created without NATIVE_COMPILATION cannot be altered to a natively compiled stored procedure.

如需原生編譯預存程序的可程式性、支援的查詢介面區及運算子的說明,請參閱原生編譯的 T-SQL 模組支援的功能For a discussion of programmability in natively compiled stored procedures, supported query surface area, and operators see Supported Features for Natively Compiled T-SQL Modules.

範例Examples

類別目錄Category 代表性語法元素Featured syntax elements
基本語法Basic Syntax CREATE PROCEDURECREATE PROCEDURE
傳遞參數Passing parameters @parameter
   • = 預設值   • = default
   • 輸出   • OUTPUT
   • 資料表值參數類型   • table-valued parameter type
   • CURSOR VARYING   • CURSOR VARYING
使用預存程序修改資料Modifying data by using a stored procedure UPDATEUPDATE
錯誤處理Error Handling TRY...CATCHTRY...CATCH
模糊化程序定義Obfuscating the procedure definition WITH ENCRYPTIONWITH ENCRYPTION
強制程序重新編譯Forcing the Procedure to Recompile WITH RECOMPILEWITH RECOMPILE
設定安全性內容Setting the Security Context EXECUTE ASEXECUTE AS

基本語法Basic Syntax

本節的範例使用所需的最少語法示範 CREATE PROCEDURE 陳述式的基本功能。Examples in this section demonstrate the basic functionality of the CREATE PROCEDURE statement using the minimum required syntax.

A.A. 建立簡單的 Transact-SQL 程序Creating a simple Transact-SQL procedure

以下範例所建立的預存程序會從 AdventureWorks2012AdventureWorks2012 資料庫中的檢視表傳回所有員工 (所提供的姓氏和名字)、工作職稱及部門名稱。The following example creates a stored procedure that returns all employees (first and last names supplied), their job titles, and their department names from a view in the AdventureWorks2012AdventureWorks2012 database. 這個程序沒有使用任何參數。This procedure does not use any parameters. 其會示範執行程序的三種方法。The example then demonstrates three methods of executing the procedure.

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

您可以利用下列方式執行 uspGetEmployees 程序:The uspGetEmployees procedure can be executed in the following ways:

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

B.B. 傳回一個以上的結果集。Returning more than one result set

下列程序會傳回兩個結果集。The following procedure returns two result sets.

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

C.C. 建立 CLR 預存程序Creating a CLR stored procedure

下列範例會建立 GetPhotoFromDB 程序,以參考 HandlingLOBUsingCLR 組件中 LargeObjectBinary 類別的 GetPhotoFromDB 方法。The following example creates the GetPhotoFromDB procedure that references the GetPhotoFromDB method of the LargeObjectBinary class in the HandlingLOBUsingCLR assembly. 建立程序之前,會先在本機資料庫中註冊 HandlingLOBUsingCLR 組件。Before the procedure is created, the HandlingLOBUsingCLR assembly is registered in the local database.

適用於SQL Server 2008SQL Server 2008SQL Server 2017SQL Server 2017SQL DatabaseSQL Database (如果使用從 assembly_bits 建立的組件)。Applies to: SQL Server 2008SQL Server 2008 through SQL Server 2017SQL Server 2017, SQL DatabaseSQL Database (if using an assembly created from assembly_bits.

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  

傳遞參數Passing Parameters

本節的範例會示範如何使用輸入和輸出參數,在預存程序之間來回傳遞值。Examples in this section demonstrate how to use input and output parameters to pass values to and from a stored procedure.

D.D. 使用輸入參數建立程序Creating a procedure with input parameters

下列範例所建立的預存程序,會傳遞特定員工的名字和姓氏值,藉以傳回該員工的資訊。The following example creates a stored procedure that returns information for a specific employee by passing values for the employee's first name and last name. 此程序只會接受與所傳遞參數完全相符的項目。This procedure accepts only exact matches for the parameters passed.

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 程序:The uspGetEmployees procedure can be executed in the following ways:

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.E. 使用程序與萬用字元參數Using a procedure with wildcard parameters

下列範例所建立的預存程序,會傳遞一些員工的全名或部分姓名值,藉以傳回這些員工的資訊。The following example creates a stored procedure that returns information for employees by passing full or partial values for the employee's first name and last name. 這個程序模式會與所傳遞的參數相符,或者,如果沒有提供參數的話,該模式會使用預設值 (字母 D 開頭的姓氏)。This procedure pattern matches the parameters passed or, if not supplied, uses the preset default (last names that start with the letter 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 程序。The uspGetEmployees2 procedure can be executed in many combinations. 此處僅示範其中幾種可能的組合。Only a few possible combinations are shown here.

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.F. 使用 OUTPUT 參數Using OUTPUT parameters

下列範例會建立 uspGetList 程序。The following example creates the uspGetList procedure. 這個程序傳回一份產品清單,其中產品的價格都沒有超過指定的金額。This procedures returns a list of products that have prices that do not exceed a specified amount. 這個範例顯示多個 SELECT 陳述式和多個 OUTPUT 參數的用法。The example shows using multiple SELECT statements and multiple OUTPUT parameters. OUTPUT 參數可讓外部程序、批次,或多個 Transact-SQLTransact-SQL 陳述式在程序執行期間存取值集。OUTPUT parameters enable an external procedure, a batch, or more than one Transact-SQLTransact-SQL statement to access a value set during the procedure execution.

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 以傳回成本低於 $700Adventure WorksAdventure Works 產品 (自行車) 清單。Execute uspGetList to return a list of Adventure WorksAdventure Works products (Bikes) that cost less than $700. 您可以搭配流程控制語言使用 OUTPUT 參數 @Cost@ComparePrices,以在 [訊息] 視窗中傳回訊息。The OUTPUT parameters @Cost and @ComparePrices are used with control-of-flow language to return a message in the Messages window.

注意

建立程序以及使用變數時,都必須定義 OUTPUT 變數。The OUTPUT variable must be defined when the procedure is created and also when the variable is used. 參數名稱與變數名稱不一定要相符;不過,除非使用 @ListPrice = variable,否則資料類型與參數定位必須相符。The parameter name and variable name do not have to match; however, the data type and parameter positioning must match, unless @ListPrice = variable is used.

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)))+'.';  

部分結果集如下:Here is the partial result set:

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.G. 使用資料表值參數Using a Table-Valued Parameter

下列範例會使用資料表值參數類型,將多個資料列插入資料表中。The following example uses a table-valued parameter type to insert multiple rows into a table. 此範例會建立此參數類型、宣告資料表變數進行參考、填入參數清單,然後將值傳遞給預存程序。The example creates the parameter type, declares a table variable to reference it, fills the parameter list, and then passes the values to a stored procedure. 預存程序會使用這些值,將多個資料列插入資料表中。The stored procedure uses the values to insert multiple rows into a table.

/* 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.H. 使用 OUTPUT 資料指標參數Using an OUTPUT cursor parameter

下列範例會使用 OUTPUT 資料指標參數,將某個資料指標 (對程序而言,其為本機資料指標) 傳遞回呼叫的批次、程序或觸發程序。The following example uses the OUTPUT cursor parameter to pass a cursor that is local to a procedure back to the calling batch, procedure, or trigger.

首先,建立宣告的程序,然後在 Currency 資料表上開啟資料指標:First, create the procedure that declares and then opens a cursor on the Currency table:

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  

接著,執行宣告本機資料指標變數的批次、執行程序將資料指標指派給本機變數,然後從資料指標提取資料列。Next, run a batch that declares a local cursor variable, executes the procedure to assign the cursor to the local variable, and then fetches the rows from the cursor.

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

使用預存程序修改資料Modifying Data by using a Stored Procedure

本節範例將示範如何在程序的定義中包含資料操作語言 (DML) 陳述式,藉以在資料表或檢視表中插入或修改資料。Examples in this section demonstrate how to insert or modify data in tables or views by including a Data Manipulation Language (DML) statement in the definition of the procedure.

I.I. 在預存程序中使用 UPDATEUsing UPDATE in a stored procedure

下列範例會在預存程序中使用 UPDATE 陳述式。The following example uses an UPDATE statement in a stored procedure. 此程序會採用一個輸入參數 @NewHours 和一個輸出參數 @RowCountThe procedure takes one input parameter, @NewHours and one output parameter @RowCount. UPDATE 陳述式內會使用 @NewHours 參數值,以更新資料表 HumanResources.Employee 中的資料行 VacationHoursThe @NewHours parameter value is used in the UPDATE statement to update the column VacationHours in the table HumanResources.Employee. @RowCount 輸出參數是用來將受影響的資料列數目傳回給區域變數。The @RowCount output parameter is used to return the number of rows affected to a local variable. SET 子句會使用 CASE 運算式,以條件方式判斷針對 VacationHours 所設定的值。A CASE expression is used in the SET clause to conditionally determine the value that is set for VacationHours. 按照時數支付薪資給員工時 (SalariedFlag = 0),VacationHours 會設定為目前的時數加上 @NewHours 中指定的值,否則 VacationHours 會設定為 @NewHours 中指定的值。When the employee is paid hourly (SalariedFlag = 0), VacationHours is set to the current number of hours plus the value specified in @NewHours; otherwise, VacationHours is set to the value specified in @NewHours.

CREATE PROCEDURE HumanResources.Update_VacationHours  
@NewHours smallint  
AS   
SET NOCOUNT ON;  
UPDATE HumanResources.Employee  
SET VacationHours =   
    ( CASE  
         WHEN SalariedFlag = 0 THEN VacationHours + @NewHours  
         ELSE @NewHours  
       END  
    )  
WHERE CurrentFlag = 1;  
GO  
  
EXEC HumanResources.Update_VacationHours 40;  

錯誤處理Error Handling

本節範例將示範如何處理在執行預存程序時可能會發生的錯誤。Examples in this section demonstrate methods to handle errors that might occur when the stored procedure is executed.

J.J. 使用 TRY...CATCHUsing TRY...CATCH

下列範例使用 TRY...CATCH 建構傳回預存程序執行期間所攔截到的錯誤資訊。The following example using the TRY...CATCH construct to return error information caught during the execution of a stored procedure.

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;  
  
/* 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;  
  
DROP PROCEDURE Production.uspDeleteWorkOrder;  

模糊化程序定義Obfuscating the Procedure Definition

本節範例將示範如何模糊化預存程序的定義。Examples in this section show how to obfuscate the definition of the stored procedure.

K.K. 使用 WITH ENCRYPTION 選項Using the WITH ENCRYPTION option

下列範例會建立 HumanResources.uspEncryptThis 程序。The following example creates the HumanResources.uspEncryptThis procedure.

適用於SQL Server 2008SQL Server 2008SQL Server 2017SQL Server 2017、SQL Database。Applies to: SQL Server 2008SQL Server 2008 through SQL Server 2017SQL Server 2017, SQL Database.

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

WITH ENCRYPTION 選項會在查詢系統目錄或使用中繼資料函數時模糊化程序的定義,如下列範例所示。The WITH ENCRYPTION option obfuscates the definition of the procedure when querying the system catalog or using metadata functions, as shown by the following examples.

執行 sp_helptextRun sp_helptext:

EXEC sp_helptext 'HumanResources.uspEncryptThis';  

以下為結果集:Here is the result set.

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

直接查詢 sys.sql_modules 目錄檢視:Directly query the sys.sql_modules catalog view:

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

以下為結果集:Here is the result set.

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

強制程序重新編譯Forcing the Procedure to Recompile

本節範例將會使用 WITH RECOMPILE 子句強制程序在每次執行時重新編譯。Examples in this section use the WITH RECOMPILE clause to force the procedure to recompile every time it is executed.

L.L. 使用 WITH RECOMPILE 選項Using the WITH RECOMPILE option

如果提供給程序的參數不是典型的參數,且不應將新的執行計畫快取或儲存在記憶體中時,WITH RECOMPILE 子句十分好用。The WITH RECOMPILE clause is helpful when the parameters supplied to the procedure are not typical, and when a new execution plan should not be cached or stored in memory.

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;  

設定安全性內容Setting the Security Context

本節範例將使用 EXECUTE AS 子句設定用以執行預存程序的安全性內容。Examples in this section use the EXECUTE AS clause to set the security context in which the stored procedure executes.

M.M. 使用 EXECUTE AS 子句Using the EXECUTE AS clause

下列範例顯示利用 EXECUTE AS 子句來指定可執行程序的安全性內容。The following example shows using the EXECUTE AS clause to specify the security context in which a procedure can be executed. 在這個範例中,選項 CALLER 指定可以在呼叫程序之使用者的內容中執行程序。In the example, the option CALLER specifies that the procedure can be executed in the context of the user that calls it.

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.N. 建立自訂權限集合Creating custom permission sets

下列範例會使用 EXECUTE AS 建立資料庫作業的自訂權限。The following example uses EXECUTE AS to create custom permissions for a database operation. 某些作業 (例如 TRUNCATE TABLE) 沒有可授與的權限。Some operations such as TRUNCATE TABLE, do not have grantable permissions. 將 TRUNCATE TABLE 陳述式併入預存程序,並且將該程序指定成以有權修改資料表的使用者身分執行,即可針對您授與程序之 EXECUTE 權限的使用者,擴充截斷資料表的權限。By incorporating the TRUNCATE TABLE statement within a stored procedure and specifying that procedure execute as a user that has permissions to modify the table, you can extend the permissions to truncate the table to the user that you grant EXECUTE permissions on the procedure.

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

範例:Azure SQL 資料倉儲Azure SQL Data Warehouse平行處理資料倉儲Parallel Data WarehouseExamples: Azure SQL 資料倉儲Azure SQL Data Warehouse and 平行處理資料倉儲Parallel Data Warehouse

O.O. 建立預存程序以執行 SELECT 陳述式Create a Stored Procedure that runs a SELECT statement

這個範例會說明建立和執行程序的基本語法。This example shows the basic syntax for creating and running a procedure. 當執行批次時,CREATE PROCEDURE 必須是第一個陳述式。When running a batch, CREATE PROCEDURE must be the first statement. 例如,若要在 AdventureWorksPDW2012AdventureWorksPDW2012 中建立下列預存程序,請先設定資料庫內容,然後再執行 CREATE PROCEDURE 陳述式。For example, to create the following stored procedure in AdventureWorksPDW2012AdventureWorksPDW2012, set the database context first, and then run the CREATE PROCEDURE statement.

-- 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;  

另請參閱See Also

ALTER PROCEDURE (Transact-SQL) ALTER PROCEDURE (Transact-SQL)
流程控制語言 (Transact-SQL) Control-of-Flow Language (Transact-SQL)
資料指標 Cursors
資料類型 (Transact-SQL) Data Types (Transact-SQL)
DECLARE @local_variable (Transact-SQL) DECLARE @local_variable (Transact-SQL)
DROP PROCEDURE (Transact-SQL) DROP PROCEDURE (Transact-SQL)
EXECUTE (Transact-SQL) EXECUTE (Transact-SQL)
EXECUTE AS (Transact-SQL) EXECUTE AS (Transact-SQL)
預存程序 (Database Engine) Stored Procedures (Database Engine)
sp_procoption (Transact-SQL) sp_procoption (Transact-SQL)
sp_recompile (Transact-SQL) sp_recompile (Transact-SQL)
sys.sql_modules (Transact-SQL) sys.sql_modules (Transact-SQL)
sys.parameters (Transact-SQL) sys.parameters (Transact-SQL)
sys.procedures (Transact-SQL) sys.procedures (Transact-SQL)
sys.sql_expression_dependencies (Transact-SQL) sys.sql_expression_dependencies (Transact-SQL)
sys.assembly_modules (Transact-SQL) sys.assembly_modules (Transact-SQL)
sys.numbered_procedures (Transact-SQL) sys.numbered_procedures (Transact-SQL)
sys.numbered_procedure_parameters (Transact-SQL) sys.numbered_procedure_parameters (Transact-SQL)
OBJECT_DEFINITION (Transact-SQL) OBJECT_DEFINITION (Transact-SQL)
建立預存程序 Create a Stored Procedure
使用資料表值參數 (資料庫引擎) Use Table-Valued Parameters (Database Engine)
sys.dm_sql_referenced_entities (Transact-SQL) sys.dm_sql_referenced_entities (Transact-SQL)
sys.dm_sql_referencing_entities (Transact-SQL)sys.dm_sql_referencing_entities (Transact-SQL)