SQL Server联机丛书：执行存储过程
2024-08-31 00:47:59
供稿：网友
execute
执行标量值的用户定义函数、系统过程、用户定义存储过程或扩展存储过程。同时支持 transact-sql 批处理内的字符串的执行
若要唤醒调用函数，请使用 execute stored_procedure 中描述的语法。
语法
执行存储过程：[ [ exec [ ute ] ]
{
[ @return_status = ]
{ procedure_name [ ;number ] | @procedure_name_var
}
[ [ @parameter = ] { value | @variable [ output ] | [ default ] ]
[ ,...n ]
[ with recompile ] 执行字符串：exec [ ute ] ( { @string_variable | [ n ] 'tsql_string' } [ + ...n ] )参数
@return_status
是一个可选的整型变量，保存存储过程的返回状态。这个变量在用于 execute 语句前，必须在批处理、存储过程或函数中声明过。
在用于唤醒调用标量值用户定义函数时，@return_status 变量可以是任何标量数据类型。
procedure_name
是拟调用的存储过程的完全合法或者不完全合法的名称。过程名称必须符合标识符规则。有关更多信息，请参见使用标识符。无论服务器的代码页或排序方式如何，扩展存储过程的名称总是区分大小写。
用户可以执行在另一数据库中创建的过程，只要该用户拥有此过程或有在该数据库中执行它的适当的权限。用户可以在另一台运行 microsoft® sql server™ 的服务器上执行过程，只要该用户有适当的权限使用该服务器（远程访问），并能在数据库中执行该过程。如果指定了服务器名称但没有指定数据库名称，sql server 会在用户默认的数据库中寻找该过程。
;number
是可选的整数，用于将相同名称的过程进行组合，使得它们可以用一句 drop procedure 语句除去。该参数不能用于扩展存储过程。
在同一应用程序中使用的过程一般都以该方式组合。例如，在订购应用程序中使用的过程可以 orderproc;1、orderproc;2 等来命名。drop procedure orderproc 语句将除去整个组。在对过程分组后，不能除去组中的单个过程。例如，drop procedure orderproc;2 是不允许的。有关过程组的更多信息，请参见 create procedure。
@procedure_name_var
是局部定义变量名，代表存储过程名称。
@parameter
是过程参数，在 create procedure 语句中定义。参数名称前必须加上符号 (@)。在以 @parameter_name = value 格式使用时，参数名称和常量不一定按照 create procedure 语句中定义的顺序出现。但是，如果有一个参数使用 @parameter_name = value 格式，则其它所有参数都必须使用这种格式。
默认情况下，参数可为空。如果传递 null 参数值，且该参数用于 create 或 alter table 语句中不允许为 null 的列（例如，插入至不允许为 null 的列），sql server 就会报错。为避免将 null 参数值传递给不允许为 null 的列，可以在过程中添加程序设计逻辑或采用默认值（使用 create 或 alter table 语句中的 default 关键字）。
value
是过程中参数的值。如果参数名称没有指定，参数值必须以 create procedure 语句中定义的顺序给出。
如果参数值是一个对象名称、字符串或通过数据库名称或所有者名称进行限制，则整个名称必须用单引号括起来。如果参数值是一个关键字，则该关键字必须用双引号括起来。
如果在 create procedure 语句中定义了默认值，用户执行该过程时可以不必指定参数。如果该过程使用了带 like 关键字的参数名称，则默认值必须是常量，并且可以包含 %、_、[ ] 及 [^] 通配符。
默认值也可以为 null。通常，过程定义会指定当参数值为 null 时应该执行的操作。
@variable
是用来保存参数或者返回参数的变量。
output
指定存储过程必须返回一个参数。该存储过程的匹配参数也必须由关键字 output 创建。使用游标变量作参数时使用该关键字。
如果使用 output 参数，目的是在调用批处理或过程的其它语句中使用其返回值，则参数值必须作为变量传递（即 @parameter = @variable）。如果一个参数在 create procedure 语句中不是定义为 output 参数，则对该参数指定 output 的过程不能执行。不能使用 output 将常量传递给存储过程；返回参数需要变量名称。在执行过程之前，必须声明变量的数据类型并赋值。返回参数可以是 text 或 image 数据类型以外的任意数据类型。
default
根据过程的定义，提供参数的默认值。当过程需要的参数值没有事先定义好的默认值，或缺少参数，或指定了 default 关键字，就会出错。
n
是占位符，表示在它前面的项目可以多次重复执行。例如，execute 语句可以指定一个或者多个 @parameter、value 或 @variable。
with recompile
强制编译新的计划。如果所提供的参数为非典型参数或者数据有很大的改变，使用该选项。在以后的程序执行中使用更改过的计划。该选项不能用于扩展存储过程。建议尽量少使用该选项，因为它消耗较多系统资源。
@string_variable
是局部变量的名称。@string_variable 可以是 char、varchar、nchar 或 nvarchar 数据类型，最大值为服务器的可用内存。如果字符串长度超过 4,000 个字符，则把多个局部变量串联起来用于 execute 字符串。有关系统提供的 sql server 数据类型更多的信息，请参见数据类型。
[n]'tsql_string'
是一个常量，tsql_string 可以是 nvarchar 或 varchar 数据类型。如果包含 n，则该字符串将解释为 nvarchar 数据类型，最大值为服务器的可用内存。如果字符串长度超过 4,000 个字符，则把多个局部变量串联起来用于 execute 字符串。
注释
如果过程名称的前三个字符为 sp_，sql server 会在 master 数据库中寻找该过程。如果没能找到合法的过程名称，sql server 会寻找所有者名称为 dbo 的过程。若要将存储过程名称解析为与系统存储过程同名的用户定义存储过程，请提供一个完全合法的过程名称。
参数可以通过利用 value 或 @parameter_name = value 来提供。参数不是事务的一个部分；因而如果事务中的参数值更改，且该事务在以后回滚，该参数值不会退回到以前的值。返回给调用方的值总是过程返回时的值。
当一个存储过程调用另一个存储过程时，会产生嵌套。当调用的过程开始执行时，嵌套级会增加，当调用过程执行结束时，嵌套级则会减少。嵌套级最高为32级，超过32级时，会导致整个调用过程链失败。当前的嵌套级存储在 @@nestlevel 函数中。
sql server 目前使用返回值 0 到 -14 来表示存储过程的执行状态。值 –15 到 -99 留作后用。有关保留的返回状态值的列表的更多信息，请参见 return。
因为远程存储过程和扩展存储过程不在事务的作用域中（除非在 begin distributed transaction 语句中发出或者是和不同的配置选项一起使用），所以通过调用执行的命令不能回滚。有关更多信息，请参见系统存储过程和 begin distributed transaction。
当使用游标变量时，如果执行的过程传递一个分配有游标的游标变量，就会出错。
在执行存储过程时，如果语句是批处理中的第一个语句，则不一定要指定 execute 关键字。
使用带字符串的 execute 命令
使用字符串串联运算符 (+) 为动态执行创建长字符串。每个字符串表达式可以是 unicode 与 non-unicode 数据类型的混合。
尽管每个 [n] 'tsql_string' 或 @string_variable 不得超过 8,000 个字节，sql server 语法分析器中对这种串联只进行逻辑处理而不占用物理内存。例如，该语句决不会生成长 16,000 个串联起来的字符串： exec('name_of_8000_char_string' + 'another_name_of_8000_char_string')
在 execute 语句执行前，不会编译 execute 语句内的语句。
数据库环境的更改只在 execute 语句结束前有效。例如，在这个例子的 exec 后，数据库环境是 master： use master exec ("use pubs") select * from authors
权限
存储过程的 execute 权限默认给该存储过程的所有者，该所有者可以将此权限转让给其他用户。当遇到 execute 语句时，即使 execute 语句是在存储过程中，也会检查在 execute 字符串内使用该语句的权限。当运行一个执行字符串的存储过程时，系统会在执行该过程的用户环境中，而不是在创建该过程的用户环境中检查权限。但是，如果某用户拥有两个存储过程，并且第一个过程调用第二个过程，则在第二个过程中不进行 execute 权限检查。
示例a. 使用 execute 传递单个参数
showind 存储过程需要参数 (@tabname)，它是一个表的名称。下面这个例子执行 showind 存储过程，以 titles 为参数值。
说明 showind 存储过程只是用来作为一个例子，pubs 数据库并没有此过程。
exec showind titles
在执行过程中变量可以显式命名：
exec showind @tabname = titles
如果这是 isql 脚本或批处理中第一个语句，则 exec 语句可以省略：
showind titles
-或-
showind @tabname = titles
b. 使用多个参数与一个输出参数
这个例子执行 roy_check 存储过程，传递三个参数。第三个参数 @pc 是输出参数。过程执行完后，返回变量可以从变量@percent
得到。
说明 roy_check 存储过程只是用作举例，pubs 数据库中并没有此过程。
declare @percent intexecute roy_check 'bu1032', 1050, @pc = @percent outputset percent = @percent
c.使用带一个变量的 execute 'tsql_string' 语句
这个例子显示 execute 语句如何处理动态生成的、含有变量的字符串。这个例子创建 tables_cursor 游标来保存所有用户定义表
(type = u) 的列表。
说明 此例子只用作举例。declare tables_cursor cursor
for
select name from sysobjects where type = 'u'
open tables_cursor
declare @tablename sysname
fetch next from tables_cursor into @tablename
while (@@fetch_status <> -1)
begin
/* a @@fetch_status of -2 means that the row has been deleted.
there is no need to test for this because this loop drops all
user-defined tables. */.
exec ('drop table ' + @tablename)
fetch next from tables_cursor into @tablename
end
print 'all user-defined tables have been dropped from the database.'
deallocate tables_cursor
d.使用带远程存储过程的 execute 语句
这个例子在远程服务器 sqlserver1 上执行 checkcontract 存储过程，在 @retstat 中保存返回状态，说明运行成功或失败。
declare @retstat int
execute @retstat = sqlserver1.pubs.dbo.checkcontract '409-56-4008'
e. 使用带扩展存储过程的 execute 语句
下例使用 xp_cmdshell 扩展存储过程列出文件扩展名为 .exe 的所有文件的目录。
use master
execute xp_cmdshell 'dir *.exe'
f. 使用带一个存储过程变量的 execute 语句
这个例子创建一个代表存储过程名称的变量。
declare @proc_name varchar(30)
set @proc_name = 'sp_who'
exec @proc_name
g. 使用带 default 的 execute 语句
这个例子创建了一个存储过程，过程中第一个和第三个参数为默认值。当运行该过程时，如果调用时没有传递值或者指定了默认值，
这些默认值就会赋给第一个和第三个参数。注意 default 关键字有多种使用方法。
use pubs
if exists (select name from sysobjects
where name = 'proc_calculate_taxes' and type = 'p')
drop procedure proc_calculate_taxes
go
-- create the stored procedure.
create procedure proc_calculate_taxes (@p1 smallint = 42, @p2 char(1),
@p3 varchar(8) = 'car')
as
select *
from mytable
proc_calculate_taxes 存储过程可以以多种组合方式执行： execute proc_calculate_taxes @p2 = 'a'
execute proc_calculate_taxes 69, 'b'
execute proc_calculate_taxes 69, 'c', 'house'
execute proc_calculate_taxes @p1 = default, @p2 = 'd'
execute proc_calculate_taxes default, @p3 = 'local', @p2 = 'e'
execute proc_calculate_taxes 69, 'f', @p3 = default
execute proc_calculate_taxes 95, 'g', default
execute proc_calculate_taxes default, 'h', default
execute proc_calculate_taxes default, 'i', @p3 = default
