Rashid Khan

Rashid Khan

  • NA
  • 159
  • 0

problem in mysql cursor

Nov 4 2010 2:18 PM

my friend again i m need your help. this time in mysql.

i create this sp in mysql. for checking if client receive qty more then issue then it give 0

PROCEDURE `sp_CheckItemReceipt`( Out c_result int(11),
IN c_ItemCode nvarchar(15),
IN c_InvoiceNo int(11),
IN c_qty int(11)
)
BEGIN
DECLARE Qty_Recd int (11);
DECLARE Qty_issued int(11);

DECLARE get_rcptkey cursor for
select sum(qty) from Receiptdetail where Itemcode = c_ItemCode and invoiceno = c_Invoiceno;

DECLARE get_rcpt CURSOR for
    select qty  from invoicedetail inner join invoicemaster
    on invoicemaster.invoicemasterid =  invoicedetail.invoicemasterid
    where Itemcode = c_ItemCode and invoicedetail.invoiceno= c_Invoiceno;

open get_rcptkey;
FETCH NEXT FROM get_rcptkey into Qty_Recd;



open get_rcpt;

FETCH NEXT FROM get_rcpt into Qty_issued;

close get_rcptkey;

if (Qty_Recd +c_qty) > Qty_issued
then
set c_result= 0;

else

set c_result= 1;
end if;
CLOSE get_rcpt;

END

i use it in sqlserver 2005 it giving relevant result but here its always give 1 so i cannot stop user to receiving qty more then issue.
please tell me where i m making mistake
waiting for our value able reply
Thanks

Answers (1)