Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB SQL » View Thread |
Messages 1 to 4 of 4 total |
StoredProcedure being accessed by TEDBStoreProc |
Sun, Jun 17 2012 9:38 PM | Permanent Link |
IQA | 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; |
Mon, Jun 18 2012 2:49 AM | Permanent Link |
Uli Becker | 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 |
Wed, Jun 20 2012 2:15 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | 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 |
Wed, Jun 20 2012 6:40 PM | Permanent Link |
IQA | > 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. |
This web page was last updated on Sunday, May 5, 2024 at 10:18 AM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |