MySQL Forums
Forum List  »  Stored Procedures

Create Procedure Generating Errors
Posted by: Subramanian Mayuranathan
Date: April 30, 2023 01:09AM

Hi All,

I have just started with MySQL. Trying to create a SP like so. But unable as MySQL throwing many errors

CREATE PROCEDURE IF NOT EXISTS db_dentalclinic.sp_AddTreatmentRecord IN (appointmentid int, natureoftreatment varchar, treatmentrendered VARCHAR, toothno tinyint, treatmentcomplete text, treatmentdate date, OUT Saved int)

DELIMITER //

DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
select 0 into Saved;
ROLLBACK;
RESIGNAL;
END;
BEGIN

START TRANSACTION;
insert into tblTreatments (appointmentid, natureoftreatment, toothnumber, treatmentcomplete, treatmentdate)
VALUES
(appointmentid, 'Root Canal Treatment', toothnumber, 1, CURRENT_DATE());

insert into tblTreatmentHistory (treatmentid, treatmentrendered, posttreatmentadvice, treatmentdate, treatmentcomplete, followupdate, materialsused)
VALUES
(LAST_INSERT_ID(), treatmentrendered, posttreatmentadvice, CURRENT_DATE(), 1, CURRENT_DATE(), materialused);

select LAST_INSERT_ID into Saved;
COMMIT;


END//
DELIMITER ;

Options: ReplyQuote


Subject
Views
Written By
Posted
Create Procedure Generating Errors
364
April 30, 2023 01:09AM


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.