How to Use IF ELSE in MySQL Procedure: Syntax and Example

In MySQL procedures, use IF ... THEN ... ELSE ... END IF; to run different code based on conditions. This lets you control the flow inside your procedure by checking conditions and executing matching blocks.

Syntax

The IF statement in MySQL procedures checks a condition and runs code accordingly. It has three parts:

• IF condition THEN: Runs code if the condition is true.
• ELSEIF condition THEN: (Optional) Checks another condition if the first is false.
• ELSE: (Optional) Runs code if none of the conditions are true.
• END IF;: Ends the IF block.

sql

IF condition THEN
  -- statements
ELSEIF another_condition THEN
  -- statements
ELSE
  -- statements
END IF;

Example

This example shows a procedure that checks a number and returns a message if it is positive, negative, or zero.

sql

DELIMITER $$
CREATE PROCEDURE CheckNumber(IN num INT)
BEGIN
  IF num > 0 THEN
    SELECT 'Number is positive' AS Result;
  ELSEIF num < 0 THEN
    SELECT 'Number is negative' AS Result;
  ELSE
    SELECT 'Number is zero' AS Result;
  END IF;
END$$
DELIMITER ;

-- Call the procedure
CALL CheckNumber(5);
CALL CheckNumber(-3);
CALL CheckNumber(0);

Output

Result
Number is positive

Result
Number is negative

Result
Number is zero

Common Pitfalls

Common mistakes when using IF ELSE in MySQL procedures include:

• Forgetting END IF; to close the IF block.
• Using ELSE IF instead of ELSEIF (MySQL requires ELSEIF as one word).
• Not setting the DELIMITER properly when creating procedures, causing syntax errors.
• Trying to use IF statements outside of BEGIN...END blocks in procedures.

sql

/* Wrong: missing END IF */
IF num > 0 THEN
  SELECT 'Positive';
-- Missing END IF here

/* Wrong: using ELSE IF instead of ELSEIF */
IF num > 0 THEN
  SELECT 'Positive';
ELSE IF num < 0 THEN
  SELECT 'Negative';
END IF;

/* Correct way */
IF num > 0 THEN
  SELECT 'Positive';
ELSEIF num < 0 THEN
  SELECT 'Negative';
ELSE
  SELECT 'Zero';
END IF;

Quick Reference

Use this quick guide when writing IF ELSE in MySQL procedures:

Keyword	Purpose	Notes
IF	Start condition check	Must be followed by a condition and THEN
THEN	Begin code block for true condition	Required after condition
ELSEIF	Check another condition if previous false	One word, no space
ELSE	Code block if all conditions false	Optional
END IF	End the IF statement block	Must end every IF block

Key Takeaways

Use IF ... THEN ... ELSEIF ... ELSE ... END IF; inside BEGIN...END blocks in MySQL procedures.

Always set DELIMITER properly when creating procedures to avoid syntax errors.

Use ELSEIF as one word, not ELSE IF, in MySQL.

Close every IF block with END IF; to prevent errors.

IF statements control flow by running code based on conditions inside procedures.