0
CREATE DEFINER=`root`@`localhost` PROCEDURE `getUserQuoteValue`(IN itemId bigint,IN amount float,IN quantity int)
BEGIN
  DECLARE ty Varchar(40);
    SELECT type INTO ty
    FROM quoteitem WHERE id = @itemid;
    IF 
    ty='servicemargin' then
   update quoteitem set  type= 'servicemargin',amount=amount, quantity=quantity, total=amount*quantity, margin=total/100 *(select marginvalue from setting where type='servicemargin'), finaltotal=(total-margin)where Id=itemid;

else
  update quoteitem set type='material',amount=amount, quantity=quantity, total=amount*quantity, margin=total/100 *(select marginvalue from setting where type='material'), finaltotal=(total-margin)where Id=itemid;
end if;


END

this is my stored procedure .if i call from the stored procedure(itemid,quantity,amount)it should be represented in the quote table ...i have created four tables (setting,quote,quoteitem,user)now how could i represent the result in the quote table what are the expression wanted .please give me explanation with the query

P.Salmon
  • 17,104
  • 2
  • 12
  • 19
mohan prabhu
  • 1
  • 1
  • please someone tell me how to represent it on quote table ...while the stored procedure is called – mohan prabhu Jul 30 '18 at 08:01
  • I am unclear what you are asking for. You say you have created 4 tables but only refer a table you have not created (quoteitem) in the procedure so where do the 4 tables come into it? Also you SELECT type INTO ty FROM quoteitem WHERE id = @itemid; but maybe this should be the passed parameter itemid and if this returns more than 1 value only 1 will be stored in ty which is possibly not what you want. – P.Salmon Jul 30 '18 at 08:03
  • It would help if you could include sample call statements and what you expect in the 4 tables after the procedure is called (as text in the question). – P.Salmon Jul 30 '18 at 08:05
  • so should i insert values in the quote item my HR said to update automatically in the quoteitem table while i call the values – mohan prabhu Jul 30 '18 at 08:06
  • 1
    There isn't enough information for me to help you. It would help if you could add sample data from quoteitem before you call the procedure for type = 'servicemargin' , and what quoteotem would look like after the call. And do the same for type='material'. – P.Salmon Jul 30 '18 at 08:42
  • i have edited my post please reconsider – mohan prabhu Jul 30 '18 at 10:26
  • I see no edits. – P.Salmon Jul 30 '18 at 11:28
  • You may be looking for mysql multi table syntax try googlin and her's an example https://stackoverflow.com/questions/15037883/mysql-update-syntax-with-multiple-tables-using-where-clause – P.Salmon Jul 30 '18 at 11:41

0 Answers0