SQL stored procedure internal RAISERROR client cannot catch the wrong solution

We have a Sea macro commercial ERP3, the inventory is written with stored procedures, reduce inventory will first check the negative inventory, such as there are 5 you think of the library 6, the stored procedure is RAISERROR (' Insufficient inventory ', 16, 1).

After the recent release of this version, some customers have reported negative inventory at times.

Another, our software in special cases will operate both local and remote two databases, open two SQL transactions, easy to produce a baffling error.

Daoteng a burst, the results are summarized here, Baidu can not find the answer, I hope someone will meet to work.

{***************************************** Test purpose ******************************sql The inventory quantity is checked first in the stored procedure, if the inventory is negative RAISERROR (' Insufficient inventory ', 16, 1), this time found that the client will not intercept this error. Tested found: 1: Compare insurance with Adoquery.open.     Can grasp the internal raiseror of the stored procedure errors, directly on the error. Use Adoquery.execsql, Adostoredproc.execproc, Connection.Execute can not grasp the error, 2: These methods are able to take the return value of the stored procedure, then write the stored procedure,     Must return an error code immediately after raiseerror, in principle return 0 means no error, and the other non-0 values are error codes. So the program can take this value, to determine whether this value = 0, not 0 is error}unit unit1;interfaceuses Windows, Messages, sysutils, variants, Classes, Graphics, Controls, Forms, Dialogs, Stdctrls, Buttons, DB, ADODB, Grids, Dbgrids;type TForm1 = Class (Tform) BTN_BYQUERY:TBITBT    N    BTN_BYPROC:TBITBTN;    BTN_BYCONN:TBITBTN;    Conn_main:tadoconnection;    Qry_main:tadoquery;    Asp_r:tadostoredproc;    Label1:tlabel;    Txt_sql:tmemo;    Qry_r:tadoquery;    Label2:tlabel;    Lbl_total:tlabel;    Label3:tlabel;    Ds_main:tdatasource;    Grd_main:tdbgrid;    Label4:tlabel;    Txt_info:tmemo;    Cbx_execsql:tcombobox; Procedure BTN_BYQUERYCLIck (Sender:tobject);    Procedure Formcreate (Sender:tobject);    Procedure Btn_byprocclick (Sender:tobject);  Procedure Btn_byconnclick (Sender:tobject);    Private {Private declarations} public//read result function Showresult:integer;  Procedure Showinfo (sinfo:string= "; ltime:boolean=true); End;var form1:tform1;implementation{$R *.dfm}procedure tform1.btn_byprocclick (sender:tobject); var asp:               Tadostoredproc;    N:integer;                         S:string;begin Asp:=asp_r;        With ASP do try close;        Connection.begintrans; Execute ASP.        procedurename:= ' Testerror '; Asp.        Parameters.clear; Asp.        Parameters.createparameter (' @RETURN_VALUE ', Ftinteger, Pdreturnvalue, fgunassigned);        Asp.parameters.CreateParameter (' @sComment ', ftstring, Pdinput, 254, ' Adostoredproc '); Asp.        Execproc; N:=round (ASP.        parameters.paramvalues[' @RETURN_VALUE ']); Adostoredproc Test Results ***********After analysis found that in the stored procedure raiseerror with Adostoredproc, can only use return 0 to judge If N=0 then Showinfo (' Adostoredpro C execution succeeds ') ELSE begin if Connection.errors.count>0 then s:= #13 +connection. Errors[0].            Description else s:= '; Raise exception.create (' Adostoredproc Error!          Error code: ' +inttostr (n) +s);        End    Commit TRANSACTION Connection.committrans; Except on X:exception do begin if connection. Intransaction then connection.            RollbackTrans;        Showinfo (X.message);    End    End                  Showresult;end;procedure Tform1.btn_byqueryclick (sender:tobject); var qry:tadoquery;                     L,lexec,lopen:boolean;begin Qry:=qry_r;    lexec:=cbx_execsql.itemindex=0;    Lopen:=not lexec;        With Qry do try close;        Connection.begintrans;        Execute sql.text:= ' declare @n int, @n2 int ';        Sql.add (' exec @n=testerror ' +quotedstr (' adoquery-' +cbx_execsql.text) + '); If Lopen then SQL.ADD (' Select @n as Vresult ');                 Open//************* key: Execsql will not cause an error, and open will result in an error **********//if Lexec then Execsql                   Unable to grasp the stored procedure raiseerror else open;        Open can catch Raiserror if not IsEmpty then Showinfo (' Adoquery execution succeeds, return value: ' +fields[0].asstring ') Else showinfo (' execution complete, no return value ');    Commit TRANSACTION Connection.committrans; Except on X:exception do begin if connection. Intransaction then connection.            RollbackTrans;        Showinfo (X.message);    End    End                       showresult;end;//with connection execution procedure Tform1.btn_byconnclick (sender:tobject); var rec:_recordset;    Conn:tadoconnection;                             s:string;                      N, N2, Nr:integer;begin Conn:=conn_main;    Nr:=-1;    Rec:=nil; With Conn does try if not conn. Connected then Conn.        Open; Conn.        BeginTrans; With Qry_r do begin sql.text:= ' declare @n int, @n2 int ‘;            Sql.add (' exec @n=testerror ' Connection ');            Sql.add (' Select @n as Vresult ');        S:=sql.text;        End        Perform compatible sql2000, 2008****************////test discovery with the lowest connection: stored procedure RAISERROR connection is not caught, can only be judged by the return value Using Rec.fields[0].value to get the return value is prone to inexplicable errors, and you need to continue to find reliable ways to//nr:=connection.        Execute (s) (0); Rec:=conn.        Execute (S, N2, Eoasyncfetch); Rec:=conn.                 Execute (s); , Cmdtext, [Eoasyncfetch]//if (Assigned (REC)) and (not rec. EOF) then Nr:=rec. Fields[0].        Value; If Nr<>0 then Showinfo (' connection error, result return value: ' +inttostr (NR)) Else Showinfo (' Connection executed successfully!        ‘);    Submit Conn.committrans; Except on X:exception do BEGIN if Conn. Intransaction then Conn.            RollbackTrans;        Showinfo (X.message);    End    End                          showresult;end;//Read result function Tform1.showresult:integer;var qry:tadoquery; I:integer;begin result:=-1;    Qry:=tadoquery.create (self);    Qry.connection:=qry_main.connection;        With Qry do try Qry_main.disablecontrols;        Close        Sql.text:= ' Select COUNT (1) from employee ';        Open        If not IsEmpty then Lbl_total.caption:=inttostr (fields[0].value);            Table with Qry_main do begin close;            sql.text:= ' SELECT Top Ten * from the employee ORDER by NID Desc ';            Open For i:=0 to FieldCount-1 do fields[i].        displaywidth:=14;    End    Except on X:exception do ShowMessage (x.message);    End    Qry_main.enablecontrols; If assigned (qry) then Freeandnil (qry); end;procedure tform1.formcreate (sender:tobject); begin try Lbl_total.capti        On:= ";        Conn_main.open;    Showresult;    Except on X:exception do ShowMessage (x.message); End;end;procedure Tform1.showinfo (sinfo:string= "; ltime:boolean=true); Begin Txt_info. Lines.add (FormatDateTime (' Yyyy-mm-dd HH:mm:ss ', noW) + "+sinfo"); End;end. 

　　

SQL stored procedure internal RAISERROR client cannot catch the wrong solution