Examples and Notes by Week

↑ Up to Parent directory
View AddOrderItem.sql code.	Open/Save AddOrderItem.sql.
View CustomerLevel_Fuction.sql code.	Open/Save CustomerLevel_Fuction.sql.
View Divide.sql code.	Open/Save Divide.sql.
View GetCustomerLevel.sql code.	Open/Save GetCustomerLevel.sql.
View InsertSupplierProduct.sql code.	Open/Save InsertSupplierProduct.sql.

The code for InsertSupplierProduct.sql is:
DROP PROCEDURE IF EXISTS InsertSupplierProduct; DELIMITER $$ CREATE PROCEDURE InsertSupplierProduct( IN inSupplierId INT, IN inProductId INT ) BEGIN DECLARE message VARCHAR(255) DEFAULT ''; DECLARE SupplierIdCount INT; DECLARE DuplicateKey CONDITION for 1062; DECLARE CONTINUE HANDLER FOR DuplicateKey SELECT CONCAT('Duplicate key (',inSupplierId,',',inProductId,') occurred') INTO message; # Most specific and will alway handle the error. DECLARE CONTINUE HANDLER FOR SQLSTATE '23000' SELECT 'SQLSTATE 23000' INTO message; # Next most specific and will handel the error if there is not one for the error code. DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SELECT 'SQLException encountered' INTO message; # Least specific. Only handles exceptions that have no SLQSTATE or error code handler. -- insert a new row into the SupplierProducts INSERT INTO SupplierProducts(supplierId,productId) VALUES(inSupplierId,inProductId); -- return the products supplied by the supplier id SELECT COUNT(*) INTO SupplierIdCount FROM SupplierProducts WHERE supplierId = inSupplierId; IF message = '' THEN SELECT SupplierIdCount AS 'SupplierId Count'; ELSE SELECT message, SupplierIdCount AS 'SupplierId Count'; END IF; END$$ DELIMITER ;