存储过程被用来将两个字符串连接成一个字符串，并将结果返回。创建存储过程：

create procedure strconnect @str1 varchar (20), @str2 varchar (20) , @connect varchar (40) output as select @connect=@str1 + @str2

如果提供三个字符串来执行这一存储过程，将看不到字符串相加的结果。虽然Select 语句用来对result变量赋值，但result结果并没有显示再执行以下语句：

declare @result varchar (40)

execute strconnect 'I am', ' John', ' string'

select 'The result'=@result

则其运行结果为：

The result

----------------------------------------

NULL

(1 row (s) affected)

若增加OUTPUT保留字到EXECUTE语句中便可显示返回参数result 的值，OUTPUT要求参数值被作为一个变量传送，而不是作为一个常量。下面的例子说明@result 变量来存放由存储过程strconnect通过@connect 返回给调用者的结果值，从而使SQL Server能够显示出存储过程的返回值。

declare @result varchar (40)

execute strconnect 'I am', 'John ', @result output

select 'The result'=@result

运行结果为：

The result

-----------------------------------

I am John

(1 row (s) affected)

下面将举一个例子使读者对该命令有更为深刻的理解：

use pubs

go

if exists (select name from sysobjects where name = 'oakland_authors' and type = 'p')

drop procedure oakland_authors

go

/* 创建一个存储过程，该存储过程获取所有居住在加里福尼亚，奥克兰城的作者的信息。*/

use pubs

go

create procedure oakland_authors

as

select au_fname, au_lname, address, city, zip

from pubs..authors

where city = 'oakland'

and state = 'ca'

order by au_lname, au_fname

go

/* 查看该存储过程的源代码*/

select o.id, c.text

from sysobjects o inner join syscomments c on o.id = c.id

where o.type = 'p' and o.name = 'oakland_authors'

/* 将执行该存储过程的权限授予 public 角色*/

grant execute on oakland_authors to public

go

/* 下面对该存储过程进行修改，使其能够显示出所有居住在加里福尼亚的作者而不考虑其它地区居住的作者*/

alter procedure oakland_authors

with encryption

as

select au_fname, au_lname, address, city, zip

from pubs..authors

where state = 'ca'

order by au_lname, au_fname

go

/* 查看该存储过程的源代码*/

select o.id, c.text

from sysobjects o inner join syscomments c on o.id = c.id

where o.type = 'p' and o.name = 'oakland_authors'

go

将该段代码输入到SQL Server Query Analyzer 窗口中运行后结果为。

id text

--------------------------------------------------------------

1557580587 create procedure oakland_authors

as

select au_fname, au_lname, address, city, zip

from pubs..authors

where city = 'oakland'

and state = 'ca'

order by au_lname, au_fname

(1 row (s) affected)

--------------------------------------------------------------------------------------------------------

id text

(1 row (s) affected)

注意：

由于在ALERT PROCEDURE 中使用了WITH ENCTYPTION 保留字所以在查看修改后的存储过程源代码时看到是一些乱码。