Board index » delphi » Returning values from SQL Server stored proc to Delphi

Returning values from SQL Server stored proc to Delphi

I have a stored procedure that returns no record set, from which, in Delphi,
I want to retrieve both a error string passed in as an output parameter and
the stored procedure's integer result parameter. I wrote some sample stored
proc's that raise an error and return both an error string and the @@Error
as the result parameter. I  got it to work OK from the SQL Enterprise
Manager, but in Delphi, the string comes back empty and the result is always
0.

The simple test stored proc looks like this:
============================================================
CREATE PROCEDURE sp_CalledErrorTest1
     @ErrorString varchar(100)  OUTPUT
AS

SELECT @ErrorString = 'Error Message from sp_CalledErrorTest1'

RAISERROR( 52001, 18, -1, 'From sp_CallerErrorTest' )

RETURN @@Error
=============================================================

My Delphi code looks like this:
=============================================================
with StoredProc1 do
    begin
         StoredProcName := sp_CalledErrorTest1;
         Params.CreateParam(ftString, '@ErrorString', ptOutput);
         Params.CreateParam(ftInteger, 'Result', ptResult);
         ParamByName('@ErrorString').AsString := '';

         try
              ExecProc;
         except on E: EDBEngineError do
             begin
                  GetResults;
                  ShowMessage( ParamByName('@ErrorString').AsString );
                  ShowMessage( 'Error ID = ' +
ParamByName('Result').AsString );
             end;
         end;

    end;
==============================================================

I have my ShowMessage lines inside my try...except block because the stored
proc will always raise an error. The problem is that the first ShowMessage
shows a blank line, when it should show 'Error Message from
sp_CalledErrorTest1', and returns error ID 0 when it should bring back
52001. Again, when I run it from Enterprise Manager, I get the correct
return values.

What am I doing wrong here. Does the fact that my stored proc does not bring
back a dataset somehow derail my return values?

Thanks for any help anyone can provide,

Steve C.

 

Re:Returning values from SQL Server stored proc to Delphi


Hi, Steve

I'm not guru, but... with yours RAISERROR
command RETURN @@Error in stored proc not run.
RAISERROR generate exception and flow execution
exit from sp before RETURN @@Error.

PS. Sorry, me english only for read.

Best wishes,
Oleg

----------------------------------------------------
Oleg Kovalevsky
Moscow, Russia
e-mail: ol...@aha.ru
phone: +7 (095) 203-6451
fax: +7 (095) 290-1252

Other Threads