CREATE PROCEDURE (Transact-SQL)

项目
04/24/2014

在 SQL Server 中创建 Transact-SQL 或公共语言运行时 (CLR) 存储过程。存储过程与其他编程语言中的过程类似，这是因为存储过程可以：

接受输入参数并以输出参数的格式向调用过程或批处理返回多个值。
包含用于在数据库中执行操作（包括调用其他过程）的编程语句。
向调用过程或批处理返回状态值，以指明成功或失败（以及失败的原因）。

使用此语句可以在当前数据库中创建永久过程，或者在 tempdb 数据库中创建临时程序。

适用于：SQL Server（SQL Server 2008 至当前版本），Azure SQL Database（初始版本至当前版本）。

语法

--SQL Server Stored Procedure Syntax
CREATE { 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 ]

--SQL Server CLR Stored Procedure Syntax
CREATE { 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 }
[;]

--SQL Server Natively Compiled Stored Procedure Syntax
CREATE { 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 } ]

-- Windows Azure SQL Database Syntax
CREATE { PROC | PROCEDURE } [schema_name.] procedure_name 
    [ { @parameter [ type_schema_name. ] data_type } 
    [ VARYING ] [ = default ] [ OUT | OUTPUT ] [READONLY]
    ] [ ,...n ] 
[ WITH <procedure_option> [ ,...n ] ]
AS { <sql_statement> [;][ ...n ] }
[;]
<procedure_option> ::= 
    [ RECOMPILE ]
    [ EXECUTE AS Clause ]

<sql_statement> ::= 
{ [ BEGIN ] statements [ END ] }

参数

schema_name
过程所属架构的名称。过程是绑定到架构的。如果在创建过程时未指定架构名称，则自动分配正在创建过程的用户的默认架构。
procedure_name
过程的名称。过程名称必须遵循有关标识符的规则，并且在架构中必须唯一。

在命名过程时避免使用 sp_ 前缀。此前缀由 SQL Server 用来指定系统过程。如果存在同名的系统过程，则使用前缀可能导致应用程序代码中断。

