The ELSE clause provides the fallback action when an IF condition is false or NULL. Think of it as otherwise: do this when the preferred action cannot happen. ELSE must follow an IF; writing it alone causes a syntax error. section 21.2.2
Skip an incomplete promotion insert
A stored procedure can insert a promotion only when _PromotionCode has a value; otherwise, ELSE returns a useful explanation:\n\nIF _PromotionCode IS NOT NULL THEN\n INSERT INTO promotion (PromotionID, PromotionCode, PromotionStartDate, PromotionEndDate)\n SELECT _PromotionID, _PromotionCode, _PromotionStartDate, _PromotionEndDate;\nELSE\n SELECT 'No PromotionCode, INSERT skipped.' AS Message;\nEND IF;\n\nCalling AddPromotion with a NULL promotion code returns the message instead of inserting the row. section 21.2.2
Use ELSE in these practical ways:
- Complete a decision structure after checking several possible failures with ELSEIF; the final ELSE handles the successful insert. section 21.2.3
- Group several fallback statements inside BEGIN and END, such as an INSERT followed by an “INSERT successful.” message. section 21.2.3
- Supply a default result in a CASE expression for values that match no WHEN condition, including unknown or NULL values. section 21.1.3
DROP PROCEDURE AddPromotion;
DELIMITER //
CREATE PROCEDURE AddPromotion (
IN _PromotionID int,
IN _PromotionCode varchar(10),
IN _PromotionStartDate datetime,
IN _PromotionEndDate datetime
)
BEGIN
IF _PromotionCode IS NOT NULL THEN
INSERT INTO promotion (
PromotionID,
PromotionCode,
PromotionStartDate,
PromotionEndDate
)
SELECT
_PromotionID,
_PromotionCode,
_PromotionStartDate,
_PromotionEndDate
;
ELSE
SELECT 'No PromotionCode, INSERT skipped.' AS Message;
END IF;
END //
DELIMITER ;
Inspect a complete AddPromotion procedure that combines IF, ELSE, INSERT, and a fallback message. section 21.2.2
