16:36 06-08-2023


Create the table Emp_EH. Below are its fields.
●	EmpID (Primary Key)
●	EmpName
●	EmailAddress


AI response:

CREATE TABLE Emp_EH (
    EmpID INT PRIMARY KEY,
    EmpName VARCHAR(50),
    EmailAddress VARCHAR(100)
);


Create a procedure to accept the values for the columns in Emp_EH. Handle the error using exception handling concept. 
Show the message as “Error occurred” in case of anything wrong.


DELIMITER //

---CREATE PROCEDURE Insert_Emp_EH(
    IN input_EmpID INT,
    IN input_EmpName VARCHAR(50),
    IN input_EmailAddress VARCHAR(100)
)
BEGIN
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        SELECT 'Error occurred' AS Message;
    END;

    START TRANSACTION;
    
    INSERT INTO Emp_EH (EmpID, EmpName, EmailAddress)
    VALUES (input_EmpID, input_EmpName, input_EmailAddress);
    
    COMMIT;
    
    SELECT 'Data inserted successfully' AS Message;
END //

DELIMITER ;

Step-1:
 three input parameters: input_EmpID, input_EmpName, and input_EmailAddress, corresponding to the columns in the Emp_EH table.
Step-2:
use the DECLARE EXIT HANDLER FOR SQLEXCEPTION statement to handle any SQL exceptions that may occur during the execution of the procedure
Step-3:
If an exception occurs, the ROLLBACK statement is executed to undo any changes made within the transaction, and the message "Error occurred" is returned.
Step-4:
The procedure starts a transaction using the START TRANSACTION statement
Step-5:
 It then performs an INSERT operation to insert the provided values into the Emp_EH table.
Step-6:
If the INSERT operation is successful, the changes are committed using the COMMIT statement, and the message "Data inserted successfully" is returned.
Step-7:
use the following code - To execute the procedure and insert values into the Emp_EH table

------CALL Insert_Emp_EH(1, 'John Doe', 'john.doe@example.com');

Replace the values 1, 'John Doe', and 'john.doe@example.com' with the desired values for EmpID, EmpName, and EmailAddress, respectively.


Alternate method:

 without using explicit transaction control statements (START TRANSACTION and COMMIT), you can use the AUTOCOMMIT mode to handle the transaction implicitly.

DELIMITER //

CREATE PROCEDURE Insert_Emp_EH(
    IN input_EmpID INT,
    IN input_EmpName VARCHAR(50),
    IN input_EmailAddress VARCHAR(100)
)
BEGIN
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        SELECT 'Error occurred' AS Message;
    END;
--- 
start procedure

    SET AUTOCOMMIT = 0;
    
    INSERT INTO Emp_EH (EmpID, EmpName, EmailAddress)
    VALUES (input_EmpID, input_EmpName, input_EmailAddress);
    
    SET AUTOCOMMIT = 1;
    
    SELECT 'Data inserted successfully' AS Message;
END //

DELIMITER ;


learning from AI:

To handle errors in DQL statements, you can use the DECLARE EXIT HANDLER FOR SQLEXCEPTION statement,
However, since DQL statements like SELECT do not raise exceptions, using a CONTINUE HANDLER for DQL exceptions is not applicable in this case.