以下存储过程返回所有作者以及他们的文章和出版者：

use pubs

if exists (select name from sysobjects

where name='author_infor' and type='p')

drop procedure author_infor

go

create procedure author_infor as

select au_l name, 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

在该存储过程中使用参数：

use pubs

if exists (select name from sysobjects

where name='author_infor' and type='p')

drop procedure author_infor

go

use pubs

go

create procedure author_infor

@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 ttitles 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

在该存储过程中使用了OUTPUT 保留字返回值，首先创建存储过程：

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 salequa @stor_id char (4) ,@sum smallint output

as

select ord_num, ord_date, payterms, title_id, qty

from sales

where stor_id = @stor_id

select @sum = sum (qty)

from sales

where stor_id = @stor_id

go

然后在Query Analyzer 中调用存储过程：

declare @totalqua smallint

execute salequa '7131',@totalqua output

if @totalqua<=50

select '销售信息'='销售等级为销售量为'+rtrim (cast ( @totalqua as varchar (20) ) )

if @totalqua>50 and @totalqua<=100

select '销售信息'='销售等级为销售量为'+rtrim ( cast ( @totalqua as varchar (20 ) ) )

if @totalqua>100

select '销售信息'='销售等级为销售量为'+rtrim ( cast ( @totalqua as varchar (20 ) ) )