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

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

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
         StoredProcName := sp_CalledErrorTest1;
         Params.CreateParam(ftString, '@ErrorString', ptOutput);
         Params.CreateParam(ftInteger, 'Result', ptResult);
         ParamByName('@ErrorString').AsString := '';

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


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 Kovalevsky
Moscow, Russia
phone: +7 (095) 203-6451
fax: +7 (095) 290-1252

Other Threads