IF-ELSE in procedures in SQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

Output of IF-ELSE in a stored procedure

Consider the following stored procedure in SQL. What will be the output when the procedure is called with input value 15?

SQL

CREATE PROCEDURE CheckValue(IN input_val INT)
BEGIN
  IF input_val < 10 THEN
    SELECT 'Less than 10' AS result;
  ELSEIF input_val BETWEEN 10 AND 20 THEN
    SELECT 'Between 10 and 20' AS result;
  ELSE
    SELECT 'Greater than 20' AS result;
  END IF;
END;

A Between 10 and 20

B NULL

C Less than 10

D Greater than 20

💡 Hint

Check the condition ranges in the IF-ELSE statement carefully.

The input value 15 falls between 10 and 20, so the ELSEIF branch executes, returning 'Between 10 and 20'.

📝 Syntax

intermediate

Identify the syntax error in IF-ELSE block

Which option contains a syntax error in the IF-ELSE block of a SQL procedure?

SQL

CREATE PROCEDURE SampleProc(IN val INT)
BEGIN
  IF val > 0 THEN
    SELECT 'Positive';
  ELSE
    SELECT 'Non-positive';
  END IF;
END;

A Missing END keyword for procedure

B Missing THEN keyword after IF condition

C Missing semicolon after END IF

D Missing BEGIN keyword after ELSE

💡 Hint

Check punctuation after control flow statements.

In SQL procedures, the END IF statement must be followed by a semicolon to mark the end of the IF block.

❓ optimization

advanced

Optimizing nested IF-ELSE in procedures

Given a procedure with nested IF-ELSE statements checking multiple ranges, which option optimizes the logic to reduce unnecessary checks?

SQL

CREATE PROCEDURE RangeCheck(IN num INT)
BEGIN
  IF num < 0 THEN
    SELECT 'Negative';
  ELSE
    IF num = 0 THEN
      SELECT 'Zero';
    ELSE
      IF num > 0 AND num < 10 THEN
        SELECT 'Small positive';
      ELSE
        SELECT 'Large positive';
      END IF;
    END IF;
  END IF;
END;

A Use ELSEIF to combine conditions instead of nested IFs

B Replace IF with CASE statement for clarity

C Remove ELSE blocks to simplify code

D Use multiple separate procedures for each condition

💡 Hint

Think about how ELSEIF can reduce nesting.

Using ELSEIF allows checking multiple conditions sequentially without deep nesting, improving readability and performance.

🔧 Debug

advanced

Debugging unexpected output in IF-ELSE procedure

A procedure is expected to return 'Adult' for age >= 18 and 'Minor' otherwise. However, it always returns 'Minor'. Which option explains the bug?

SQL

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

A The ELSE block is missing a SELECT statement

B The condition should be age >= 18, not age > 18

C The procedure lacks a RETURN statement

D The input parameter is not declared correctly

💡 Hint

Check the boundary condition in the IF statement.

Age 18 does not satisfy age > 18, so it falls to ELSE returning 'Minor'. Changing to age >= 18 fixes this.

🧠 Conceptual

expert

Understanding IF-ELSE execution flow in procedures

In a SQL procedure, what happens if multiple IF conditions are true but only IF and ELSEIF branches are used without ELSE? Which option best describes the behavior?

A The ELSE block executes regardless of IF conditions

B All true conditions' blocks execute sequentially

C The procedure throws a runtime error due to multiple true conditions

D Only the first true condition's block executes, others are skipped

💡 Hint

Think about how IF-ELSEIF chains work in SQL procedures.

In IF-ELSEIF chains, once a true condition is found, its block runs and the rest are skipped.