[求助]如何判断存储过程执行成功与否

zyp

在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

我测试了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)存储过程只能执行第一条，第三条也无法执行。即其中一条语句有错误时，就跳出了存储过程，后面的语句无法得到执行。

徐阿鹏

应该在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应该这样写而且在这种批处理的语句中应该加上事务的开始，事务的结束，及事务的回滚，保证数据的统一性

徐阿鹏

哦，应该是return -100,或其他的数值，不能为0

徐阿鹏

我觉得你对vba 和sql的参数返回理解不够

zyp

谢谢阿鹏指点．

我按照你的写法也通过不了

附Access 2003数据库，SQL底层mdf、ldf文件



最主要的是预存程序中的Ｔ－ＳＱＬ一旦出错，就没有继续往下执行，而是立即退出．

比如说：

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

操作系统：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

徐阿鹏

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

谢谢阿鹏，我先测试一下

zyp

徐阿鹏兄弟的方法可以检测到存储过程是否成功执行，但不能检测到具体是哪一条代码产生的错误，因为只要T-SQL代码出错后，后面的代码都无法执行了．在VBA中輸出傳出參數時,如果其值為null,就表明存储过程沒有成功執行.在存储过程中嵌入事務處理后,就可以保證資料的完整性(T-SQL要麼全部執行,要麼全部不執行) 謝謝阿鵬!