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

适用对象: yesSQL ServeryesAzure SQL 数据库yesAzure SQL 数据仓库yes并行数据仓库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 数据库SQL DatabaseCLR integration does not apply to Azure SQL 数据库SQL 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 数据库SQL DatabaseSQL ServerSQL Server(从 SQL Server 2016 (13.x)SQL Server 2016 (13.x) SP1 开始)。Applies to: Azure SQL 数据库SQL 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_name procedure_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 2017Azure 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)

  • 游标数据类型只能是 OUTPUT 参数,并且必须带有 VARYING 关键字 。cursor data types can only be OUTPUT parameters and must be accompanied by the VARYING keyword.

针对 CLR 过程的准则 :Guidelines for CLR procedures:

  • 在托管代码中具有等效值的所有本机 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).

  • 表值或游标数据类型不能用作参数 。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. 仅适用于游标参数 。Applies only to cursor parameters. 该选项对于 CLR 过程无效。This option is not valid for CLR procedures.

default default
参数的默认值。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.

只有 CLR 过程的默认值记录在 sys.parameters.default 列中 。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 过程,否则 text、ntext 和 image 参数不能用作 OUTPUT 参数 。text, ntext, and image parameters cannot be used as OUTPUT parameters, unless the procedure is a CLR procedure. OUTPUT 参数可以为游标占位符,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 Engine不缓存此过程的查询计划,这强制在每次执行此过程时都对该过程进行编译。Indicates that the 数据库引擎Database 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 Engine放弃过程内单个查询的查询计划,请在该查询的定义中使用 RECOMPILE 查询提示。To instruct the 数据库引擎Database 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.objects 和 sys.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 数据库SQL DatabaseApplies to: SQL Server 2008SQL Server 2008 through SQL Server 2017SQL Server 2017, SQL 数据库SQL Database.

指定 .NET Framework.NET Framework 程序集的方法,以便 CLR 过程引用。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 2017Azure 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.

以下 SET 选项在 ATOMIC 块中始终为 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

SET 选项不能在 ATOMIC 块内更改。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 NULL NULL | 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 2017Azure 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 2017Azure 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 2017Azure 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 2017Azure 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 2017Azure 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 = format DATEFORMAT = format
适用范围:SQL Server 2014 (12.x)SQL Server 2014 (12.x)SQL Server 2017SQL Server 2017Azure 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 DATEFORMAT (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 2017Azure 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;   

调用过程时,请提供数据库 id 号。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. 这会禁止显示在执行任何 SELECT、INSERT、UPDATE、MERGE 和 DELETE 语句后 SQL ServerSQL Server 发送回客户端的消息。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 Engine 解析对象名称时所需的处理时间会更少。It takes less processing time for the 数据库引擎Database 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 Engine错误。This can prevent some 数据库引擎Database Engine errors that stop procedure execution. 例如,返回 12 列的表中的数据然后将数据插入 12 列临时表中的 SELECT * 语句将成功(在二表中任一表中列的数量或顺序发生更改之前)。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 语句中未进行指定,则 ANSI_DFLT_ON 和 ANSI_DFLT_OFF 选项将控制数据库引擎Database Engine为列指派 NULL 或 NOT NULL 属性的方式。The ANSI_DFLT_ON and ANSI_DFLT_OFF options control the way the 数据库引擎Database 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 不是空或者“无意义”值。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 Engine计划缓存中,则该过程随后执行的操作可能重新使用该计划。Subsequent executions of the procedure may reuse the plan already generated if it still remains in the plan cache of the 数据库引擎Database 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 例程、类型或聚合来调用另一个过程或执行托管代码时,过程将被嵌套。Procedures are nested when one procedure call another or executes managed code by referencing a CLR routine, type, or aggregate. 过程和托管代码引用的嵌套最高可达 32 级。Procedures and managed code references can be nested up to 32 levels. 每当调用的过程或托管代码引用开始执行,嵌套级别就增加一级;执行完成后,嵌套级别就减少一级。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. 从托管代码内部调用的方法不根据嵌套级别限制进行计数。Methods invoked from within the managed code do not count against the nesting level limit. 但是,当一个 CLR 存储过程通过 SQL Server 托管访问接口执行数据访问操作时,在从托管代码到 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 Engine 过程时,Transact-SQLTransact-SQL将保存 SET QUOTED_IDENTIFIER 和 SET ANSI_NULLS 的设置。The 数据库引擎Database 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_name USE 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.

为了使数据库引擎Database Engine在 .NET Framework 中被重载时引用正确的方法,EXTERNAL NAME 子句中指定的方法必须具有下列特征:For the 数据库引擎Database 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. 使用 ENCRYPTION 选项创建的过程的文本不能使用 sys.sql_modules 目录视图查看 。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:Plan Cache 对象SQLServer: Plan Cache Object Cache Hit RatioCache Hit Ratio
Cache PagesCache Pages
Cache Object Counts*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 计划缓存对象For more information, see SQL Server, Plan Cache Object.

SecuritySecurity

权限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.Departments 的本机编译存储过程:The 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   • = default
   • OUTPUT   • 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 数据库SQL Database(如果使用从 assembly_bits 创建的程序集) Applies to: SQL Server 2008SQL Server 2008 through SQL Server 2017SQL Server 2017, SQL 数据库SQL 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 数据库 。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)
存储过程(数据库引擎) 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)