关于存储过程的一个奇怪问题!(100分)

F

fwd

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2008-04-26

• #1

CREATE PROCEDURE MyProc AS<br>BEGIN<br>&nbsp; SET XACT_ABORT ON<br>&nbsp; BEGIN TRAN<br><br>&nbsp; INSERT INTO t2 VALUES (1)<br>&nbsp; INSERT INTO t2 VALUES (2) /* Foreign key error */<br>&nbsp; INSERT INTO t2 VALUES (3)<br><br>&nbsp; COMMIT TRAN<br>END<br>以上是我的存储过程.<br>在Delphi中用 ADOStoredProc执行上过程,<br>&nbsp;1. 用ExecProc时,当遇外键错误时,Delphi竟不提示任何错误信息,不知是否执行成功;<br>&nbsp;2. 用Open时,当遇外键错误时,倒是有错误提示,可当过程执行成功,它又提示 <br>&nbsp; &nbsp; 'CommandText does not return a result set'.不能返回结果.<br>我用ADOQuery中的ExecSQL和Open同样也是.<br>&nbsp; 当过程执行错误时,我想得到其错误信息,这点是必须的.<br><br>郁闷哪.不知错在哪里.请帮我!!

 

K

kukujaky

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2008-04-26

• #2

ADOCExecSQL: TADOConnection;<br><br>procedure ExecSql(SQLstr: string);<br>begin<br>&nbsp; try<br>&nbsp; &nbsp; ADOCExecSQL.BeginTrans;<br>&nbsp; &nbsp; ADOCExecSQL.Execute(SQLstr);<br>&nbsp; &nbsp; ADOCExecSQL.CommitTrans;<br>&nbsp; except<br>&nbsp; &nbsp; on E: Exception do<br>&nbsp; &nbsp; begin<br>&nbsp; &nbsp; &nbsp; ADOCExecSQL.RollbackTrans;<br>&nbsp; &nbsp; &nbsp; ShowMessage(E.Message);<br>&nbsp; &nbsp; end;<br>&nbsp; end;<br>end;

 

F

fwd

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2008-04-26

• #3

To kukujaky:<br>&nbsp; 我想这种方法也能实现,问题是我的过程在建立在SQL2000中,语句也较多,也可能以后维护此过程.将SQLstr换成Exec MyProc 不知可以不可以. 我试试....

 

P

penal

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2008-04-26

• #4

SET NOCOUNT ON

 

P

penal

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2008-04-26

• #5

这是因为ADO执行存贮过程后返回的不只有一个结果集, 而是多个, 第一个是成功的, 因此它不报错了.<br><br>var<br>&nbsp; Rst: _Recordset;<br>&nbsp; Affected: OleVariant;<br>begin<br>&nbsp; Rst := &nbsp;ADOConnect.Execute(SQLstr);<br>&nbsp; while Rst &lt;&gt; nil do<br>&nbsp; &nbsp; Rst := Rst.NextRecordSet(Affected);<br>end;

 

F

fwd

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2008-04-26

• #6

To kukujaky:<br>&nbsp; 将SQLstr换成Exec MyProc 不可以,不提示错误.<br>&nbsp; 在Delphi中直接调用过程. &nbsp;Exec MyProc <br>&nbsp; 不用语句段.

 

F

fwd

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2008-04-26

• #7

to Penal:<br>&nbsp; 用SET NOCOUNT ON 法,照旧.帮助中说是不返回计数.<br>&nbsp; 用ExecProc问题是执行不成功后不返回错误.

 

P

penal

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2008-04-26

• #8

SET NOCOUNT ON 是有效的, 我已经用这种方法解决了这种问题. 至于你为什么不行, 可能是没加对地方吧.<br><br>如果不行, 可以用TADOCommand, 看我的笔记

 

F

fwd

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2008-04-26

• #9

To Penal:<br>CREATE PROCEDURE MyProc AS<br>BEGIN<br><br>&nbsp; SET NOCOUNT ON &nbsp; //此处<br>&nbsp; SET XACT_ABORT ON<br>&nbsp; BEGIN TRAN<br><br>&nbsp; INSERT INTO t2 VALUES (1)<br>&nbsp; INSERT INTO t2 VALUES (2) /* Foreign key error */<br>&nbsp; INSERT INTO t2 VALUES (3)<br><br>&nbsp; COMMIT TRAN<br>END<br>我是这样加的,是不是不对,怎么看你的笔记?

 

P

penal

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2008-04-26

• #10

你加得很正确, 关于你提供的存贮过程, 我还特地在我的机器上试验了一下呢, 结果是有效的. 未加SET NOCOUNT之前, ADOStoredProc.ExecProc是不报错的, 加了之后, 执行了就抛出异常了.<br><br>我的笔记在这里: http://www.delphibbs.com/keylife/iblog.asp?author=penal

 

F

fwd

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2008-04-26

• #11

多人接受答案了。