IF-ELSE in procedures in SQL - Step-by-Step Execution

Concept Flow - IF-ELSE in procedures

Start Procedure

↓

Evaluate IF condition

↓

Execute IF block

↓

End Procedure

The procedure starts, checks the IF condition, executes the IF block if true, otherwise executes the ELSE block, then ends.

Execution Sample

SQL

CREATE PROCEDURE CheckAge(IN age INT)
BEGIN
  IF age >= 18 THEN
    SELECT 'Adult';
  ELSE
    SELECT 'Minor';
  END IF;
END;

This procedure checks if the input age is 18 or older and returns 'Adult' or 'Minor' accordingly.

Execution Table

Step	Input age	Condition (age >= 18)	Branch Taken	Output
1	20	True	IF block	'Adult'
2	17	False	ELSE block	'Minor'
3	18	True	IF block	'Adult'
4	0	False	ELSE block	'Minor'
5	Exit	-	-	Procedure ends

💡 Procedure ends after executing IF or ELSE block based on condition.

Variable Tracker

Variable	Start	After Step 1	After Step 2	After Step 3	After Step 4	Final
age	undefined	20	17	18	0	-
condition (age >= 18)	undefined	True	False	True	False	-
output	undefined	'Adult'	'Minor'	'Adult'	'Minor'	-

Key Moments - 2 Insights

Why does the ELSE block run when age is 17?

Because the condition age >= 18 is False at step 2 in the execution_table, so the ELSE block executes.

What happens if age is exactly 18?

At step 3, the condition age >= 18 is True, so the IF block runs and outputs 'Adult'.

Visual Quiz - 3 Questions

Look at the execution_table, what output is produced when age is 20?

A No output

B 'Minor'

C 'Adult'

D Error

💡 Hint

Check row 1 in execution_table where age=20 and output is shown.

At which step does the condition become False?

A Step 2

B Step 1

C Step 3

D Step 5

💡 Hint

Look at the 'Condition' column in execution_table for False values.

If age is changed to 15, which branch will execute?

A IF block

B ELSE block

C Both IF and ELSE blocks

D Neither block

💡 Hint

Refer to variable_tracker and execution_table where condition is False for ages less than 18.

Concept Snapshot

IF-ELSE in SQL procedures:
- Use IF condition THEN ... ELSE ... END IF;
- Condition is evaluated once per call
- IF block runs if condition True
- ELSE block runs if condition False
- Helps control flow inside procedures

Full Transcript

This visual execution trace shows how IF-ELSE works inside SQL procedures. The procedure starts and checks the condition age >= 18. If true, it runs the IF block and outputs 'Adult'. If false, it runs the ELSE block and outputs 'Minor'. The execution table shows different input ages and which branch runs. The variable tracker follows the age input, condition result, and output at each step. Key moments clarify why ELSE runs for age 17 and IF runs for age 18. The quiz tests understanding of outputs and condition results. This helps beginners see step-by-step how IF-ELSE controls procedure flow.

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