SQL Server联机丛书：存储过程及其创建[4]

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

有关更多信息，请参见 sql server:buffer manager 对象和 sql server:cache manager 对象。

sql_statement 限制

除了 set showplan_text 和 set showplan_all 之外（这两个语句必须是批处理中仅有的语句），任何 set 语句均可以在存储过程内部指定。所选择的 set 选项在存储过程执行过程中有效，之后恢复为原来的设置。

如果其他用户要使用某个存储过程，那么在该存储过程内部，一些语句使用的对象名必须使用对象所有者的名称限定。这些语句包括：

1. alter table
   
2. create index
   
3. create table
   
4. 所有 dbcc 语句
   
5. drop table
   
6. drop index
   
7. truncate table
   
8. 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)，并使用一个可选的输入参数和一个输出参数。

首先，创建过程：

use pubs
go
if exists(select name from sysobjects
      where name = 'titles_sum' and type = 'p')
   drop procedure titles_sum
go
use pubs
go
create procedure titles_sum @@title varchar(40) = '%', @@sum money output
as
select 'title name' = title
from titles 
where title like @@title 
select @@sum = sum(price)
from titles
where title like @@title
go

接下来，将该 output 参数用于控制流语言。

说明  output 变量必须在创建表和使用该变量时都进行定义。

参数名和变量名不一定要匹配，不过数据类型和参数位置必须匹配（除非使用 @@sum = variable 形式）。