CREATE PROCEDURE syntax in SQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

Output of a simple stored procedure call

Consider the following stored procedure in SQL that returns the sum of two numbers. What will be the output when calling CALL AddNumbers(3, 5);?

SQL

CREATE PROCEDURE AddNumbers(IN num1 INT, IN num2 INT)
BEGIN
  SELECT num1 + num2 AS SumResult;
END;

A SumResult = 15

B No output, procedure does not return values

C Syntax error due to missing RETURN statement

D SumResult = 8

💡 Hint

The procedure uses SELECT to output the sum of the two input numbers.

The procedure takes two input integers and uses SELECT to output their sum as SumResult. Calling with 3 and 5 returns 8.

📝 Syntax

intermediate

Identify the syntax error in procedure creation

Which option contains a syntax error when creating a stored procedure in SQL?

A CREATE PROCEDURE TestProc BEGIN SELECT 1; END;

B ;DNE ;1 TCELES NIGEB )(corPtseT ERUDECORP ETAERC

C REATE PROCEDURE TestProc() BEGIN SELECT 1; END;

D CREATE PROCEDURE TestProc() BEGIN SELECT 1; END;

💡 Hint

Check if parentheses are required after procedure name.

In SQL, parentheses are required after the procedure name even if there are no parameters. Option A misses the parentheses, causing syntax error.

🧠 Conceptual

advanced

Understanding parameter modes in CREATE PROCEDURE

Which parameter mode allows a stored procedure to return a value back to the caller?

A INOUT

B RETURN

C OUT

D IN

💡 Hint

Think about how data flows from procedure to caller.

The OUT parameter mode allows the procedure to send a value back to the caller. IN is input only, INOUT is input and output, RETURN is not a parameter mode.

🔧 Debug

advanced

Why does this procedure fail to compile?

Given the procedure below, why does it fail to compile? CREATE PROCEDURE MultiplyNumbers(IN a INT, IN b INT) BEGIN DECLARE result INT; SET result = a * b SELECT result; END;

SQL

CREATE PROCEDURE MultiplyNumbers(IN a INT, IN b INT)
BEGIN
  DECLARE result INT;
  SET result = a * b;
  SELECT result;
END;

A Missing semicolon after SET statement

B DECLARE cannot be used inside procedures

C SELECT cannot be used inside procedures

D Procedure must have RETURNS clause

💡 Hint

Check statement terminators inside the procedure body.

Each statement inside the procedure must end with a semicolon. The SET statement is missing a semicolon, causing a syntax error.

❓ optimization

expert

Optimizing procedure for multiple calls

You have a procedure that calculates the factorial of a number using a loop. Which change will improve performance when calling it many times?

SQL

CREATE PROCEDURE Factorial(IN n INT, OUT fact BIGINT)
BEGIN
  SET fact = 1;
  DECLARE i INT DEFAULT 1;
  WHILE i <= n DO
    SET fact = fact * i;
    SET i = i + 1;
  END WHILE;
END;

A Replace WHILE loop with a recursive procedure call

B Cache previously computed factorials in a table and reuse them

C Use a temporary table to store intermediate factorial values

D Increase the size of the OUT parameter to BIGINT

💡 Hint

Think about avoiding repeated calculations for the same inputs.

Caching previously computed factorials avoids recalculating them, improving performance for multiple calls with overlapping inputs.