I'm trying to understand how and why you would use an OUT parameter within a stored procedure. Can someone please give me a good example for using an OUT parameter?

Can you also explain how you access the OUT result in both EDBManager and Delphi?

Thanks in advance.
Michael Riley
GySgt USMC (Retired)
www.zilchworks.com

Michael,

First the 'how to':
- In EDB Manager, the OUT parameters are shown in a window after the execution of the stored procedure.
- In PSM/SQL scrips, you can use them the same way you use var parameters in Delphi procedures, after a CALL to the stored procedure
- In Delphi code you can get the value of an OUT parameter by using ParamByName method, as if it was an IN parameter

Now, why ...
To get results from a procedure
For example, in one of my applications I have a stored procedure that runs a series of tests on the database and returns 3 parameters: the number of errors found, the number of automatically fixed errors and the number of error that were not fixed.

--
Fernando Dias
[Team Elevate]

Michael


I have a script (OK not quite the same) which builds a fairly complex query depending on a couple of IN parameters to build an in-memory table which it then goes on to build (using EXECUTE IMMEDIATE). I added an OUT parameter to give me sight of the query that was built to give me a clue as to what was going wrong.

Roy Lambert

Michael

>>Can you also explain how you access the OUT result in both EDBManager and Delphi?

A little bit of Delphi code (untested, but more-or-less right I think):

var
 aProc: TEDBStoredProc;
 aOutParam: Integer;
begin
 aProc:= TEDBStoredProc.Create(self);
// aProc.DatabaseName ....               //add lines here to set up the proc so it is connected to your database
aProc.StoredProcName:= 'SomeName';     //must exist on the database
aProc.Prepare;                                 //initializes the proc, so you can reference parameters
 if aProc.Prepared then
   begin
      aProc.ParamByName('aInputParam').asInteger:= 1234;
      aProc.ExecProc;                              //runs the procedure
      aOutParam:= aProc.ParamByName('aOutParam').asInteger; //gets the result back
   end;
end;