存储过程中返回结果
从存储过程中返回结果有三种方式:
1、返回结果集
这是客户端应用程序返回结果的最通用的方法。结果集是通过使用select语句选择数据产生的。结果集可以从永久表、临时表或局部变量中产生。将结果返回到另一个存储过程不是一种有效的方法。存储过程不能访问另一个存储过程建立的结果集。
例如从永久表中返回结果集:
use pubs
go
create procedure ap_createresultfrompermtable
as
select au_iname from authors
go
例如从局部变量中创建结果集:
use pubs
go
create procedure ap_createresultfromvariable
as
declare @au_iname char(20)
select @au_iname = au_iname from authors
where au_id = '172-32-1176’
select @au_id
go
2、设置output参数的值
输出参数经常用来从存储过程中检索出结果。如果某个参数在传输到存储过程中时被定义成output,则对该参数的任何修改在退出存储之后仍然有效。
例如:
use pubs
go
create procedure ap_setoutputvar @count integer output
as
select @count = count(*) from authors
go
从输出参数中检索出值:
use pubs
go
create procedure ap_getoutputvar
as
declare @num integer
execute ap_setoutputvar @num output
print “the count is”+convert(char,@num)
go
· 将游标使用成output参数。游标可以使用output(输出)参数,但不能使用成输入参数。也就是说,游标可以作为结果返回,但却不能传输到过程中去。当游标被用作参数时,需要限定其为output和varying。varying关键字指出该结果集要用来支持输出参数。这样就提供了将结果集返回到调用过程的能力。
例如:
use pubs
go
create procedure gettitlecount @count_cursor cursor varying output
as
set @count_cursor = cursor
for
select au_id,count(*)
from titleauthors
group by au_id
open @count_cursor
go
3、通过return参数返回状态
这是一种从存储过程返回错误码的方法。存储过程总是返回一个状态值,用户也可以使用return语句返回自己的状态。
例如:
use pubs
go
create procedure ap_setreturnstatus
as
declare @count integer
select @count = count(*) from authors
if @count = 0
return(1)
else
return (0)
go
例如检索出返回的状态:
use pubs
go
create procedure ap_getreturnstatus
as
declare @status integer
execute @status = ap_setreturnstatus
if @status = 1
print “no rows found”
else
print “successful”
go
在存储过程中进行错误处理
如同其它程序一样,在存储过程中进行错误处理是非常重要的。系统变更@@error在执行每一个transact sql语句之后都会得到一个值。对于成功的执行,@@error的值为0,如果出现错误,则@@error中将包含错误信息。@@error系统变量对存储过程的错误处理是非常重要的。
注意:为了防止错误,@@error所能设置的值在sysmessages表的“error”中反映了出来。
在存储过程中的错误有两种类型:
1、数据库相关的错误
这些错误是由数据库的不一致性引起的,系统使用非0的@@error值表示特定的数据库问题。在transact sql执行之后,可以通过@@error获得所出现的错误。如果发现@@error不为0,则必须采取必要的行动,大多数情况下,存储将不再继续进行处理而返回。下面的示例展示了典型的获取数据库错误的方法。该过程将错误代码放置到输出变量中,这样,调用程序就能够访问到。
use pubs
go
create procedure ap_trapdatabaseerror @return_code integer output
as
update authors set au_iname = “jackson”
where au_iname = “smith”
if @@error <> 0
begin
select @return_code = @@error
return
end
else
@return_code = 0
go
2、业务逻辑错误
这些错误是由于违反了业务规则而引起的。要获取这些错误,首先需要定义业务规则,基于这些规则,需要在存储过程中增加必要的错误检测代码。人们经常使用raiserror语句通报这些错误。raiserror提供了返回用户定义错误及将@@error变量设置成用户定义错误号的能力。错误消息可以被动态地建立,或者基于错误号从“sysmessages”表中检索到。一旦出现了错误,错误就会以一种服务器错误消息的方式返回到客户机。下面是raiserror命令的语法:
raiserror (msg_id | msg_str, severity, state
[, argument ][,…n]])
[with options]
msg_id指明用户定义消息的id,该消息存储在“sysmessages”系统表中。
msg_str用于动态创建消息的消息字符串。这与c语言中的“printf”非常相似。
severity定义用户赋值的错误消息严重程度。
state是从1到127的任意整数值,它表示错误的调用状态信息。负数的state值将缺省为1。
options指明错误的定制选项。options的有效值如下:
1)log。
将错误记录到服务器错误日志和nt事件日志中。该选项需要消息带有从19到25的严重程度。而只有系统管理员才能发出这种消息。
2)nowait。
将消息立即发送到客户端服务器。
3)seterror。
不管其严重级别如何,将@@error的值设置为msg_id或5000。
远程过程调用
sql server提供了调用驻留在不同服务器上的存储过程的能力。调用这样的存储过程称谓远程存储过程调用。为了使得调用能从一个sql server转移到另一个服务器,两个服务器应该相互定义成对方的有效远程服务器。
设置远程服务器的配置:
· 扩展某个服务器的组。
· 右击该服务器并点击“properties”。
· 设置选项“allow other sql servers to connect remotely to this sql server via rpc”。
· 设置“query time out”选项的值,该值指定从一个查询处理返回所能等待的秒数。缺省值为0,表示允许无限的等待时间。
· 设置完成配置选项之后,点击“ok”。
· 重新启动服务器之后,修改将会生效。
· 在另一台远程服务器上重复相同的步骤。
调用远程存储过程需要指明服务器的名称,后带数据库的名称和拥有者的名称。下面是在不同的服务器(server2)上调用一个存储过程的示例。
exec server2.pubs.dbo.myproc
豆豆的后话:
这里只是粗浅的介绍了sql server常用的知识,对象也是基于sql server数据库编写应用程序的编程人员,而非数据库管理者。但对于应用程序编程者,了解数据库的管理也是非常有用的。建议在以后的时间自行去了解数据库的管理,这对于优化程序也是相当有用的。 from:/?action-viewthread-tid-519