可在 procedure_name 前面使用一个数字符号 (#) (#procedure_name) 来创建局部临时程序，使用两个数字符号 (##procedure_name) 来创建全局临时程序。局部临时程序仅对创建了它的连接可见，并且在关闭该连接后将被删除。全局临时程序可用于所有连接，并且在使用该过程的最后一个会话结束时将被删除。对于 CLR 过程，不能指定临时名称。

过程或全局临时程序的完整名称（包括 ##）不能超过 128 个字符。局部临时程序的完整名称（包括 #）不能超过 116 个字符。
; number

适用于：SQL Server 2008 到 SQL Server 2014。

用于对同名的过程分组的可选整数。使用一个 DROP PROCEDURE 语句可将这些分组过程一起删除。

备注

后续版本的 Microsoft SQL Server 将删除该功能。请避免在新的开发工作中使用该功能，并着手修改当前还在使用该功能的应用程序。

带编号的过程不能使用 xml 或 CLR 用户定义类型，并且不能用于计划指南中。
@ parameter
在过程中声明的参数。通过将 at 符号 (@) 用作第一个字符来指定参数名称。参数名称必须符合有关标识符的规则。每个过程的参数仅用于该过程本身；其他过程中可以使用相同的参数名称。

可声明一个或多个参数；最大值是 2,100。除非定义了参数的默认值或者将参数设置为等于另一个参数，否则用户必须在调用过程时为每个声明的参数提供值。如果过程包含表值参数，并且该参数在调用中缺失，则传入空表。参数只能代替常量表达式，而不能用于代替表名、列名或其他数据库对象的名称。有关详细信息，请参阅 EXECUTE (Transact-SQL)。

如果指定了 FOR REPLICATION，则无法声明参数。
[ type_schema_name**.**] data_type
参数的数据类型以及该数据类型所属的架构。

针对 Transact-SQL 过程的准则：
- 所有 Transact-SQL 数据类型都可以用作参数。
- 您可以使用用户定义的表类型创建表值参数。表值参数只能是 INPUT 参数，并且这些参数必须带有 READONLY 关键字。有关详细信息，请参阅使用表值参数（数据库引擎）。
- cursor 数据类型只能是 OUTPUT 参数，并且必须带有 VARYING 关键字。
针对 CLR 过程的准则：
- 在托管代码中具有等效值的所有本机 SQL Server 数据类型都可以用作参数。有关 CLR 类型与 SQL Server 系统数据类型之间关系的详细信息，请参阅映射 CLR 参数数据。有关 SQL Server 系统数据类型及其语法的详细信息，请参阅数据类型 (Transact-SQL)。
- 表值或 cursor 数据类型不能用作参数。
- 如果参数的数据类型为 CLR 用户定义类型，则必须对此类型有 EXECUTE 权限。
VARYING
指定作为输出参数支持的结果集。该参数由过程动态构造，其内容可能发生改变。仅适用于 cursor 参数。该选项对于 CLR 过程无效。
default
参数的默认值。如果为参数定义了默认值，则无需指定此参数的值即可执行过程。默认值必须是常量或 NULL。该常量值可以采用通配符的形式，这使其可以在将该参数传递到过程时使用 LIKE 关键字。请参阅以下示例 C。

只有 CLR 过程的默认值记录在 sys.parameters.default 列中。对于 Transact-SQL 过程参数，该列将为 NULL。
OUT | OUTPUT
指示参数是输出参数。使用 OUTPUT 参数将值返回给过程的调用方。除非是 CLR 过程，否则 text、ntext 和 image 参数不能用作 OUTPUT 参数。 OUTPUT 参数可以为游标占位符，CLR 过程除外。不能将表值数据类型指定为过程的 OUTPUT 参数。
READONLY
指示不能在过程的主体中更新或修改参数。如果参数类型为表值类型，则必须指定 READONLY。
RECOMPILE
指示数据库引擎不缓存此过程的查询计划，这强制在每次执行此过程时都对该过程进行编译。有关强制重新编译的原因的详细信息，请参阅重新编译存储过程。在指定了 FOR REPLICATION 或者用于 CLR 过程时不能使用此选项。

若要指示数据库引擎放弃过程内单个查询的查询计划，请在该查询的定义中使用 RECOMPILE 查询提示。有关详细信息，请参阅查询提示 (Transact-SQL)。
ENCRYPTION

适用于：SQL Server 2008 到 SQL Server 2014。

指示 SQL Server 将 CREATE PROCEDURE 语句的原始文本转换为模糊格式。模糊代码的输出在 SQL Server 的任何目录视图中都不能直接显示。对系统表或数据库文件没有访问权限的用户不能检索模糊文本。但是，可以通过 DAC 端口访问系统表的特权用户或直接访问数据文件的特权用户可以使用此文本。此外，能够向服务器进程附加调试器的用户可在运行时从内存中检索已解密的过程。有关访问系统元数据的详细信息，请参阅元数据可见性配置。

该选项对于 CLR 过程无效。

使用此选项创建的过程不能作为 SQL Server 复制的一部分发布。
EXECUTE AS clause
指定在其中执行过程的安全上下文。

内存中 OLTP 支持 SELF、OWNER 和 ‘user_name’ 子句。

有关详细信息，请参阅 EXECUTE AS 子句 (Transact-SQL)。
FOR REPLICATION

适用于：SQL Server 2008 到 SQL Server 2014。

指定为复制创建该过程。因此，它不能在订阅服务器上执行。使用 FOR REPLICATION 选项创建的过程可用作过程筛选器，且仅在复制过程中执行。如果指定了 FOR REPLICATION，则无法声明参数。对于 CLR 过程，不能指定 FOR REPLICATION。对于使用 FOR REPLICATION 创建的过程，忽略 RECOMPILE 选项。

FOR REPLICATION 过程将在 sys.objects 和 sys.procedures 中包含 RF 对象类型。
{ [ BEGIN ] sql_statement [;] [ ...n ][ END ] }
构成过程主体的一个或多个 Transact-SQL 语句。您可以使用可选的 BEGIN 和 END 关键字将这些语句括起来。有关信息，请参阅后面的“最佳实践”、“一般备注”以及“限制和局限”部分。
EXTERNAL NAME assembly_name**.class_name.**method_name

适用于：SQL Server 2008 到 SQL Server 2014。

指定 .NET Framework 程序集的方法，以便 CLR 过程引用。 class_name 必须为有效的 SQL Server 标识符，并且必须作为类存在于程序集中。如果类包含一个使用句点 (.) 分隔命名空间各部分的限定命名空间的名称，则必须使用方括号 ([ ]) 或引号 (" ") 将类名称分隔开。指定的方法必须为该类的静态方法。

默认情况下，SQL Server 不能执行 CLR 代码。可以创建、修改和删除引用公共语言运行时模块的数据库对象；不过，只有在启用 clr enabled 选项之后，才能在 SQL Server 中执行这些引用。要启用该选项，请使用 sp_configure。

备注

包含数据库中不支持 CLR 过程。
ATOMIC WITH

适用于：SQL Server 2014 到 SQL Server 2014。

指示执行原子存储过程。更改提交或所有更改通过引发异常回滚。 ATOMIC WITH 块对于本机编译存储过程是必需的。

如果过程（通过 RETURN 语句显式或者通过完成执行隐式）返回，则提交过程所执行的工作。如果过程引发，则过程所执行的工作将回滚。

默认情况下，XACT_ABORT 在原子块内为 ON，并且不能更改。 XACT_ABORT 指定当 Transact-SQL 语句出现运行时错误时，SQL Server 是否自动回滚当前事务。

以下 SET 选项在 ATOMIC 块中始终为 ON；该选项不能更改。
- CONCAT_NULL_YIELDS_NULL
- QUOTED_IDENTIFIER, ARITHABORT
- NOCOUNT
- ANSI_NULLS
- ANSI_WARNINGS
SET 选项不能在 ATOMIC 块内更改。用户会话中的 SET 选项不在本机编译存储过程的范围内使用。这些选项在编译时是固定的。

BEGIN、ROLLBACK 和 COMMIT 操作无法在原子块内使用。

每个本机编译存储过程的外层作用域都有一个 ATOMIC 块。这些块不能嵌套。有关原子块的详细信息，请参阅本机编译的存储过程简介。
NULL | NOT NULL
确定参数中是否允许 Null 值。默认值为 NULL。
NATIVE_COMPILATION

适用于：SQL Server 2014 到 SQL Server 2014。

指示过程已本机编译。 NATIVE_COMPILATION、SCHEMABINDING 和 EXECUTE AS 可以按任意顺序指定。有关详细信息，请参阅本机编译的存储过程简介。
SCHEMABINDING

适用于：SQL Server 2014 到 SQL Server 2014。

确保过程引用的表不能删除或修改。 SCHEMABINDING 在本机编译存储过程中是必需的。（有关详细信息，请参阅本机编译的存储过程简介。）SCHEMABINDING 限制与其对用户定义的函数的限制是相同的。有关详细信息，请参阅 CREATE FUNCTION (Transact-SQL) 中的 SCHEMABINDING 部分。
LANGUAGE = [N] ‘language’

适用于：SQL Server 2014 到 SQL Server 2014。

等同于 SET LANGUAGE (Transact-SQL) 会话选项。 LANGUAGE = [N] 'language' 是必须的。
TRANSACTION ISOLATION LEVEL

适用于：SQL Server 2014 到 SQL Server 2014。

对于本机编译存储过程是必需的。指定存储过程的事务隔离级别。选项如下所示：

有关这些选项的详细信息，请参阅 SET TRANSACTION ISOLATION LEVEL (Transact-SQL)。
- REPEATABLE READ
  指定语句不能读取已由其他事务修改但尚未提交的数据。如果另一个事务修改由当前事务读取的数据，当前事务将失败。
- SERIALIZABLE
  请指定下列内容：
  - 语句不能读取已由其他事务修改但尚未提交的数据。
  - 如果另一个事务修改由当前事务读取的数据，当前事务将失败。
  - 如果另一个事务使用当前事务中任何语句读取的键值插入新行，当前事务将失败。
- SNAPSHOT
  指定事务中任何语句读取的数据都将是在事务开始时便存在的数据的事务上一致的版本。
DATEFIRST = number

适用于：SQL Server 2014 到 SQL Server 2014。

将一周的第一天指定为 1 到 7 中的一个数字。 DATEFIRST 是可选的。如果未指定，该设置从指定语言进行推断。

有关详细信息，请参阅 SET DATEFIRST (Transact-SQL)。
DATEFORMAT = format

适用于：SQL Server 2014 到 SQL Server 2014。

指定用于解释 date、smalldatetime、datetime、datetime2 和 datetimeoffset 字符串的月、日和年日期部分的顺序。 DATEFORMAT 是可选的。如果未指定，该设置从指定语言进行推断。

有关详细信息，请参阅 SET DATEFORMAT (Transact-SQL)。
DELAYED_DURABILITY = { OFF | ON }

适用于：SQL Server 2014 到 SQL Server 2014。

SQL Server 事务提交可以是完全持久、默认或延迟的持久。

有关详细信息，请参阅控制事务持续性。

最佳做法

尽管并未列出所有最佳做法，这些建议还是可以提高过程性能。

使用 SET NOCOUNT ON 语句作为过程主体中的第一个语句。也就是说，将其放置于紧接着 AS 关键字之后。这会禁止显示在执行任何 SELECT、INSERT、UPDATE、MERGE 和 DELETE 语句后 SQL Server 发送回客户端的消息。通过消除这一不必要的网络开销可以提高数据库和应用程序的总体性能。有关信息，请参阅 SET NOCOUNT (Transact-SQL)。
当在过程中创建或引用数据库对象时使用架构名称。如果不必搜索多个架构，则这样做将会减少数据库引擎解析对象名称所用的处理时间。这样做还可以避免在创建对象时如果未指定架构，由要分配的用户的默认架构导致的权限和访问问题。
避免函数包装在 WHERE 和 JOIN 子句中指定的列。这样做会使列具有不确定性并且禁止查询处理器使用索引。
避免在返回许多行数据的 SELECT 语句中使用标量函数。因为标量函数必须应用于每一行，所以最终导致的行为将类似于基于行的处理并且会降低性能。
避免使用 SELECT *。而是应指定所需的列名。这样做可以避免停止过程执行的数据库引擎错误。例如一个 SELECT * 语句，该语句从由 12 列构成的表返回数据，然后将这些数据插入一个由 12 列构成的临时表中，在更改这两个表中任何一个表的列数目或顺序前，该语句将成功执行。
避免处理或返回过多的数据。尽可能在过程代码中缩小结果的范围，这样，该过程执行的任何后续操作都将使用可能的最小数据集完成。仅将基本数据发送到客户端应用程序。它比跨网络发送多余的数据并且强制客户端应用程序处理不必要的大结果集更高效。
通过使用 BEGIN/END TRANSACTION 来使用显式事务并且保留尽可能短的事务。更长的事务意味着更长的记录锁定和更高的死锁风险。
使用 Transact-SQL TRY…CATCH 功能进行过程内的错误处理。 TRY…CATCH 可以封装整个 Transact-SQL 语句块。这不仅产生更少的性能开销，还通过显著减少的编程，使错误报告更精确。
在过程主体中对 CREATE TABLE 或 ALTER TABLE Transact-SQL 语句引用的所有表列使用 DEFAULT 关键字。这将禁止将 NULL 传递到不允许 Null 值的列。
对于临时表中的每一列使用 NULL 或 NOT NULL。如果在 CREATE TABLE 或 ALTER TABLE 语句中未进行指定，则 ANSI_DFLT_ON 和 ANSI_DFLT_OFF 选项将控制数据库引擎为列指派 NULL 或 NOT NULL 属性的方式。如果某个连接执行的过程对这些选项的设置与创建该过程的连接的设置不同，则为第二个连接创建的表列可能会有不同的为 Null 性，并且表现出不同的行为。如果为每个列显式声明了 NULL 或 NOT NULL，那么将对所有执行该过程的连接使用相同的为 Null 性创建临时表。
使用将转换 Null 的修改语句并且包括从查询中删除含 Null 值的行的逻辑。请注意，在 Transact-SQL 中，NULL 不是空或者没有任何值。它是针对未知值的占位符并且可能导致意外的行为，特别是在查询结果集或使用 AGGREGATE 函数时。
使用 UNION ALL 运算符来代替 UNION 或 OR 运算符，除非存在针对非重复值的特定需要。 UNION ALL 运算符要求更少的处理开销，因为重复值不从结果集中筛选出来。

一般备注

一个过程没有预定义的最大大小。

在过程中指定的变量可以是用户定义变量或系统变量，如 @@SPID。

第一次执行某个过程时，将编译该过程以确定检索数据的最优访问计划。如果已经生成的计划仍保留在数据库引擎计划缓存中，则该过程随后执行的操作可能重新使用该计划。

SQL Server 启动时可以自动执行一个或多个过程。这些过程必须由系统管理员在 master 数据库中创建，并以 sysadmin 固定服务器角色作为后台进程执行。这些过程不能有任何输入或输出参数。有关详细信息，请参阅执行存储过程。

当一个过程通过引用 CLR 例程、类型或聚合来调用另一个过程或执行托管代码时，过程将被嵌套。过程和托管代码引用的嵌套最高可达 32 级。每当调用的过程或托管代码引用开始执行，嵌套级别就增加一级；执行完成后，嵌套级别就减少一级。从托管代码内部调用的方法不根据嵌套级别限制进行计数。但是，当一个 CLR 存储过程通过 SQL Server 托管访问接口执行数据访问操作时，在从托管代码到 SQL 的转换中将添加一级嵌套。

试图超过最高级的嵌套将导致整个调用链失败。可以使用 @@NESTLEVEL 返回当前存储过程执行的嵌套级别。

互操作性

在创建或修改 Transact-SQL 过程时，数据库引擎将保存 SET QUOTED_IDENTIFIER 和 SET ANSI_NULLS 的设置。执行过程时，将使用这些原始设置。因此，所有客户端会话的 SET QUOTED_IDENTIFIER 和 SET ANSI_NULLS 设置在执行过程时都将被忽略。

在创建或更改过程时不保存其他 SET 选项（例如 SET ARITHABORT、SET ANSI_WARNINGS 或 SET ANSI_PADDINGS）。如果过程的逻辑取决于特定的设置，则应在过程开头添加一条 SET 语句，以确保设置正确。从过程中执行 SET 语句时，该设置只在过程完成之前有效。之后，设置将还原为调用过程时的值。这样一来，单个客户端就可以设置所需的选项，而不会影响过程的逻辑。

可以在过程中指定除了 SET SHOWPLAN_TEXT 和 SET SHOWPLAN_ALL 以外的任何 SET 语句。这些语句在批处理中必须唯一。选择的 SET 选项在过程执行过程中有效，之后恢复为原来的设置。

备注

在过程和用户定义函数中传递参数，或者在批处理语句中声明和设置变量时，不执行 SET ANSI_WARNINGS。例如，如果将变量定义为 char(3) 类型，然后为它设置一个大于三个字符的值，则该数据会被截断为定义的大小，并且 INSERT 或 UPDATE 语句可以成功执行。

限制和局限

在单个批处理中，CREATE PROCEDURE 语句不能与其他 Transact-SQL 语句组合使用。

以下语句不能用于存储过程主体中的任何地方。

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

过程可以引用尚不存在的表。在创建时，只进行语法检查。直到第一次执行该过程时才对其进行编译。只有在编译过程中才解析过程中引用的所有对象。因此，如果语法正确的过程引用了不存在的表，则仍可以成功创建；但如果被引用的表不存在，则过程将在执行时将失败。

不能将某一函数名称指定为参数默认值或者在执行过程时传递给参数的值。但是，您可以将函数作为变量传递，如以下示例中所示：

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

如果该过程对 SQL Server 的远程实例进行更改，将无法回滚这些更改。远程过程不参与事务。

为了使数据库引擎在 .NET Framework 中被重载时引用正确的方法，EXTERNAL NAME 子句中指定的方法必须具有下列特征：

声明为静态方法。
接收的参数个数与过程的参数个数相同。
使用的参数类型与 SQL Server 过程的相应参数的数据类型兼容。有关将 SQL Server 数据类型与 .NET Framework 数据类型匹配的信息，请参阅映射 CLR 参数数据。

元数据

下表列出了可用于返回有关存储过程的信息的目录视图和动态管理视图。

视图	说明
sys.sql_modules	返回 Transact-SQL 过程的定义。不能使用 sys.sql_modules 目录视图查看使用 ENCRYPTION 选项创建的过程的文本。
sys.assembly_modules	返回有关 CLR 过程的信息。
sys.parameters	返回有关在过程中定义的参数的信息。
sys.sql_expression_dependencies sys.dm_sql_referenced_entities sys.dm_sql_referencing_entities	返回过程引用的对象。

若要估计编译后的过程大小，请使用下列性能监视器计数器。

性能监视器对象名	性能监视器计数器名称
SQLServer: Plan Cache Object	Cache Hit Ratio
	Cache Pages
	Cache Object Counts*

*各种类别的缓存对象均可以使用这些计数器，包括即席 Transact-SQL、准备好的 Transact-SQL、过程、触发器等。有关详细信息，请参阅 SQL Server Plan Cache 对象。

安全性

权限

要求数据库中的 CREATE PROCEDURE 权限以及对要创建过程的架构的 ALTER 权限，或者要求 db_ddladmin 固定数据库角色中的成员身份。

对于 CLR 存储过程，需要对 EXTERNAL NAME 子句中引用的程序集的所有权，或拥有对该程序集的 REFERENCES 权限。

CREATE PROCEDURE 和内存优化表

有关详细信息，请参阅本机编译的存储过程简介。

下面的示例介绍如何创建存储过程以和内存优化表一起使用：

CREATE PROCEDURE 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

使用本机编译存储过程访问内存优化表。有关详细信息，请参阅内存中 OLTP（内存中优化）。

本机编译存储过程的 CREATE PROCEDURE 只支持原样提供的参数默认值。也支持输出参数。

未使用 NATIVE_COMPILATION 创建的过程不能更改为本机编译存储过程。本机编译存储过程不支持 ALTER PROCEDURE。

有关本机编译的存储过程中的可编程性、支持的查询外围应用和运算符的论述，请参阅本机编译的存储过程中支持的构造。

示例

类别	作为特征的语法元素
基本语法	CREATE PROCEDURE
传递参数	@parameter • = 默认值 • OUTPUT • 表值参数类型 • CURSOR VARYING
使用存储过程修改数据	UPDATE
错误处理	TRY…CATCH
对过程定义进行模糊处理	WITH ENCRYPTION
强制重新编译过程	WITH RECOMPILE
设置安全上下文	EXECUTE AS

基本语法

此节中的示例说明了使用最低要求的语法的 CREATE PROCEDURE 语句的基本功能。

A.创建简单 Transact-SQL 过程

以下示例将创建一个存储过程，该存储过程将从 AdventureWorks2012 数据库的一个视图中返回所有雇员（提供姓和名）、职务以及部门名称。此过程不使用任何参数。该示例然后说明执行此过程的三个方法。

IF OBJECT_ID ( 'HumanResources.uspGetAllEmployees', 'P' ) IS NOT NULL 
    DROP PROCEDURE HumanResources.uspGetAllEmployees;
GO
CREATE PROCEDURE HumanResources.uspGetAllEmployees
AS
    SET NOCOUNT ON;
    SELECT LastName, FirstName, JobTitle, Department
    FROM HumanResources.vEmployeeDepartment;
GO

SELECT * FROM HumanResources.vEmployeeDepartment;

uspGetEmployees 过程可通过以下方式执行：

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

B.返回多个结果集

以下过程返回两个结果集。

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

C.创建 CLR 存储过程

以下示例将创建 GetPhotoFromDB 过程，此过程引用 HandlingLOBUsingCLR 程序集中的 LargeObjectBinary 类的 GetPhotoFromDB 方法。在创建该过程之前，在本地数据库中注册了 HandlingLOBUsingCLR 程序集。

适用于：SQL Server 2008 到 SQL Server 2014。

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

传递参数

此节中的示例说明如何使用输入参数和输出参数将值传递给存储过程以及从存储过程传递值。

A.创建带有输入参数的过程

以下示例将创建一个存储过程，此过程通过传递特定雇员的名和姓的值来返回该雇员的信息。此过程仅接受与传递的参数精确匹配的值。

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

uspGetEmployees 过程可通过以下方式执行：

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

B.使用带有通配符参数的过程

以下示例将创建一个存储过程，此过程通过传递雇员的名和姓的完整值或部分值来返回雇员的信息。此过程模式与所传递的参数相匹配；或者，如果未提供参数，则使用预设的默认值（以字母 D 开头的姓）。

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

uspGetEmployees2 过程可通过多种组合方式执行。此处只列出了几个可能的组合。

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

C.使用 OUTPUT 参数

以下示例将创建 uspGetList 过程。此过程将返回价格不超过指定数值的产品的列表。此示例显示如何使用多个 SELECT 语句和多个 OUTPUT 参数。 OUTPUT 参数允许外部过程、批处理或多条 Transact-SQL 语句在过程执行期间访问设置的某个值。

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

执行 uspGetList，返回价格低于 $700 的 Adventure Works 产品（自行车）的列表。 OUTPUT 参数 @Cost 和 @ComparePrices 用于流控制语言，以便在**“消息”**窗口中返回消息。

备注

OUTPUT 变量必须在创建过程时或使用变量时定义。参数名和变量名不一定要匹配；但是，除非使用 @ListPrice = variable 的形式，否则数据类型和参数位置必须匹配。

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

下面是部分结果集：

Product List Price

-------------------------- ----------

Road-750 Black, 58 539.99

Mountain-500 Silver, 40 564.99

Mountain-500 Silver, 42 564.99

...

Road-750 Black, 48 539.99

Road-750 Black, 52 539.99

(14 row(s) affected)

These items can be purchased for less than $700.00.

D.使用表值参数

以下示例使用表值参数类型将多个行插入表中。该示例将创建参数类型，声明表变量来引用它，填充参数列表，然后将值传递给存储过程。存储过程使用这些值将多个行插入表中。

/* 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

E.使用 OUTPUT 游标参数

以下示例使用 OUTPUT 游标参数将过程的局部游标传递回执行调用的批处理、过程或触发器。

首先，创建在 Currency 表上声明并打开一个游标的过程：

IF OBJECT_ID ( 'dbo.uspCurrencyCursor', 'P' ) IS NOT NULL
    DROP PROCEDURE dbo.uspCurrencyCursor;
GO
CREATE PROCEDURE dbo.uspCurrencyCursor 
    @CurrencyCursor CURSOR VARYING OUTPUT
AS
    SET NOCOUNT ON;
    SET @CurrencyCursor = CURSOR
    FORWARD_ONLY STATIC FOR
      SELECT CurrencyCode, Name
      FROM Sales.Currency;
    OPEN @CurrencyCursor;
GO

接下来，运行以下批处理：声明一个局部游标变量，执行上述过程以将游标赋值给局部变量，然后从该游标提取行。

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

使用存储过程修改数据

此节中的示例说明如何通过在过程定义中包含数据操作语言 (DML) 语句，在表或视图中插入或修改数据。

A.在存储过程中使用 UPDATE

下面的示例在一个存储过程中使用了 UPDATE 语句。该过程采用一个输入参数 @NewHours 和一个输出参数 @RowCount。该 @NewHours 参数值用于 UPDATE 语句中，以便更新 HumanResources.Employee 表中的 VacationHours 列。 @RowCount 输出参数用于将影响的行数返回给一个局部变量。在 SET 子句中使用 CASE 表达式，以便按条件确定为 VacationHours 设置的值。在按每小时向员工付薪时 (SalariedFlag = 0)，VacationHours 设置为当前小时数加上 @NewHours 中指定的值；否则，VacationHours 设置为在 @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;

错误处理

此节中的示例介绍一些方法，这些方法用于处理在执行存储过程时可能出现的错误。

使用 TRY…CATCH

以下示例使用 TRY…CATCH 构造返回在执行存储过程期间捕获的错误信息。

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

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

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

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

GO
EXEC Production.uspDeleteWorkOrder 13;

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

对过程定义进行模糊处理

此节中的示例说明如何对存储过程定义进行模糊处理。

A.使用 WITH ENCRYPTION 选项

以下示例将创建 HumanResources.uspEncryptThis 过程。

适用于：SQL Server 2008 到 SQL Server 2014。

IF OBJECT_ID ( 'HumanResources.uspEncryptThis', 'P' ) IS NOT NULL 
    DROP PROCEDURE HumanResources.uspEncryptThis;
GO
CREATE PROCEDURE HumanResources.uspEncryptThis
WITH ENCRYPTION
AS
    SET NOCOUNT ON;
    SELECT BusinessEntityID, JobTitle, NationalIDNumber, VacationHours, SickLeaveHours 
    FROM HumanResources.Employee;
GO

在查询系统目录或使用元数据函数时，WITH ENCRYPTION 选项对过程定义进行模糊处理，如以下示例中所示。

运行 sp_helptext：

EXEC sp_helptext 'HumanResources.uspEncryptThis';

下面是结果集：

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

直接查询 sys.sql_modules 目录视图：

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

下面是结果集：

definition

--------------------------------

NULL

强制重新编译过程

此节中的示例使用 WITH RECOMPILE 子句强制过程在每次执行时进行重新翻译。

A.使用 WITH RECOMPILE 选项

如果为过程提供的参数不是典型的参数，并且新的执行计划不应被缓存或存储在内存中，则 WITH RECOMPILE 子句会很有用。

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

设置安全上下文

此节中的示例使用 EXECUTE AS 子句设置执行存储过程的安全上下文。

A.使用 EXECUTE AS 子句

以下示例显示使用 EXECUTE AS 子句指定执行过程的安全上下文。在此示例中，选项 CALLER 指定此过程可在调用它的用户上下文中执行。

IF OBJECT_ID ( 'Purchasing.uspVendorAllInfo', 'P' ) IS NOT NULL 
    DROP PROCEDURE Purchasing.uspVendorAllInfo;
GO
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

B.创建自定义权限集

以下示例使用 EXECUTE AS 为数据库操作创建自定义权限。某些操作（如 TRUNCATE TABLE）没有可授予的权限。通过将 TRUNCATE TABLE 语句合并到存储过程中并指定该过程作为一个有权修改表的用户执行，您可以将截断表的权限扩展至授予其对过程的 EXECUTE 权限的用户。

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

请参阅

参考

ALTER PROCEDURE (Transact-SQL)

控制流语言 (Transact-SQL)

数据类型 (Transact-SQL)

DECLARE @local\_variable (Transact-SQL)

DROP PROCEDURE (Transact-SQL)

EXECUTE (Transact-SQL)

EXECUTE AS (Transact-SQL)

sp_procoption (Transact-SQL)

sp_recompile (Transact-SQL)

sys.sql_modules (Transact-SQL)

sys.parameters (Transact-SQL)

sys.procedures (Transact-SQL)

sys.sql_expression_dependencies (Transact-SQL)

sys.assembly_modules (Transact-SQL)

sys.numbered_procedures (Transact-SQL)

sys.numbered_procedure_parameters (Transact-SQL)

OBJECT_DEFINITION (Transact-SQL)

sys.dm_sql_referenced_entities (Transact-SQL)

sys.dm_sql_referencing_entities (Transact-SQL)

概念