MySQL Forums
Forum List  »  Stored Procedures

2014 - commands out of sync; you can't run this command now
Posted by: chan ming chan
Date: July 25, 2013 09:49PM

I created the following stored procudure, it work just fine when the Delete record and update record is success.

However, the following error occur when the update record is not found.
2014 - commands out of sync; you can't run this command now

What I want to do is ensure the delete and update record all success, or else rollback and display the error code.

MySQL version is 5.0.24


I would be very appreciate if anyone can help..
Many thanks!!!


delimiter $$
DROP PROCEDURE IF EXISTS rollbackA$$

create procedure rollbackA(
IN p_fk_tender_id dec(20))

proc_label: BEGIN


DECLARE max_version_id dec;
DECLARE result_id int;
DECLARE row_cnt INT DEFAULT 0;

DECLARE exit handler for sqlexception rollback;
DECLARE exit handler for sqlwarning rollback;

SET autocommit=0;

START TRANSACTION;


SELECT max(fk_version_id) into max_version_id FROM tbl_tender2_analysis_approval where fk_tender_id = p_fk_tender_id;


IF max_version_id is NULL then

SELECT 1;
LEAVE proc_label; -- version no not found

else

DELETE FROM tbl_tender2_analysis_approval WHERE fk_tender_id=p_fk_tender_id and fk_version_id=max_version_id;
select row_count() into row_cnt;
if row_cnt < 1 then

SELECT 1;
-- not record were delete
ROLLBACK;

end if;

UPDATE tbl_tender2_phase2_station_summary set tender_am_suggest_status=0 WHERE fk_tender_id = p_fk_tender_id;
select row_count() into row_cnt;
if row_cnt < 1 then
SELECT 2; -- not record were update
ROLLBACK;

end if;


update tbl_tender set fk_vendor_id = 0, final_option = 0, final_price = 0 where tender_id = p_fk_tender_id;


END if;

COMMIT;
-- return result
SELECT 0;

END $$
delimiter ;

Options: ReplyQuote




Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.