IF-ELSE in procedures in SQL

Introduction

IF-ELSE lets you make decisions inside a procedure. It helps the database choose what to do based on conditions.

You want to check if a value is above or below a limit and act differently.

You want to run some code only if a user exists in the database.

You want to update a record only if certain conditions are met.

You want to return different messages based on input values.

Syntax

SQL

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

The condition is a logical test that returns TRUE or FALSE.

ELSE part is optional; you can have only IF with THEN.

Examples

This checks if score is 60 or more. If yes, result is 'Pass', else 'Fail'.

SQL

IF score >= 60 THEN
   SET result = 'Pass';
ELSE
   SET result = 'Fail';
END IF;

This runs update only if user_exists is true.

SQL

IF user_exists THEN
   UPDATE users SET last_login = NOW() WHERE id = user_id;
END IF;

Sample Program

This procedure checks the age and sets a category. It uses IF, ELSEIF, and ELSE to decide.

SQL

DELIMITER $$
CREATE PROCEDURE CheckAge(IN age INT, OUT category VARCHAR(20))
BEGIN
   IF age < 13 THEN
      SET category = 'Child';
   ELSEIF age < 20 THEN
      SET category = 'Teenager';
   ELSE
      SET category = 'Adult';
   END IF;
END$$
DELIMITER ;

-- Call the procedure
CALL CheckAge(15, @cat);
SELECT @cat;

Important Notes

Remember to use DELIMITER when creating procedures in MySQL to avoid confusion with semicolons.

IF-ELSE helps make your procedures smart by choosing actions based on data.

Summary

IF-ELSE lets you run different code based on conditions inside procedures.

You can use IF, ELSEIF, and ELSE for multiple choices.

It makes your database actions flexible and dynamic.