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.

Practice

1. What is the main purpose of using IF-ELSE statements inside SQL procedures?

easy

A. To delete all data from a table

B. To execute different code blocks based on conditions

C. To create new tables automatically

D. To change the database schema

Solution

1. Step 1: Understand the role of IF-ELSE

   IF-ELSE statements allow conditional execution of code depending on whether a condition is true or false.

2. Step 2: Identify the correct purpose

   Creating tables or deleting data are not related to IF-ELSE logic but to other SQL commands.

3. Final Answer:

   To execute different code blocks based on conditions -> Option B

4. Quick Check:

   IF-ELSE controls flow [OK]

Hint: IF-ELSE runs code only when conditions match [OK]

Common Mistakes:

• Thinking IF-ELSE creates or deletes tables
• Confusing IF-ELSE with data manipulation commands
• Assuming IF-ELSE changes database structure

2. Which of the following is the correct syntax to start an IF statement inside a SQL procedure?

easy

A. IF condition THEN

B. IF (condition) {

C. IF condition DO

D. IF condition BEGIN

Solution

1. Step 1: Recall SQL procedure IF syntax

   In SQL procedures, the IF statement starts with IF condition THEN.

2. Step 2: Compare options

   Options B, C, and D use syntax styles from other languages or incorrect keywords.

3. Final Answer:

   IF condition THEN -> Option A

4. Quick Check:

   SQL IF starts with IF condition THEN [OK]

Hint: Use THEN after condition in SQL IF [OK]

Common Mistakes:

• Using curly braces {} like in programming languages
• Using DO or BEGIN instead of THEN
• Missing THEN keyword after condition

3. Consider this SQL procedure snippet:

DECLARE v_score INT DEFAULT 75;
IF v_score >= 90 THEN
  SELECT 'Grade A';
ELSEIF v_score >= 80 THEN
  SELECT 'Grade B';
ELSEIF v_score >= 70 THEN
  SELECT 'Grade C';
ELSE
  SELECT 'Grade F';
END IF;

What will be the output?

medium

A. 'Grade A'

B. 'Grade B'

C. 'Grade C'

D. 'Grade F'

Solution

1. Step 1: Check the value of v_score

   v_score is 75.

2. Step 2: Evaluate conditions in order

   75 is not >= 90, not >= 80, but it is >= 70, so 'Grade C' is selected.

3. Final Answer:

   'Grade C' -> Option C

4. Quick Check:

   75 >= 70 = 'Grade C' [OK]

Hint: Check conditions top to bottom, first true wins [OK]

Common Mistakes:

• Choosing 'Grade B' or 'Grade A' without checking all conditions
• Ignoring ELSEIF order
• Assuming ELSE runs when a previous condition matches

4. Identify the error in this SQL procedure snippet:

IF score > 50
  SELECT 'Pass';
ELSE
  SELECT 'Fail';
END IF;

medium

A. Missing semicolon after ELSE

B. Missing BEGIN keyword

C. Incorrect END IF syntax

D. Missing THEN after IF condition

Solution

1. Step 1: Check IF statement syntax

   In SQL procedures, IF must be followed by THEN after the condition.

2. Step 2: Verify given code

   The code has IF score > 50 but no THEN keyword.

3. Final Answer:

   Missing THEN after IF condition -> Option D

4. Quick Check:

   IF condition THEN is required [OK]

Hint: Always put THEN after IF condition [OK]

Common Mistakes:

• Omitting THEN after IF
• Adding semicolon after ELSE incorrectly
• Misusing END IF syntax

5. You want to write a procedure that checks a user's age and returns 'Adult' if age is 18 or more, 'Minor' if less, and 'Invalid' if age is negative. Which IF-ELSE structure correctly implements this?

hard

A. IF age < 0 THEN SELECT 'Invalid'; ELSEIF age >= 18 THEN SELECT 'Adult'; ELSE SELECT 'Minor'; END IF;

B. IF age >= 18 THEN SELECT 'Adult'; ELSEIF age < 0 THEN SELECT 'Invalid'; ELSE SELECT 'Minor'; END IF;

C. IF age < 0 THEN SELECT 'Invalid'; ELSE SELECT 'Minor'; ELSEIF age >= 18 THEN SELECT 'Adult'; END IF;

D. IF age >= 18 THEN SELECT 'Adult'; ELSE SELECT 'Minor'; END IF; IF age < 0 THEN SELECT 'Invalid'; END IF;

Solution

1. Step 1: Check condition order for correctness

   Negative age must be checked first to return 'Invalid'. Then check if age is 18 or more for 'Adult'. Otherwise, 'Minor'.

2. Step 2: Analyze options

   IF age < 0 THEN SELECT 'Invalid'; ELSEIF age >= 18 THEN SELECT 'Adult'; ELSE SELECT 'Minor'; END IF; checks negative first, then adult, then minor. IF age >= 18 THEN SELECT 'Adult'; ELSEIF age < 0 THEN SELECT 'Invalid'; ELSE SELECT 'Minor'; END IF; checks adult before negative, which is wrong. IF age < 0 THEN SELECT 'Invalid'; ELSE SELECT 'Minor'; ELSEIF age >= 18 THEN SELECT 'Adult'; END IF; has ELSE before ELSEIF, invalid syntax. IF age >= 18 THEN SELECT 'Adult'; ELSE SELECT 'Minor'; END IF; IF age < 0 THEN SELECT 'Invalid'; END IF; splits checks into two IFs, which can cause multiple outputs.

3. Final Answer:

   IF age < 0 THEN SELECT 'Invalid'; ELSEIF age >= 18 THEN SELECT 'Adult'; ELSE SELECT 'Minor'; END IF; -> Option A

4. Quick Check:

   Check invalid first, then adult, else minor [OK]

Hint: Check invalid cases before valid ranges [OK]

Common Mistakes:

• Checking valid age before invalid
• Using ELSE before ELSEIF
• Splitting conditions into separate IFs causing multiple outputs