sql2005创建存储过程

创建存储过程。存储过程是已保存的 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 [ PUT ]
    ] [ ,...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

备注
Transact-SQL 存储过程的最大大小为 128 MB。

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

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

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

存储过程中的任何 CREATE TABLE 或 ALTER TABLE 语句都将自动创建临时表。建议对于临时表中的每列，显式指定 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 数据类型匹配的信息，请参阅 SQL Server Data Types and Their .NET Framework Equivalents。

返回 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_dependencies 目录视图或使用 sp_depends。sp_depends 不返回有关 CLR 存储过程引用的对象的信息。若要显示有关 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 语句）中使用的对象名称进行限定。有关详细信息，请参阅设计存储过程（数据库引擎）。

参数
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
参数以及所属架构的数据类型。除 table 之外的其他所有数据类型均可以用作 Transact-SQL 存储过程的参数。但是，cursor 数据类型只能用于 OUTPUT 参数。如果指定了 cursor 数据类型，则还必须指定 VARYING 和 OUTPUT 关键字。可以为 cursor 数据类型指定多个输出参数。

对于 CLR 存储过程，不能指定 char、varchar、text、ntext、image、cursor 和 table 作为参数。有关 CLR 类型与 SQL Server 系统数据类型之间关系的详细信息，请参阅 SQL Server Data Types and Their .NET Framework Equivalents。有关 SQL Server 系统数据类型及其语法的详细信息，请参阅数据类型 (Transact-SQL)。

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

如果未指定 type_schema_name，则 SQL Server 2005 Database Engine 将按以下顺序引用 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 过程除外。

RECOMPILE
指示数据库引擎 不缓存该过程的计划，该过程在运行时编译。如果指定了 FOR REPLICATION，则不能使用此选项。对于 CLR 存储过程，不能指定 RECOMPILE。

若要指示数据库引擎 放弃存储过程内单个查询的计划，请使用 RECOMPILE 查询提示。有关详细信息，请参阅查询提示 (Transact-SQL)。如果非典型值或临时值仅用于属于存储过程的查询子集，则使用 RECOMPILE 查询提示。

ENCRYPTION
指示 SQL Server 将 CREATE PROCEDURE 语句的原始文本转换为模糊格式。模糊代码的输出在 SQL Server 2005 的任何目录视图中都不能直接显示。对系统表或数据库文件没有访问权限的用户不能检索模糊文本。但是，可通过 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 语句。有关某些适用的限制的信息，请参阅“备注”部分。

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

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

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

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

示例
A. 使用简单过程
以下存储过程将从视图中返回所有雇员（提供姓和名）、职务以及部门名称。此存储过程不使用任何参数。
USE AdventureWorks;
GO
IF OBJECT_ID ( 'HumanResources.usp_GetAllEmployees', 'P' ) IS NOT NULL
    DROP PROCEDURE HumanResources.usp_GetAllEmployees;
GO
CREATE PROCEDURE HumanResources.usp_GetAllEmployees
AS
    SELECT LastName, FirstName, JobTitle, Department
    FROM HumanResources.vEmployeeDepartment;
GO

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

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

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

USE AdventureWorks;
GO
IF OBJECT_ID ( 'HumanResources.usp_GetEmployees', 'P' ) IS NOT NULL
    DROP PROCEDURE HumanResources.usp_GetEmployees;
GO
CREATE PROCEDURE HumanResources.usp_GetEmployees
    @lastname varchar(40),
    @firstname varchar(20)
AS
    SELECT LastName, FirstName, JobTitle, Department
    FROM HumanResources.vEmployeeDepartment
    WHERE FirstName = @firstname AND LastName = @lastname;
GO

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

复制代码
EXECUTE HumanResources.usp_GetEmployees 'Ackerman', 'Pilar';
-- Or
EXEC HumanResources.usp_GetEmployees @lastname = 'Ackerman', @firstname = 'Pilar';
GO
-- Or
EXECUTE HumanResources.usp_GetEmployees @firstname = 'Pilar', @lastname = 'Ackerman';
GO
-- Or, if this procedure is the first statement within a batch:
HumanResources.usp_GetEmployees 'Ackerman', 'Pilar';

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

USE AdventureWorks;
GO
IF OBJECT_ID ( 'HumanResources.usp_GetEmployees2', 'P' ) IS NOT NULL
    DROP PROCEDURE HumanResources.usp_GetEmployees2;
GO
CREATE PROCEDURE HumanResources.usp_GetEmployees2
    @lastname varchar(40) = 'D%',
    @firstname varchar(20) = '%'
AS
    SELECT LastName, FirstName, JobTitle, Department
    FROM HumanResources.vEmployeeDepartment
    WHERE FirstName LIKE @firstname
        AND LastName LIKE @lastname;
GO
usp_GetEmployees2 存储过程可以按多种组合执行。下面只显示了几个组合：
EXECUTE HumanResources.usp_GetEmployees2;
-- Or
EXECUTE HumanResources.usp_GetEmployees2 'Wi%';
-- Or
EXECUTE HumanResources.usp_GetEmployees2 @firstname = '%';
-- Or
EXECUTE HumanResources.usp_GetEmployees2 '[CK]ars[OE]n';
-- Or
EXECUTE HumanResources.usp_GetEmployees2 'Hesse', 'Stefen';
-- Or
EXECUTE HumanResources.usp_GetEmployees2 'H%', 'S%';

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

USE AdventureWorks;
GO
IF OBJECT_ID ( 'Production.usp_GetList', 'P' ) IS NOT NULL
    DROP PROCEDURE Production.usp_GetList;
GO
CREATE PROCEDURE Production.usp_GetList @product varchar(40)
    , @maxprice money
    , @compareprice money OUTPUT
    , @listprice money OUT
AS
    SELECT p.name AS Product, p.ListPrice AS 'List Price'
    FROM Production.Product p
    JOIN Production.ProductSubcategory s
      ON p.ProductSubcategoryID = s.ProductSubcategoryID
    WHERE s.name LIKE @product AND p.ListPrice < @maxprice;
-- Populate the output variable @listprice.
SET @listprice = (SELECT MAX(p.ListPrice)
        FROM Production.Product p
        JOIN Production.ProductSubcategory s
          ON p.ProductSubcategoryID = s.ProductSubcategoryID
        WHERE s.name LIKE @product AND p.ListPrice < @maxprice);
-- Populate the output variable @compareprice.
SET @compareprice = @maxprice;
GO

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

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

DECLARE @compareprice money, @cost money
EXECUTE Production.usp_GetList '%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.

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

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

F. 使用 WITH ENCRYPTION 选项
以下示例将创建 HumanResources.usp_encrypt_this 存储过程。

USE AdventureWorks;
GO
IF OBJECT_ID ( 'HumanResources.usp_encrypt_this', 'P' ) IS NOT NULL
    DROP PROCEDURE HumanResources.usp_encrypt_this;
GO
CREATE PROCEDURE HumanResources.usp_encrypt_this
WITH ENCRYPTION
AS
    SELECT EmployeeID, Title, NationalIDNumber, VacationHours, SickLeaveHours
    FROM HumanResources.Employee;
GO

如以下示例所示，WITH ENCRYPTION 选项可阻止返回存储过程的定义。

运行 sp_helptext：

EXEC sp_helptext 'HumanResources.usp_encrypt_this';

下面是结果集：

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

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

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

下面是结果集：

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

(1 row(s) affected)

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

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

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

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

下面是结果集：
definition
-----------------------------------------------------------------------
CREATE PROCEDURE usp_proc1
AS
    SELECT column1, column2 FROM table_does_not_exist

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

USE AdventureWorks;
GO
IF OBJECT_ID ( 'Purchasing.usp_vendor_info_all', 'P' ) IS NOT NULL
    DROP PROCEDURE Purchasing.usp_vendor_info_all;
GO
CREATE PROCEDURE Purchasing.usp_vendor_info_all
WITH EXECUTE AS CALLER
AS
    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

I. 创建 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

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

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

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

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

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