MSSQL SERVER认识存储过程语法

　　CREATE PROCEDURE
　　 缔造存储过程，存储过程是 保留起来的 可以 承受和返回消费者提供的参数的 Transact-SQL 语句的 集中 。 可以 缔造一个过程供 永远 使用，或在一个会话中暂时 使用( 部分暂时过程)，或在全部会话中暂时 使用(全局暂时过程) 。也 可以 缔造在 Microsoft SQL Server启动时自动运行的存储过程 。

　　语法
　　CREATE PROC [ EDURE ] procedure_name [ ; number ]
　　[ { @parameter data_type }
　　[ VARYING ] [ = default ] [ OUTPUT ]
　　] [ ,...n ]

　　[ WITH
　　{ RECOMPILE | ENCRYPTION | RECOMPILE , ENCRYPTION } ]

　　[ FOR REPLICATION ]

　　AS sql_statement [ ...n ]

　　参数
　　procedure_name

　　新存储过程的名称 。过程名必须 相符标识符 规定，且关于数据库及其全部者必须唯一 。有关更多信息，请参见 使用标识符 。
　　要 缔造 部分暂时过程， 可以在 procedure_name 前面加一个编号符 (#procedure_name)，要 缔造全局暂时过程， 可以在 procedure_name 前面加两个编号符 (##procedure_name) 。 完全的名称(包括 # 或 ##)不能超过 128 个字符 。指定过程全部者的名称是可选的 。

　　;number
　　是可选的整数，用来对同名的过程分组，以便用一条 DROP PROCEDURE 语句即可将同组的过程一同除去 。例如，名为 orders 的 利用程序 使用的过程 可以命名为 orderproc;1、orderproc;2 等 。DROP PROCEDURE orderproc 语句将除去整个组 。假如名称中包括定界标识符，则数字不应包括在标识符中，只应在 procedure_name 前后 使用适当的定界符 。

　　@parameter

　　过程中的参数 。在 CREATE PROCEDURE 语句中 可以申明一个或多个参数 。消费者必须在执行过程时提供每个所申明参数的值(除非定义了该参数的默许值) 。存储过程最多 可以有 2.100 个参数 。

　　 使用 @ 符号作为第一个字符来指定参数名称 。参数名称必须 相符标识符的 规定 。每个过程的参数仅用于该过程 本身； 雷同的参数名称 可以用在其它过程中 。默许状况下，参数不得不 接替常量，而不能用于 接替表名、列名或其它数据库对象的名称 。有关更多信息，请参见 EXECUTE 。

　　data_type

　　参数的数据类型 。全部数据类型(包括 text、ntext 和 image)均 可以用作存储过程的参数 。不过，cursor 数据类型不得不用于 OUTPUT 参数 。假如指定的数据类型为 cursor，也必须同时指定 VARYING 和 OUTPUT 要害字 。有关 SQL Server 提供的数据类型及其语法的更多信息，请参见数据类型 。

　　 注明 关于 可以是 cursor 数据类型的输出参数，没有最大数 目标 制约 。

　　VARYING

　　指定作为输出参数 支撑的 后果集(由存储过程动态 构造，内容 可以 变迁) 。仅 实用于游标参数 。

　　default

　　参数的默许值 。假如定义了默许值， 毋庸指定该参数的值即可执行过程 。默许值必须是常量或 NULL 。假如过程将对该参数 使用 LIKE 要害字，那么默许值中 可以包括通配符(*、_、[] 和 [^]) 。

　　OUTPUT

　　表明参数是返回参数 。该选项的值 可以返回给 EXEC[UTE] 。 使用 OUTPUT 参数可将信息返回给调用过程 。Text、ntext 和 image 参数可用作 OUTPUT 参数 。 使用 OUTPUT 要害字的输出参数 可以是游标占位符 。

　　n

　　 示意最多 可以指定 2.100 个参数的占位符 。

　　{RECOMPILE | ENCRYPTION | RECOMPILE, ENCRYPTION}

　　RECOMPILE 表明 SQL Server 不会缓存该过程的 方案，该过程将在运行时再一次编译 。在 使用非典型值或暂时值而不 盼望 遮蔽缓存在内存中的执行 方案时，请 使用 RECOMPILE 选项 。

　　ENCRYPTION 示意 SQL Server 加密 syscomments 表中包括 CREATE PROCEDURE 语句文本的条目 。 使用 ENCRYPTION 可 预防将过程作为 SQL Server 复制的一 部分公布 。

　　 注明 在 晋级过程中，SQL Server 利用存储在 syscomments 中的加密 诠释来再一次 缔造加密过程 。

　　FOR REPLICATION

　　指定不能在订阅服务器上执行为复制 缔造的存储过程 。. 使用 FOR REPLICATION 选项 缔造的存储过程可用作存储过程筛选，且不得不在复制过程中执行 。本选项不能和 WITH RECOMPILE 选项一同 使用 。

　　AS

　　指定过程要执行的操作 。

　　sql_statement

　　过程中要包括的任意数目和类型的 Transact-SQL 语句 。但有一些 制约 。

　　n

　　是 示意此过程 可以包括多条 Transact-SQL 语句的占位符 。

　　 诠释
　　存储过程的最大大小为 128 MB 。

　　消费者定义的存储过程不得不在目前数据库中 缔造(暂时过程除外，暂时过程总是在 tempdb 中 缔造) 。在单个批 解决中，CREATE PROCEDURE 语句不能与其它 Transact-SQL 语句组合 使用 。

　　默许状况下，参数可为空 。假如传递 NULL 参数值而且该参数在 CREATE 或 ALTER TABLE 语句中 使用，而该语句中 引用的列又不同意 使用 NULL，则 SQL Server 会产生一条 舛误信息 。为了 预防向不同意 使用 NULL 的列传递 NULL 参数值，应向过程中增加编程逻辑或为该列 使用默许值( 使用 CREATE 或 ALTER TABLE 的 DEFAULT 要害字) 。

　　 提议在存储过程的任何 CREATE TABLE 或 ALTER TABLE 语句中都为每列显式指定 NULL 或 NOT NULL，例如在 缔造暂时表时 。ANSI_DFLT_ON 和 ANSI_DFLT_OFF 选项操纵 SQL Server 为列指派 NULL 或 NOT NULL 特点的 模式(假如在 CREATE TABLE 或 ALTER TABLE 语句中没有指定的话) 。假如某个衔接执行的存储过程对这些选项的设置与 缔造该过程的衔接的设置不同，则为第二个衔接 缔造的表列可能会有不同的为空性，而且 体现出不同的行为 模式 。假如为每个列显式申明了 NULL 或 NOT NULL，那么将对全部执行该存储过程的衔接 使用 雷同的为空性 缔造暂时表 。

　　在 缔造或更改存储过程时，SQL Server 将 保留 SET QUOTED_IDENTIFIER 和 SET ANSI_NULLS 的设置 。执行存储过程时，将 使用这些原始设置 。 因此，全部客户端会话的 SET QUOTED_IDENTIFIER 和 SET ANSI_NULLS 设置在执行存储过程时都将被 忽略 。在存储过程中浮现的 SET QUOTED_IDENTIFIER 和 SET ANSI_NULLS 语句不影响存储过程的 性能 。

　　其它 SET 选项(例如 SET ARITHABORT、SET ANSI_WARNINGS 或 SET ANSI_PADDINGS)在 缔造或更改存储过程时不 保留 。假如存储过程的逻辑取决于特定的设置，应在过程开头增加一条 SET 语句，以确保设置正确 。从存储过程中执行 SET 语句时，该设置只在存储过程 实现之前有效 。之后，设置将 复原为调用存储过程时的值 。这使个别的客户端 可以设置所需的选项，而不会影响存储过程的逻辑 。

　　 注明 SQL Server 是将空字符串解释为单个空格还是解释为真正的空字符串，由兼容级别设置操纵 。假如兼容级别小于或等于 65，SQL Server 就将空字符串解释为单个空格 。假如兼容级别等于 70，则 SQL Server 将空字符串解释为空字符串 。有关更多信息，请参见 sp_dbcmptlevel 。

　　 获得有关存储过程的信息
　　若要显示用来 缔造过程的文本，请在过程所在的数据库中执行 sp_helptext，并 使用过程名作为参数 。

　　 注明 使用 ENCRYPTION 选项 缔造的存储过程不能 使用 sp_helptext 查看 。

　　若要显示有关过程 引用的对象的报表，请 使用 sp_depends 。

　　若要为过程重命名，请 使用 sp_rename 。

　　 引用对象
　　SQL Server 同意 缔造的存储过程 引用尚不存在的对象 。在 缔造时，只进行语法 审查 。执行时，假如高速缓存中尚无有效的 方案，则编译存储过程以生成执行 方案 。惟独在编译过程中才解析存储过程中 引用的全部对象 。 因此，假如语法正确的存储过程 引用了不存在的对象，则仍 可以 顺利 缔造，但在运行时将失败，由于所 引用的对象不存在 。有关更多信息，请参见延迟名称解析和编译 。

　　延迟名称解析和兼容级别
　　SQL Server 同意 Transact-SQL 存储过程在 缔造时 引用不存在的表 。这种 威力称为延迟名称解析 。不过，假如 Transact-SQL 存储过程 引用了该存储过程中定义的表，而兼容级别设置(通过执行 sp_dbcmptlevel 来设置)为 65，则在 缔造时会发出 忠告信息 。而假如在运行时所 引用的表不存在，将返回 舛误信息 。有关更多信息，请参见 sp_dbcmptlevel 和延迟名称解析和编译 。

　　执行存储过程
　　 顺利执行 CREATE PROCEDURE 语句后，过程名称将存储在 sysobjects 系统表中，而 CREATE PROCEDURE 语句的文本将存储在 syscomments 中 。第一次执行时，将编译该过程以确定检索数据的最佳 拜访 方案 。

　　 使用 cursor 数据类型的参数
　　存储过程不得不将 cursor 数据类型用于 OUTPUT 参数 。假如为某个参数指定了 cursor 数据类型，也必须指定 VARYING 和 OUTPUT 参数 。假如为某个参数指定了 VARYING 要害字，则数据类型必须是 cursor，而且必须指定 OUTPUT 要害字 。

　　 注明 cursor 数据类型不能通过数据库 API(例如 OLE DB、ODBC、ADO 和 DB-Library)绑定到 利用程序变量上 。由于必须先绑定 OUTPUT 参数， 利用程序才 可以执行存储过程，所以带有 cursor OUTPUT 参数的存储过程不能通过数据库 API 调用 。惟独将 cursor OUTPUT 变量赋值给 Transact-SQL 部分 cursor 变量时，才 可以通过 Transact-SQL 批 解决、存储过程或触发器调用这些过程 。

　　Cursor 输出参数
　　在执行过程时，以下 规定 实用于 cursor 输出参数：

　　关于只进游标，游标的 后果集中返回的行只不过那些存储过程执行 完毕时处于或超出游标位置的行，例如：
　　在过程中的名为 RS 的 100 行 后果集上 打开一个非滚动游标 。

　　过程提取 后果集 RS 的头 5 行 。

　　过程返回到其调用者 。

　　返回到调用者的 后果集 RS 由 RS 的第 6 到 100 行构成，调用者中的游标处于 RS 的第一行之前 。
　　关于只进游标，假如存储过程 实现后，游标位于第一行的前面，则整个 后果集将返回给调用批 解决、存储过程或触发器 。返回时，游标将位于第一行的前面 。

　　关于只进游标，假如存储过程 实现后，游标的位置超出最终一行的结尾，则为调用批 解决、存储过程或触发器返回空 后果集 。

　　 注明 空 后果集与空值不同 。

　　关于可滚动游标，在存储过程执行 完毕时， 后果集中的全部行均会返回给调用批 解决、存储过程或触发器 。返回时，游标保留在过程中最终一次执行提取时的位置 。

　　关于任意类型的游标，假如游标关闭，则将空值传递回调用批 解决、存储过程或触发器 。假如将游标指派给一个参数，但该游标从未 打开过，也会浮现这种状况 。

　　 注明 关闭状态惟独在返回时才有影响 。例如， 可以在过程中关闭游标，稍后再 打开游标， 而后将该游标的 后果集返回给调用批 解决、存储过程或触发器 。

　　暂时存储过程
　　SQL Server 支撑两种暂时过程： 部分暂时过程和全局暂时过程 。 部分暂时过程不得不由 缔造该过程的衔接 使用 。全局暂时过程则可由全部衔接 使用 。 部分暂时过程在目前会话 完毕时自动除去 。全局暂时过程在 使用该过程的最终一个会话 完毕时除去 。通常是在 缔造该过程的会话 完毕时 。

　　暂时过程用 # 和 ## 命名， 可以由任何消费者 缔造 。 缔造过程后， 部分过程的全部者是唯一 可以 使用该过程的消费者 。执行 部分暂时过程的权限不能授予 其余消费者 。假如 缔造了全局暂时过程，则全部消费者均 可以 拜访该过程，权限不能显式破除 。惟独在 tempdb 数据库中 存在显式 CREATE PROCEDURE 权限的消费者，才 可以在该数据库中显式 缔造暂时过程(不 使用编号符命名) 。 可以授予或破除这些过程中的权限 。

　　 注明 频繁 使用暂时存储过程会在 tempdb 中的系统表上产生争用，从而对性能产生负面影响 。 提议 使用 sp_executesql 接替 。sp_executesql 不在系统表中存储数据， 因此 可以幸免这一问题 。

　　自动执行存储过程
　　SQL Server 启动时 可以自动执行一个或多个存储过程 。这些存储过程必须由系统治理员 缔造，并在 sysadmin 固定服务器角色下作为 后盾过程执行 。这些过程不能有任何输入参数 。

　　对启动过程的数目没有 制约，然而要 留神，每个启动过程在执行时都会占用一个衔接 。假如必须在启动时执行多个过程，但不需求并行执行，则 可以指定一个过程作为启动过程，让该过程调用其它过程 。这样就只占用一个衔接 。

　　在启动时 复原了最终一个数据库后，即开始执行存储过程 。若要跳过这些存储过程的执行，请将启动参数指定为跟踪标记 4022 。假如以最低配置启动 SQL Server( 使用 -f 标记)，则启动存储过程也不会执行 。有关更多信息，请参见跟踪标记 。

　　若要 缔造启动存储过程，必须作为 sysadmin 固定服务器角色的成员登录，并在 master 数据库中 缔造存储过程 。

　　 使用 sp_procoption 可以：

　　将现有存储过程指定为启动过程 。

　　 停留在 SQL Server 启动时执行过程 。

　　查看 SQL Server 启动时执行的全部过程的列表 。
　　存储过程嵌套
　　存储过程 可以嵌套，即一个存储过程 可以调用另一个存储过程 。在被调用过程开始执行时，嵌套级将增加，在被调用过程执行 完毕后，嵌套级将削减 。假如超出最大的嵌套级，会使整个调用过程链失败 。可用 @@NESTLEVEL 函数返回目前的嵌套级 。

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

　　性能 监督器对象名 性能 监督计数器名称
　　SQLServer：缓冲区治理器 高速缓存大小(页面数)
　　SQLServer：高速缓存治理器 高速缓存命中率
　　高速缓存页
　　高速缓存对象计数*

　　* 各种分类的高速缓存对象均 可以 使用这些计数器，包括特别 sql、 预备 sql、过程、触发器等 。

　　有关更多信息，请参见 SQL Server:Buffer Manager 对象和 SQL Server:Cache Manager 对象 。

　　sql_statement 制约
　　除了 SET SHOWPLAN_TEXT 和 SET SHOWPLAN_ALL 之外(这两个语句必须是批 解决中仅有的语句)，任何 SET 语句均 可以在存储过程内部指定 。所 取舍的 SET 选项在存储过程执行过程中有效，之后 复原为原来的设置 。

　　假如 其余消费者要 使用某个存储过程，那么在该存储过程内部，一些语句 使用的对象名必须 使用对象全部者的名称限定 。这些语句包括：

　　ALTER TABLE

　　CREATE INDEX

　　CREATE TABLE

　　全部 DBCC 语句

　　DROP TABLE

　　DROP INDEX

　　TRUNCATE TABLE

　　UPDATE STATISTICS
　　权限
　　CREATE PROCEDURE 的权限默许授予 sysadmin 固定服务器角色成员和 db_owner 和 db_ddladmin 固定数据库角色成员 。sysadmin 固定服务器角色成员和 db_owner 固定数据库角色成员 可以将 CREATE PROCEDURE 权限转让给 其余消费者 。执行存储过程的权限授予过程的全部者，该全部者 可 认为其它数据库消费者设置执行权限 。

　　示例
　　A. 使用带有复杂 SELECT 语句的 方便过程
　　下面的存储过程从四个表的联接中返回全部作者(提供了姓名)、出版的书籍以及出版社 。该存储过程不 使用任何参数 。

　　USE pubs
　　IF EXISTS (SELECT name FROM sysobjects
　　WHERE name = 'au_info_all' AND type = 'P')
　　DROP PROCEDURE au_info_all
　　GO
　　CREATE PROCEDURE au_info_all
　　AS
　　SELECT au_lname, au_fname, title, pub_name
　　FROM authors a INNER JOIN titleauthor ta
　　ON a.au_id = ta.au_id INNER JOIN titles t
　　ON t.title_id = ta.title_id INNER JOIN publishers p
　　ON t.pub_id = p.pub_id
　　GO

　　au_info_all 存储过程 可以通过以下 步骤执行：

　　EXECUTE au_info_all
　　-- Or
　　EXEC au_info_all

　　假如该过程是批 解决中的第一条语句，则可 使用：

　　au_info_all

　　B. 使用带有参数的 方便过程
　　下面的存储过程从四个表的联接中只返回指定的作者(提供了姓名)、出版的书籍以及出版社 。该存储过程 承受与传递的参数准确匹配的值 。

　　USE pubs
　　IF EXISTS (SELECT name FROM sysobjects
　　WHERE name = 'au_info' AND type = 'P')
　　DROP PROCEDURE au_info
　　GO
　　USE pubs
　　GO
　　CREATE PROCEDURE au_info
　　@lastname varchar(40),
　　@firstname varchar(20)
　　AS
　　SELECT au_lname, au_fname, title, pub_name
　　FROM authors a INNER JOIN titleauthor ta
　　ON a.au_id = ta.au_id INNER JOIN titles t
　　ON t.title_id = ta.title_id INNER JOIN publishers p
　　ON t.pub_id = p.pub_id
　　WHERE au_fname = @firstname
　　AND au_lname = @lastname
　　GO

　　au_info 存储过程 可以通过以下 步骤执行：

　　EXECUTE au_info 'Dull', 'Ann'
　　-- Or
　　EXECUTE au_info @lastname = 'Dull', @firstname = 'Ann'
　　-- Or
　　EXECUTE au_info @firstname = 'Ann', @lastname = 'Dull'
　　-- Or
　　EXEC au_info 'Dull', 'Ann'
　　-- Or
　　EXEC au_info @lastname = 'Dull', @firstname = 'Ann'
　　-- Or
　　EXEC au_info @firstname = 'Ann', @lastname = 'Dull'

　　假如该过程是批 解决中的第一条语句，则可 使用：

　　au_info 'Dull', 'Ann'
　　-- Or
　　au_info @lastname = 'Dull', @firstname = 'Ann'
　　-- Or
　　au_info @firstname = 'Ann', @lastname = 'Dull'

　　C. 使用带有通配符参数的 方便过程
　　下面的存储过程从四个表的联接中只返回指定的作者(提供了姓名)、出版的书籍以及出版社 。该存储过程对传递的参数进行模式匹配，假如没有提供参数，则 使用预设的默许值 。

　　USE pubs
　　IF EXISTS (SELECT name FROM sysobjects
　　WHERE name = 'au_info2' AND type = 'P')
　　DROP PROCEDURE au_info2
　　GO
　　USE pubs
　　GO
　　CREATE PROCEDURE au_info2
　　@lastname varchar(30) = 'D*',
　　@firstname varchar(18) = '*'
　　AS
　　SELECT au_lname, au_fname, title, pub_name
　　FROM authors a INNER JOIN titleauthor ta
　　ON a.au_id = ta.au_id INNER JOIN titles t
　　ON t.title_id = ta.title_id INNER JOIN publishers p
　　ON t.pub_id = p.pub_id
　　WHERE au_fname LIKE @firstname
　　AND au_lname LIKE @lastname
　　GO

　　au_info2 存储过程 可以用多种组合执行 。下面只列出了 部分组合：

　　EXECUTE au_info2
　　-- Or
　　EXECUTE au_info2 'Wh*'
　　-- Or
　　EXECUTE au_info2 @firstname = 'A*'
　　-- Or
　　EXECUTE au_info2 '[CK]ars[OE]n'
　　-- Or
　　EXECUTE au_info2 'Hunter', 'Sheryl'
　　-- Or
　　EXECUTE au_info2 'H*', 'S*'

　　D. 使用 OUTPUT 参数
　　OUTPUT 参数同意外部过程、批 解决或多条 Transact-SQL 语句 拜访在过程执行期间设置的某个值 。下面的示例 缔造一个存储过程 (titles_sum)，并 使用一个可选的输入参数和一个输出参数 。