Office中国论坛/Access中国论坛
标题: [求助]如何判断存储过程执行成功与否 [打印本页]
作者: zyp 时间: 2005-9-30 16:54
标题: [求助]如何判断存储过程执行成功与否
在ACCESS2000的ADP项目中，后台为offiece2000自带的MSDE，在一个存储过程中，包含多条T-SQL语句，然后在VBA中调用此存储过程，不论存储过程中的所有T-SQL是否成功执行，均不报告（如果在存储过程中只有单条T-SQL语句且发生错误，VBA会产生错误信息），不知用什么方法可以判断所有的T-SQL语句是否成功执行。
我的代码如下：
存储过程：
Alter Procedure "test1"
(
@ErrNum int output
)
As
set @ErrNum=0
INSERT into a (bmName,zhuguan,Rennum,paixu) values('工程','张三',100,100)
INSERT into a (bmname,zhuguan,Rennum,paixu) values('品管','李四','aa',10)
IF (@@ERROR<> 0) BEGIN
set @Errnum=1
END
RETURN 25
VBA中调用存储过程的代码：
Private Sub Command0_Click()
Dim cn As New ADODB.Connection
Dim cmd As New ADODB.Command
Dim parm0 As New ADODB.Parameter
Set cn = CurrentProject.Connection
cmd.ActiveConnection = cn
cmd.CommandType = adCmdStoredProc
cmd.CommandText = "test1"
cmd.Parameters.Append cmd.CreateParameter("@return_value", adInteger, adParamReturnValue)
parm0.Direction = adParamOutput
parm0.Type = adInteger
parm0.Size = 4
cmd.Parameters.Append parm0
cmd.Execute
MsgBox cmd.Parameters("@return_value").Value
MsgBox parm0.Value
Set parm0 = Nothing
Set cmd = Nothing
Set cn = Nothing
End Sub
存储过程中的第二条INSERT语句的红色部分是错误的（数据类型不对），在VBA中调用时，第一条insert成功执行，而第二条无法执行，但并没有任何提示。
所以在VBA中取得存储过程的输出变量和返回值：
MsgBox cmd.Parameters("@return_value").Value '程序返回空值(MSGBOX无任何字符）
MsgBox parm0.Value '系统出错：null的使用不正确
如果将第二条T-SQL改为正确的或去掉，VBA能返回预期值
MsgBox cmd.Parameters("@return_value").Value '程序返回25
MsgBox parm0.Value ''程序返回0
是不是在存储过程中，一旦出现错误，后面的语句就停止执行？
那么用什么方法才能判断存储过程中的所有T-SQL语句都成功执行，甚至判断是哪行代码出错。
是否可用ＶＢＡ判断输出变量为null判断存储过程没有执行成功？
请多多指教？
[此贴子已经被作者于2005-9-30 9:07:12编辑过]
作者: zyp 时间: 2005-10-2 17:28
我测试了3条insert语句INSERT into a (bmName,zhuguan,Rennum,paixu) values('工程','张三',100,100)
INSERT into a (bmname,zhuguan,Rennum,paixu) values('品管','李四','aa',10)
INSERT into a (bmName,zhuguan,Rennum,paixu) values('包装','王二,5,5)存储过程只能执行第一条，第三条也无法执行。即其中一条语句有错误时，就跳出了存储过程，后面的语句无法得到执行。
作者: 徐阿鹏 时间: 2005-10-11 21:34
应该在tsql语句中加上错误判断语句declare @err然后在每条操作语句上加上INSERT into a (bmName,zhuguan,Rennum,paixu) values('工程','张三',100,100)set @err=@@error
if @err<>0 return 0INSERT into a (bmname,zhuguan,Rennum,paixu) values('品管','李四','aa',10)set @err=@@error
if @err<>0 return 0应该这样写而且在这种批处理的语句中应该加上事务的开始，事务的结束，及事务的回滚，保证数据的统一性
作者: 徐阿鹏 时间: 2005-10-11 21:36
哦，应该是return -100,或其他的数值，不能为0
作者: 徐阿鹏 时间: 2005-10-11 21:40
我觉得你对vba 和sql的参数返回理解不够
作者: zyp 时间: 2005-10-12 03:49
谢谢阿鹏指点．
我按照你的写法也通过不了
附Access 2003数据库，SQL底层mdf、ldf文件
[attach]13580[/attach]
最主要的是预存程序中的Ｔ－ＳＱＬ一旦出错，就没有继续往下执行，而是立即退出．
比如说：
CREATE PROCEDURE "AddToTb1"
(@BmNm varchar(10))
AS
declare @Err int
/*下面这个insert是正确的*/
insert into tb1 (BmName,RenNum) values (@bmNm,10)
set @err=@@error
if @err<>0 begin
return -10
end
/*下面这个insert是错误的,因为Rennum(人数)栏位只能接受数字(int),而传入值是字符
按理说传回值应该是-20才对,但是在执行下面第一句出错后,以后的根本就没执行*/
insert into tb1 (BmName,RenNum) values (@bmNm,@bmNm)
set @err=@@error
if @err<>0 begin
return -20
end
/*下面这个insert是正确的*/
insert into tb1 (BmName,RenNum) values (@bmNm,20)
set @err=@@error
if @err<>0 begin
return -30
end
return -40
在上面的例中，按理说应该是返回-20才对，可在ＶＢＡ中返回的是０长度的字符串．
也就是set @err=@@error
if @err<>0 begin
return -20
end根本就不执行
请问这又是怎么回事
谢谢！
[此贴子已经被作者于2005-10-11 20:47:11编辑过]
作者: zyp 时间: 2005-10-12 04:48
操作系统：windows2000 advanced server
SQL版本：SQL 2000简体中文版
Access版本：Access 2003在下面的VBA代码调用存储过程，并且从存储过程中返回值。
按我原意，应该是返回-20的，但是，当insert into tb1 (BmName,RenNum) values (@bmNm,@bmNm)这条T-SQL语句执行错误后，就直接跳出，其后的所有代码均无法执行,在VBA的msgbox中@return_value返回的只是一个零长度的字符串
这个问题困扰我好久，请各位高手多多指点，谢谢！
================================================================
VBA代码：
Option Compare Database
Option ExplicitPrivate Sub Command0_Click()
Dim cn As New ADODB.Connection
Dim Cmd As New ADODB.Command
Set cn = CurrentProject.Connection
Set Cmd.ActiveConnection = cn
Cmd.CommandType = adCmdStoredProc
Cmd.CommandText = "AddToTb1"
[url=mailto:'@return_value]'@return_value[/url]这个参数名称是固定的,且在输入参数的最前面
Cmd.Parameters.Append Cmd.CreateParameter("@return_value", adInteger, adParamReturnValue)
'输入部门名称参数
Cmd.Parameters.Append Cmd.CreateParameter("@BmNm", adVarChar, adParamInput, 10, "加工")
Cmd.Execute
'输出@return_value
MsgBox Cmd.Parameters("@return_value").Value, vbInformation, "存储过程Return值"
Set Cmd = Nothing
Set cn = Nothing
End Sub============================================================存储过程代码：
CREATE PROCEDURE "AddToTb1"
(@BmNm varchar(10))
AS
declare @Err int
/*下面这个insert是正确的*/
insert into tb1 (BmName,RenNum) values (@bmNm,10)
set @err=@@error
if @err<>0 begin
return -10
end
/*下面这个insert是错误的,因为Rennum(人数)栏位只能接受数字(int),而传入值是字符
按理说传回值应该是-20才对,但是在执行下面第一句出错后,以后的根本就没执行*/
insert into tb1 (BmName,RenNum) values (@bmNm,@bmNm)
set @err=@@error
if @err<>0 begin
return -20
end
/*下面这个insert是正确的*/
insert into tb1 (BmName,RenNum) values (@bmNm,20)
set @err=@@error
if @err<>0 begin
return -30
endreturn -40===========================================================
表tb1的结构：(bmName部门名称RenNum人数)
ID BmName RenNum
1 行政部 10
2 业务部 25
3 物料课 34
作者: 徐阿鹏 时间: 2005-10-12 17:35
tsql改成：CREATE PROCEDURE AddToTb1
(@BmNm varchar(10),
@错误 nvarchar(50)='' output)
AS
set nocount on
declare @err intbegin transaction /*下面这个insert是正确的*/
insert into tb1 (BmName,RenNum) values (@bmNm,10)
set @err=@@error
if @err<>0
begin
rollback transaction
select @错误='未知错误'
return -100
end
/*下面这个insert是错误的,因为Rennum(人数)栏位只能接受数字(int),而传入值是字符
按理说传回值应该是-20才对,但是在执行下面第一句出错后,以后的根本就没执行*/
insert into tb1 (BmName,RenNum) values (@bmNm,'hahah')
set @err=@@error
if @err<>0
begin
rollback transaction
select @错误='未知错误'
return -200
end
/*下面这个insert是正确的*/
insert into tb1 (BmName,RenNum) values (@bmNm,20)
set @err=@@error
if @err<>0
begin
rollback transaction
select @错误='未知错误'
return -300
endcommit transaction
select @错误='执行成功'
return 0vba 改成
Private Sub Command0_Click()
On Error GoTo err
Dim cmd As New ADODB.Command
Dim Par As New ADODB.ParameterSet cmd.ActiveConnection = CurrentProject.Connectioncmd.Parameters.Append cmd.CreateParameter("@return_value", adInteger, adParamReturnValue, 8)
Set Par = cmd.CreateParameter("@BmNm", adVarChar, adParamInput, 10, "hahaha")
cmd.Parameters.Append Par
Set Par = cmd.CreateParameter("@错误", adVarChar, adParamInputOutput, 50, "")
cmd.Parameters.Append Parcmd.CommandText = "AddToTb1"
cmd.CommandType = adCmdStoredProc
cmd.ExecuteMsgBox cmd.Parameters("@return_value").Value, vbInformation, "系统提示"
MsgBox Par.Value, vbInformation, "系统提示"Exit Sub
err:
MsgBox "程序错误！", vbInformation, "系统提示"
End Sub
不过如果不加on error 的话就直接提示你数值转换发生错误了
作者: zyp 时间: 2005-10-12 23:16
谢谢阿鹏，我先测试一下
作者: zyp 时间: 2005-10-20 22:35
徐阿鹏兄弟的方法可以检测到存储过程是否成功执行，但不能检测到具体是哪一条代码产生的错误，因为只要T-SQL代码出错后，后面的代码都无法执行了．在VBA中輸出傳出參數時,如果其值為null,就表明存储过程沒有成功執行.在存储过程中嵌入事務處理后,就可以保證資料的完整性(T-SQL要麼全部執行,要麼全部不執行) 謝謝阿鵬!
作者: 徐阿鹏 时间: 2005-10-20 22:54
但是，我在应用中发现有时输入输出的参数有时并不能从sql传递到vba，试过vba接收不到output的数据，但是又不是全部是这样，郁闷的很[em06]
作者: zyp 时间: 2005-10-21 06:48
以下是引用徐阿鹏在2005-10-20 14:54:00的发言：
但是，我在应用中发现有时输入输出的参数有时并不能从sql传递到vba，试过vba接收不到output的数据，但是又不是全部是这样，郁闷的很[em06]
我在这方面测试的不是很多，但是只要存储过程中的T-SQL不出错，还是可以按要求返回值到VBA中，尚未遇到阿鹏兄的问题。
下面是阿鹏的VBA：
Dim Par As New ADODB.Parameter
Set cmd.ActiveConnection = CurrentProject.Connection
cmd.Parameters.Append cmd.CreateParameter("@return_value", adInteger, adParamReturnValue, 8)
Set Par = cmd.CreateParameter("@BmNm", adVarChar, adParamInput, 10, "hahaha")
cmd.Parameters.Append Par
Set Par = cmd.CreateParameter("@错误", adVarChar, adParamInputOutput, 50, "")
cmd.Parameters.Append Par
改写成：
Dim Par0 As New ADODB.Parameter
Dim Par1 As New ADODB.Parameter
Set cmd.ActiveConnection = CurrentProject.Connection
cmd.Parameters.Append cmd.CreateParameter("@return_value", adInteger, adParamReturnValue, 8)
Set Par0 = cmd.CreateParameter("@BmNm", adVarChar, adParamInput, 10, "hahaha")
cmd.Parameters.Append Par0
Set Par1 = cmd.CreateParameter("@错误", adVarChar, adParamInputOutput, 50, "")
cmd.Parameters.Append Par1
当然，改写前的代码绝对是问题的，因为我已经测试过。不过有些问题的出现总是莫明其妙，说不定换一种方式可以解决
[此贴子已经被作者于2005-10-20 22:57:59编辑过]
作者: 徐阿鹏 时间: 2005-10-24 16:29
谢谢，我试试
| 欢迎光临 Office中国论坛/Access中国论坛 (http://www.office-cn.net/) |
Powered by Discuz! X3.3 |