CREATE PROCEDURE jobseeker_add(IN inTitle VARCHAR(50), 
                               IN inFname VARCHAR(50),
                               IN inLname VARCHAR(50),
			       IN inJobTitle VARCHAR(100),
			       IN inAddLine1 VARCHAR(100),
			       IN inAddLine2 VARCHAR(100), 
			       IN inCity VARCHAR(50),
			       IN inState VARCHAR(50),
			       IN inZip VARCHAR(50),
			       IN inCountryId INT,
			       IN inPhone VARCHAR(50),
			       IN inMobile VARCHAR(50),
			       IN inEmail VARCHAR(100),
			       IN inDob DATE,
			       IN inCvProfile TEXT)

BEGIN

DECLARE jobseekerId INT;

START TRANSACTION;
    
		# Insert a new record into jobseeker and obtain the new jobseeker ID
		INSERT INTO tbl_jobseekers (js_title, js_fname, js_lname, js_job_title, js_addressline1, js_addressline2,js_city, js_state, js_zipcode, js_country_id, js_phone, js_mobile, js_email, js_dob) 
		VALUES (inTitle, inFname, inLname, inJobTitle, inAddLine1, inAddLine2 ,inCity ,inState, inZip, inCountryId,inPhone, inMobile, inEmail, inDob);
		
		# Obtain the new jobseeker ID
		SELECT LAST_INSERT_ID() INTO jobseekerId;

                # insert jobseeker cv
		INSERT INTO tbl_jobseeker_cv (jobseeker_id, cv) VALUES (jobseekerId, inCvProfile );
		
		
		#return jobseeker id
		SELECT jobseekerId;
		
COMMIT;
END$$

Listing 1. STORED PROCEDURE:

create procedure sp_answer(IN name VARCHAR(20))
begin
declare exit handler for not found rollback;
declare exit handler for sqlwarning rollback;
declare exit handler for sqlexception rollback;


start transaction

-- insert into ...
-- update ...
-- any other DDL operation

-- select 1
commit;

end