[EMAIL PROTECTED] wrote > I have a stored procedure to calculate total cost. It goes through customer's > shopping cart and add total cost for each item in the shopping cart: > > CREATE DBPROC getSCSubTotal (IN sc_id fixed(10,0), OUT sub_total fixed(17,2)) > AS > VAR cost fixed(17,2); qty fixed(3,0); > BEGIN > set sub_total=0; > SELECT scl_cost,scl_qty from tpcw.shopping_cart_line where scl_sc_id=1; > while $rc=0 do begin > fetch into :cost, :qty; > set sub_total=sub_total+cost*qty; > end; > END; > > The strange thing is when it executes, it always add 1 more cost. For example, > there are 3 items in shopping cart, > item1 cost1 qty1 > item2 cost2 qty2 > item3 cost3 qty3 > after execution, sub_total=cost1*qty1+cost2*qty2+cost3*qty3++cost3*qty3 > > I think I copyed the sample from reference manual on page 149, except for > using BEGIN/END instead of TRY/CATCH. > > Aslo, is there a message explaination manual for SQL return code? > > Thanks, > Jenny > _______________________________________________
Hi Jenny, the last fetch statement in your fetch loop will not yield a result row, instead $rc = 100 will be returned. You simply should check $rc after execution of the fetch statement : while $rc=0 do begin fetch into :cost, :qty; if $rc = 0 then set sub_total=sub_total+cost*qty; end; A description of the SQL return codes can be found at http://www.sapdb.org/htmhelp/c5/e3c635d0998e4be10000009b38f839/frameset.htm Thomas --- Thomas Anhaus SAPDB, SAP Labs Berlin _______________________________________________ sapdb.general mailing list [EMAIL PROTECTED] http://listserv.sap.com/mailman/listinfo/sapdb.general
