存储过程(二) 存储过程语法

一、语法

CREATE { PROC | PROCEDURE } [schema_name.] procedure_name     

[ { @parameter [ type_schema_name. ] data_type }       

  [ VARYING ] [ = default ] [ OUT | OUTPUT ]     ] [ ,...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 ：新存储过程的名称（在架构中必须唯一）。 @ parameter:过程中的参数。可以声明一个或多个参数。（ 如果指定了 FOR REPLICATION，则无法声明参数)。 [ type_schema_name. ] data_type:  参数以及所属架构的数据类型。 VARYING: 指定作为输出参数支持的结果集。该参数由存储过程动态构造，其内容可能发生改变( 仅适用于 cursor 参数)。 default:参数的默认值。 OUTPUT :指示参数是输出参数。 AS: 过程将要执行的操作。 <sql_statement>: 过程中要包含的任意数目和类型的 Transact-SQL 语句 RECOMPILE : 指示数据库引擎不缓存该过程的计划，该过程在运行时编译。如果指定了 FOR REPLICATION，则不能使用此选项。 ENCRYPTION :指示 SQL Server 将 CREATE PROCEDURE 语句的原始文本转换为模糊格式。模糊代码的输出在 SQL Server 2005 的任何目录视图中都不能直接显示。 对系统表或数据库文件没有访问权限的用户不能检索模糊文本。该选项对于 CLR 存储过程无效。 EXECUTE AS :指定在其中执行存储过程的安全上下文。 FOR REPLICATION: 指定不能在订阅服务器上执行为复制创建的存储过程。 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 标识符，并且该类必须存在于程序集中。如果类包含一个使用句点 ( .) 分隔命名空间各部分的限定命名空间的名称，则必须使用方括号 ( [ ]) 或引号 ( " ") 将类名称分隔开。指定的方法必须为该类的静态方法。    

二、注意事项

１、

（１）建议不在过程名称中使用前缀 sp_。此前缀由 SQL Server 使用，以指定系统存储过程。

（２）可以给非CLR的 存储过程指定临时过程名．

#procedure_name（即procedure_name 前面使用一个数字符号 #）创建局部临时过程．##procedure_name创建全局临时过程。

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

（４）调用过程时必须为每个参数提供值（除非设置了参数的默认值或者将参数设置为等于另一个参数），存储过程最多可以有 2,100 个参数。

（５）默认情况下，参数只能代替常量表达式，而不能用于代替表名、列名或其他数据库对象的名称。

（６）存储过程的参数的类型不能为table类型。

（７）cursor 数据类型只能用于 OUTPUT 参数。如果指定了 cursor 数据类型，则必须指定 VARYING 和 OUTPUT 关键字（可为 cursor 数据类型指定多个输出参数）。

（８） CLR 存储过程，不能指定 char、varchar、text、ntext、image、cursor 和 table 作为参数。如果参数的数据类型为 CLR 用户定义类型，则必须对此类型有 EXECUTE 权限。

（９）如果未指定 参数数据类型（type_schema_name），则 按以下顺序引用 数据类型：

Ａ：SQL Server 系统数据类型。 Ｂ： 当前数据库中当前用户的默认架构。 Ｃ：当前数据库中的 dbo 架构。

（１０）默认值必须是常量或 NULL。如果过程使用带 LIKE 关键字的参数，则可包含下列通配符：%、_、[] 和 [^]。 （１１）对于 CLR 存储过程，不能指定 RECOMPILE。 （１２）使用 FOR REPLICATION 选项创建的存储过程可用作存储过程筛选器，且只能在复制过程中执行。如果指定了 FOR REPLICATION，则无法声明参数。对于 CLR 存储过程，不能指定 FOR REPLICATION。对于使用 FOR REPLICATION 创建的过程，忽略 RECOMPILE 选项。

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

２、使用 CLR 存储过程的参数

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

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

• （１）声明为静态方法。
• （２）接收的参数个数与存储过程的参数个数相同。
• （３）不能是类的构造函数或析构函数。
• （４）使用的参数类型与 SQL Server 过程的相应参数的数据类型兼容。
• （５）返回 void，或者返回类型为 SQLInt32、SQLInt16、System.Int32 或 System.Int16 的值。
• （６）如果对于任何特定的参数声明都指定了 OUTPUT，则按照引用返回它的参数，而不是按照值返回。

３、有关 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 语句时，该设置只在存储过程完成之前有效。之后，设置将还原为调用存储过程时的值。

４、获得有关存储过程的信息

使用该存储过程所在的数据库中的 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';

５、执行存储过程

       当执行用户定义的存储过程时，无论是在批中还是在模块（例如用户定义的存储过程或函数）内，极力建议使用架构名称来限定存储过程名。如果存储过程编写为可以接受参数值，则可以提供参数值。该值必须是常量或变量。不能指定函数名作为参数值。变量可以是用户定义变量或系统变量，例如 @@SPID。

(1)自动执行存储过程

自动执行的存储过程必须由系统管理员在 master 数据库中创建，并以 sysadmin 固定服务器角色作为后台进程执行且不能有任何输入或输出参数。

自动执行的存储过程使用与固定服务器角色 sysadmin 成员相同的权限进行操作。该存储过程生成的所有错误消息都将写入 SQL Server 错误日志。

启动时恢复了 master 数据库后，即开始执行存储过程。

(2)设置、清除和控制自动执行

只有系统管理员 (sa) 可以将存储过程标记为自动执行。且该存储过程必须在 master 数据库中并由 sa 所有，而且不能有输入或输出参数。

使用 sp_procoption 可以设置存储过程是否为自动启动的存储过程。