Hi Guys,

I just want to make sure this is efficient regarding where the PREPARE /
UNPREPARE should be taking place (in the SQL or via the TEDBStoreProc)

The stored procedure is...

BEGIN
DECLARE aCursor CURSOR FOR aStmt;
DECLARE aBalance DECIMAL(19,2);
SET aBalance = 0;
PREPARE aStmt FROM 'SELECT SUM(trans_total) AS balance FROM trans WHERE
res_num = ''' + resnum + ''' GROUP BY res_num';
OPEN aCursor;
FETCH FIRST FROM aCursor ('balance') INTO aBalance;
CLOSE aCursor;
UNPREPARE aStmt;
SET balance = aBalance;
END

And the code I use to run the TEDBStoredProc is...

Currency myBalance = 0;
GetBalanceProcedure->Prepare();
GetBalanceProcedure->ParamByName("resnum")->AsString = resnum;
GetBalanceProcedure->ExecProc();
myBalance = GetBalanceProcedure->ParamByName("balance")->AsCurrency;
GetBalanceProcedure->UnPrepare();

return myBalance;

Phil,

how about using a function with an in-param "ResNum" instead? E.g.:

DECLARE aBalance DECIMAL(19,2) DEFAULT 0;
Execute Immediate 'Select SUM(trans_total) INTO ? FROM trans WHERE
res_num = ? GROUP BY res_num' using aBalance, ResNum;
RETURN aBalance;

Regards Uli

Phil,

<< I just want to make sure this is efficient regarding where the PREPARE /
UNPREPARE should be taking place (in the SQL or via the TEDBStoreProc) >>

Actually, if you're going to always call the TEDBStoredProc.UnPrepare method
in the Delphi code, you won't need the UNPREPARE in the actual procedure.
This will save some time if you want to execute the stored procedure
multiple times without calling the UnPrepare method.  In such a case, the
STATEMENT variable used in the procedure will stay prepared, so it won't
need to be re-compiled for every execution.

If you have any other questions, please let me know.

Tim Young
Elevate Software
www.elevatesoft.com

> Actually, if you're going to always call the TEDBStoredProc.UnPrepare
> method in the Delphi code, you won't need the UNPREPARE in the actual
> procedure. This will save some time if you want to execute the stored
> procedure multiple times without calling the UnPrepare method.  In such
> a case, the STATEMENT variable used in the procedure will stay prepared,
> so it won't need to be re-compiled for every execution.

Perfect Tim, that's the answer I was looking for, much appreciated.

Thanks Uli for your input too. Love this support group.

Phil.