Handle Error When Inserting a Duplicate Customer Email in SQL
Handle Error When Inserting a Duplicate Customer Email in SQL
CustomerInfo Table:
Query Explanation:
● DELIMITER //: This changes the delimiter temporarily to //, allowing us to write multi-statement procedures without MySQL interpreting the semicolons inside the procedure.
● CREATE PROCEDURE InsertCustomerInfo: A new stored procedure named InsertCustomerInfo is being created. This procedure takes multiple parameters (Name, Email, City, Country, RegisteredDate).
● DECLARE email_exists INT: A variable email_exists is declared to store the result of the email existence check.
● SELECT COUNT(*) INTO email_exists: This query counts how many times the given email already exists in the CustomerInfo table. The result is stored in the email_exists variable.
● IF email_exists = 0 THEN: If no record exists with the given email (i.e., email_exists = 0), the following block will execute.
● INSERT INTO CustomerInfo: This inserts the provided customer information (Name, Email, City, Country, RegisteredDate) into the CustomerInfo table.
● ELSE SIGNAL SQLSTATE '45000': If an email already exists, the SIGNAL statement raises an error, preventing the insertion and displaying the message "Error: Duplicate email. The customer was not inserted."
● END IF: Ends the condition block.
● DELIMITER ;: Resets the delimiter back to ;, which is the default.
● CALL InsertCustomerInfo(...): This line calls the InsertCustomerInfo procedure with sample data to insert a new customer.
SQL Query:
DELIMITER //
CREATE PROCEDURE InsertCustomerInfo(
IN Name VARCHAR(100),
IN Email VARCHAR(100),
IN City VARCHAR(50),
IN Country VARCHAR(50),
IN RegisteredDate DATE
)
BEGIN
DECLARE email_exists INT;
-- Check if email already exists
SELECT COUNT(*) INTO email_exists
FROM CustomerInfo
WHERE CustomerInfo.Email = Email;
IF email_exists = 0 THEN
INSERT INTO CustomerInfo (CustomerName, Email, City, Country, RegisteredDate)
VALUES (Name, Email, City, Country, RegisteredDate);
ELSE
SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT = 'Error: Duplicate email. The customer was not inserted.';
END IF;
END //
DELIMITER ;
-- Call the procedure
CALL InsertCustomerInfo('Amit Sharma', 'amit.sharma@example.com', 'Pune', 'India', '2025-04-15');
Output:
