项目
12/14/2010

CREATE PROCEDURE (Transact-SQL)

创建存储过程。存储过程是已保存的 Transact-SQL 语句集合，或对 Microsoft .NET Framework 公共语言运行时 (CLR) 方法的引用，可接收并返回用户提供的参数。可以创建过程供永久使用，或在一个会话（局部临时过程）中临时使用，或在所有会话（全局临时过程）中临时使用。

启动 SQL Server 的一个实例时，也可以创建并自动运行存储过程。

主题链接图标 Transact-SQL 语法约定

语法

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 { <sql_statement> [;][ ...n ] | <method_specifier> }
[;]
<procedure_option> ::= 
    [ ENCRYPTION ]
    [ RECOMPILE ]
    [ EXECUTE AS Clause ]

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

<method_specifier> ::=
EXTERNAL NAME assembly_name.class_name.method_name

参数

schema_name
过程所属架构的名称。
procedure_name
新存储过程的名称。过程名称必须遵循有关标识符的规则，并且在架构中必须唯一。

极力建议不在过程名称中使用前缀 sp_。此前缀由 SQL Server 使用，以指定系统存储过程。有关详细信息，请参阅创建存储过程（数据库引擎）。

可在 procedure_name 前面使用一个数字符号 (#) (#procedure_name) 来创建局部临时过程，使用两个数字符号 (##procedure_name) 来创建全局临时过程。对于 CLR 存储过程，不能指定临时名称。

存储过程或全局临时存储过程的完整名称（包括 ##）不能超过 128 个字符。局部临时存储过程的完整名称（包括 #）不能超过 116 个字符。
**;**number
是可选整数，用于对同名的过程分组。使用一个 DROP PROCEDURE 语句可将这些分组过程一起删除。例如，称为 orders 的应用程序可能使用名为 orderproc;1、orderproc;2 等的过程。DROP PROCEDURE orderproc 语句将删除整个组。如果名称中包含分隔标识符，则数字不应包含在标识符中；只应在 procedure_name 前后使用适当的分隔符。

带编号的存储过程有以下限制：
- 不能使用 xml 或 CLR 用户定义类型作为数据类型。
- 不能对带编号的存储过程创建计划指南。
注意

后续版本的 Microsoft SQL Server 将删除该功能。请避免在新的开发工作中使用该功能，并着手修改当前还在使用该功能的应用程序。
**@**parameter
过程中的参数。在 CREATE PROCEDURE 语句中可以声明一个或多个参数。除非定义了参数的默认值或者将参数设置为等于另一个参数，否则用户必须在调用过程时为每个声明的参数提供值。存储过程最多可以有 2,100 个参数。如果过程包含表值参数，并且该参数在调用中缺失，则传入空表默认值。

通过将 at 符号 (@) 用作第一个字符来指定参数名称。参数名称必须符合标识符规则。每个过程的参数仅用于该过程本身；其他过程中可以使用相同的参数名称。默认情况下，参数只能代替常量表达式，而不能用于代替表名、列名或其他数据库对象的名称。有关详细信息，请参阅 EXECUTE (Transact-SQL)。

如果指定了 FOR REPLICATION，则无法声明参数。
[ type_schema_name**.** ] data_type
参数以及所属架构的数据类型。所有数据类型都可以用作 Transact-SQL 存储过程的参数。可以使用用户定义表类型来声明表值参数作为 Transact-SQL 存储过程的参数。只能将表值参数指定为输入参数，这些参数必须带有 READONLY 关键字。cursor 数据类型只能用于 OUTPUT 参数。如果指定了 cursor 数据类型，则还必须指定 VARYING 和 OUTPUT 关键字。可以为 cursor 数据类型指定多个输出参数。

对于 CLR 存储过程，不能指定 char、varchar、text、ntext、image、cursor、用户定义表类型和 table 作为参数。有关 CLR 类型与 SQL Server 系统数据类型之间关系的详细信息，请参阅映射 CLR 参数数据。有关 SQL Server 系统数据类型及其语法的详细信息，请参阅数据类型 (Transact-SQL)。

如果参数的数据类型为 CLR 用户定义类型，则必须对此类型有 EXECUTE 权限。

如果未指定 type_schema_name，则 SQL Server 数据库引擎将按以下顺序引用 type_name：
- SQL Server 系统数据类型。
- 当前数据库中当前用户的默认架构。
- 当前数据库中的 dbo 架构。
对于带编号的存储过程，数据类型不能为 xml 或 CLR 用户定义类型。
VARYING
指定作为输出参数支持的结果集。该参数由存储过程动态构造，其内容可能发生改变。仅适用于 cursor 参数。
default
参数的默认值。如果定义了 default 值，则无需指定此参数的值即可执行过程。默认值必须是常量或 NULL。如果过程使用带 LIKE 关键字的参数，则可包含下列通配符：%、_、[] 和 [^]。

注意

只有 CLR 过程的默认值记录在 sys.parameters.default 列中。对于 Transact-SQL 过程参数，该列将为 NULL。
OUTPUT
指示参数是输出参数。此选项的值可以返回给调用 EXECUTE 的语句。使用 OUTPUT 参数将值返回给过程的调用方。除非是 CLR 过程，否则 text、ntext 和 image 参数不能用作 OUTPUT 参数。使用 OUTPUT 关键字的输出参数可以为游标占位符，CLR 过程除外。不能将用户定义表类型指定为存储过程的 OUTPUT 参数。
READONLY
指示不能在过程的主体中更新或修改参数。如果参数类型为用户定义的表类型，则必须指定 READONLY。
RECOMPILE
指示数据库引擎不缓存该过程的计划，该过程在运行时编译。如果指定了 FOR REPLICATION，则不能使用此选项。对于 CLR 存储过程，不能指定 RECOMPILE。

若要指示数据库引擎放弃存储过程内单个查询的计划，请使用 RECOMPILE 查询提示。有关详细信息，请参阅查询提示 (Transact-SQL)。如果非典型值或临时值仅用于属于存储过程的查询子集，则使用 RECOMPILE 查询提示。
ENCRYPTION
指示 SQL Server 将 CREATE PROCEDURE 语句的原始文本转换为模糊格式。模糊代码的输出在 SQL Server 的任何目录视图中都不能直接显示。对系统表或数据库文件没有访问权限的用户不能检索模糊文本。但是，可以通过 DAC 端口访问系统表的特权用户或直接访问数据文件的特权用户可以使用此文本。此外，能够向服务器进程附加调试器的用户可在运行时从内存中检索已解密的过程。有关访问系统元数据的详细信息，请参阅元数据可见性配置。

该选项对于 CLR 存储过程无效。

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

有关详细信息，请参阅 EXECUTE AS 子句 (Transact-SQL)。
FOR REPLICATION
指定不能在订阅服务器上执行为复制创建的存储过程。使用 FOR REPLICATION 选项创建的存储过程可用作存储过程筛选器，且只能在复制过程中执行。如果指定了 FOR REPLICATION，则无法声明参数。对于 CLR 存储过程，不能指定 FOR REPLICATION。对于使用 FOR REPLICATION 创建的过程，忽略 RECOMPILE 选项。

FOR REPLICATION 过程将在 sys.objects 和 sys.procedures 中包含 RF 对象类型。
<sql_statement>
要包含在过程中的一个或多个 Transact-SQL 语句。有关某些适用的限制的信息，请参阅“备注”部分。

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

注意
只有 CLR 过程的默认值记录在 sys.parameters.default 列中。对于 Transact-SQL 过程参数，该列将为 NULL。

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

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

注释

存储过程没有预定义的最大大小。

只能在当前数据库中创建用户定义存储过程。临时过程对此是个例外，因为它们总是在 tempdb 中创建。如果未指定架构名称，则使用创建过程的用户的默认架构。有关架构的详细信息，请参阅用户架构分离。

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

默认情况下，参数可为空值。如果传递 NULL 参数值并且在 CREATE 或 ALTER TABLE 语句中使用该参数，而该语句中被引用列又不允许使用空值，则数据库引擎会产生一个错误。若要阻止向不允许使用空值的列传递 NULL，请为过程添加编程逻辑，或使用 CREATE TABLE 或 ALTER TABLE 的 DEFAULT 关键字，以便对该列使用默认值。

建议对于临时表中的每列，显式指定 NULL 或 NOT NULL。如果在 CREATE TABLE 或 ALTER TABLE 语句中未进行指定，则 ANSI_DFLT_ON 和 ANSI_DFLT_OFF 选项将控制数据库引擎为列指派 NULL 或 NOT NULL 属性的方式。如果某个连接执行的存储过程对这些选项的设置与创建该过程的连接的设置不同，则为第二个连接创建的表列可能会有不同的为空性，并且显示出不同的行为。如果为每个列显式声明了 NULL 或 NOT NULL，那么将对所有执行该存储过程的连接使用相同的为空性创建临时表。

使用 SET 选项

在创建或修改 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 语句时，该设置只在存储过程完成之前有效。之后，设置将还原为调用存储过程时的值。这样一来，单个客户端就可以设置所需的选项，而不会影响存储过程的逻辑。

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

使用 CLR 存储过程的参数

CLR 存储过程的参数可以是标量 SQL Server 系统数据类型的任何一种。

为了使数据库引擎在 .NET Framework 中被重载时引用正确的方法，<method_specifier> 中指示的方法必须具有下列特征：

声明为静态方法。
接收的参数个数与过程的参数个数相同。
不能是类的构造函数或析构函数。
使用的参数类型与 SQL Server 过程的相应参数的数据类型兼容。有关将 SQL Server 数据类型与 .NET Framework 数据类型匹配的信息，请参阅映射 CLR 参数数据。
返回 void，或者返回类型为 SQLInt32、SQLInt16、System.Int32 或 System.Int16 的值。
如果对于任何特定的参数声明都指定了 OUTPUT，则按照引用返回它的参数，而不是按照值返回。

获得有关存储过程的信息

若要显示 Transact-SQL 存储过程的定义，请使用该过程所在的数据库中的 sys.sql_modules 目录视图。

例如：

USE AdventureWorks;
GO
SELECT definition 
FROM sys.sql_modules 
JOIN sys.objects ON sys.sql_modules.object_id = sys.objects.object_id AND TYPE = 'P';

注意
不能使用 sys.sql_modules 目录视图查看使用 ENCRYPTION 选项创建的存储过程的文本。

若要获取有关某过程引用的对象的报表，请查询 sys.sql_expression_dependencies 目录视图或使用 sys.dm_sql_referenced_entities 和 sys.dm_sql_referencing_entities。

若要显示有关 CLR 存储过程的信息，请使用该过程所在的数据库中的 sys.assembly_modules 目录视图。

若要显示有关存储过程中定义的参数的信息，请使用该过程所在的数据库中的 sys.parameters 目录视图。

延迟名称解析

可以创建引用尚不存在的表的存储过程。在创建时，只进行语法检查。直到第一次执行该存储过程时才对其进行编译。只有在编译过程中才解析存储过程中引用的所有对象。因此，如果语法正确的存储过程引用了不存在的表，则仍可以成功创建；但如果被引用的表不存在，则存储过程将在运行时失败。有关详细信息，请参阅延迟名称解析和编译。

执行存储过程

当执行用户定义的存储过程时，无论是在批中还是在模块（例如用户定义的存储过程或函数）内，极力建议使用架构名称来限定存储过程名。

如果存储过程编写为可以接受参数值，则可以提供参数值。该值必须是常量或变量。不能指定函数名作为参数值。变量可以是用户定义变量或系统变量，例如 @@SPID。

有关详细信息，请参阅执行存储过程（数据库引擎）。

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

使用 cursor 数据类型的参数

Transact-SQL 存储过程只能将 cursor 数据类型用于 OUTPUT 参数。如果为某个参数指定了 cursor 数据类型，则还需要 VARYING 和 OUTPUT 参数。如果为某个参数指定了 VARYING 关键字，则数据类型必须是 cursor，并且必须指定 OUTPUT 关键字。有关详细信息，请参阅在 OUTPUT 参数中使用 cursor 数据类型。

临时存储过程

数据库引擎支持两种临时过程：局部临时过程和全局临时过程。局部临时过程只对创建该过程的连接可见。全局临时过程则可由所有连接使用。局部临时过程在当前会话结束时将被自动删除。全局临时过程在使用该过程的最后一个会话结束时被删除。有关详细信息，请参阅创建存储过程（数据库引擎）。

自动执行存储过程

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

存储过程嵌套

存储过程可以被嵌套。这表示一个存储过程可以调用另一个存储过程。在被调用过程开始运行时，嵌套级将增加，在被调用过程运行结束后，嵌套级将减少。存储过程最多可以嵌套 32 级。有关详细信息，请参阅嵌套存储过程。

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

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

* 各种类别的缓存对象均可以使用这些计数器，包括即席 sql、准备好的 sql、过程、触发器等。

有关详细信息，请参阅 SQL Server Plan Cache 对象。

<sql_statement> 限制

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

如果用户不是存储过程所有者，则在使用存储过程时，必须使用对象架构名称对存储过程内所有数据定义语言 (DDL) 语句（例如 CREATE、ALTER 或 DROP 语句、DBCC 语句、EXECUTE 和动态 SQL 语句）中使用的对象名进行限定。有关详细信息，请参阅设计存储过程（数据库引擎）。

权限

需要在数据库中有 CREATE PROCEDURE 权限，对在其中创建过程的架构有 ALTER 权限。

对于 CLR 存储过程，需要对 <method_specifier> 中引用的程序集的所有权，或拥有对该程序集的 REFERENCES 权限。

示例

A. 使用简单过程

以下存储过程将从视图中返回所有雇员（提供姓和名）、职务以及部门名称。此存储过程不使用任何参数。

USE AdventureWorks;
GO
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, Department
    FROM HumanResources.vEmployeeDepartmentHistory;
GO

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

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

B. 使用带有参数的简单过程

下面的存储过程只从视图中返回指定的雇员（提供名和姓）及其职务和部门名称。此存储过程接受与传递的参数精确匹配的值。

USE AdventureWorks;
GO
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, Department
    FROM HumanResources.vEmployeeDepartmentHistory
    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';

C. 使用带有通配符参数的简单过程

以下存储过程只从视图中返回指定的一些雇员（提供名和姓）及其职务和部门名称。此存储过程模式与所传递的参数相匹配；或者，如果未提供参数，则使用预设的默认值（以字母 D 打头的姓）。

USE AdventureWorks;
GO
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, Department
    FROM HumanResources.vEmployeeDepartmentHistory
    WHERE FirstName LIKE @FirstName AND LastName LIKE @LastName;
GO

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

D. 返回多个结果集

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

USE AdventureWorks;
GO
CREATE PROCEDURE uspNResults 
AS
SELECT COUNT(ContactID) FROM Person.Contact
SELECT COUNT(CustomerID) FROM Sales.Customer;
GO

E. 使用 OUTPUT 参数

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

USE AdventureWorks;
GO
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.

F. 使用 WITH RECOMPILE 选项

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

USE AdventureWorks;
GO
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.VendorID = pv.VendorID 
    JOIN Production.Product AS p 
      ON pv.ProductID = p.ProductID
    WHERE v.Name LIKE @Name;
GO

G. 使用 WITH ENCRYPTION 选项

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

USE AdventureWorks;
GO
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 EmployeeID, Title, 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 目录视图：

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

下面是结果集。

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

(1 row(s) affected)

H. 使用延迟名称解析

以下示例将创建 uspProc1 过程。该过程使用延迟名称解析。尽管引用的表在编译时不存在，但仍能创建存储过程。但是，执行过程时表必须存在。

USE AdventureWorks;
GO
IF OBJECT_ID ( 'dbo.uspProc1', 'P' ) IS NOT NULL 
    DROP PROCEDURE dbo.uspProc1;
GO
CREATE PROCEDURE dbo.uspProc1
AS
    SET NOCOUNT ON;
    SELECT column1, column2 FROM table_does_not_exist
GO

若要验证是否已创建了存储过程，请运行以下查询：

USE AdventureWorks;
GO
SELECT definition
FROM sys.sql_modules
WHERE object_id = OBJECT_ID('dbo.uspproc1');

下面是结果集。

definition
-----------------------------------------------------------------------
CREATE PROCEDURE uspproc1
AS
    SELECT column1, column2 FROM table_does_not_exist

(1 row(s) affected)

I. 使用 EXECUTE AS 子句

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

USE AdventureWorks;
GO
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 'Credit Rating', 
      v.ActiveFlag AS Availability
    FROM Purchasing.Vendor v 
    INNER JOIN Purchasing.ProductVendor pv
      ON v.VendorID = pv.VendorID 
    INNER JOIN Production.Product p
      ON pv.ProductID = p.ProductID 
    ORDER BY v.Name ASC;
GO

J. 创建 CLR 存储过程

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

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

K. 使用 OUTPUT 游标参数

OUTPUT 游标参数用来将存储过程的局部游标传递回执行调用的批处理、存储过程或触发器。

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

USE AdventureWorks;
GO
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

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

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

请参阅

通过

CREATE PROCEDURE (Transact-SQL)

语法

参数

注释

使用 SET 选项

使用 CLR 存储过程的参数

获得有关存储过程的信息

延迟名称解析

执行存储过程

使用 cursor 数据类型的参数

临时存储过程

自动执行存储过程

存储过程嵌套

<sql_statement> 限制

权限

示例

A. 使用简单过程

B. 使用带有参数的简单过程

C. 使用带有通配符参数的简单过程

D. 返回多个结果集

E. 使用 OUTPUT 参数

F. 使用 WITH RECOMPILE 选项

G. 使用 WITH ENCRYPTION 选项

H. 使用延迟名称解析

I. 使用 EXECUTE AS 子句

J. 创建 CLR 存储过程

K. 使用 OUTPUT 游标参数

请参阅

任务

参考

概念

其他资源

其他资源