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.

Practice

1. What is the main purpose of the CREATE PROCEDURE statement in SQL?

easy

A. To display data from a table

B. To delete data from a table

C. To create a new database

D. To save a set of SQL commands that can be run later

Solution

1. Step 1: Understand the purpose of CREATE PROCEDURE

   The CREATE PROCEDURE statement is used to save a group of SQL commands as a procedure that can be executed later.

2. Step 2: Compare options with the purpose

   Options B, C, and D describe other SQL commands, not the purpose of CREATE PROCEDURE.

3. Final Answer:

   To save a set of SQL commands that can be run later -> Option D

4. Quick Check:

   CREATE PROCEDURE saves commands = A [OK]

Hint: Remember: procedures store commands to run later [OK]

Common Mistakes:

• Confusing CREATE PROCEDURE with CREATE TABLE
• Thinking it runs commands immediately
• Mixing it up with SELECT statement

2. Which of the following is the correct basic syntax to create a procedure named GetUsers without parameters?

easy

A. CREATE PROCEDURE GetUsers AS SELECT * FROM Users;

B. CREATE PROCEDURE GetUsers BEGIN SELECT * FROM Users; END;

C. CREATE PROCEDURE GetUsers() BEGIN SELECT * FROM Users; END;

D. CREATE PROCEDURE GetUsers SELECT * FROM Users;

Solution

1. Step 1: Recall standard procedure syntax

   The correct syntax includes parentheses for parameters (empty if none), and a BEGIN...END block to enclose statements.

2. Step 2: Check each option

   CREATE PROCEDURE GetUsers() BEGIN SELECT * FROM Users; END; correctly uses CREATE PROCEDURE GetUsers() BEGIN SELECT * FROM Users; END;. Options A, C, and D miss parentheses or BEGIN...END or have syntax errors.

3. Final Answer:

   CREATE PROCEDURE GetUsers() BEGIN SELECT * FROM Users; END; -> Option C

4. Quick Check:

   Procedure syntax needs () and BEGIN...END = B [OK]

Hint: Always use () and BEGIN...END in procedure syntax [OK]

Common Mistakes:

• Omitting parentheses after procedure name
• Not using BEGIN and END to wrap statements
• Missing semicolon inside procedure

3. Given the procedure below, what will be the output when calling CALL GetUserCount();?

CREATE PROCEDURE GetUserCount()
BEGIN
  SELECT COUNT(*) AS TotalUsers FROM Users;
END;

medium

A. No output because procedure does not return anything

B. The total number of rows in the Users table

C. Syntax error because of missing parameters

D. An error because SELECT cannot be used in procedures

Solution

1. Step 1: Understand what the procedure does

   The procedure runs a SELECT COUNT(*) query on the Users table and returns the count as TotalUsers.

2. Step 2: Analyze the CALL statement output

   Calling CALL GetUserCount(); executes the procedure and returns the count of rows in Users table as a result set.

3. Final Answer:

   The total number of rows in the Users table -> Option B

4. Quick Check:

   CALL runs procedure and returns SELECT result = C [OK]

Hint: CALL runs procedure and returns SELECT results [OK]

Common Mistakes:

• Thinking procedures cannot return SELECT results
• Assuming parameters are always required
• Confusing procedure call with function call

4. Identify the error in the following procedure definition:

CREATE PROCEDURE AddUser(
  IN username VARCHAR(50),
  IN email VARCHAR(100)
)
INSERT INTO Users (Username, Email) VALUES (username, email);
END;

medium

A. Missing BEGIN before the INSERT statement

B. Parameters should not have IN keyword

C. END keyword is not needed

D. Procedure name cannot have parameters

Solution

1. Step 1: Check procedure block structure

   Procedures must enclose statements inside BEGIN and END blocks. This code misses BEGIN before INSERT.

2. Step 2: Verify other syntax parts

   IN keyword for parameters is correct, END is needed, and procedure names can have parameters.

3. Final Answer:

   Missing BEGIN before the INSERT statement -> Option A

4. Quick Check:

   Procedures need BEGIN...END blocks = A [OK]

Hint: Always wrap procedure statements with BEGIN and END [OK]

Common Mistakes:

• Forgetting BEGIN before statements
• Removing IN keyword from parameters
• Omitting END keyword

5. You want to create a procedure UpdateUserEmail that takes a user ID and a new email, then updates the user's email in the Users table. Which of the following is the correct complete procedure?

hard

A. CREATE PROCEDURE UpdateUserEmail(IN userId INT, IN newEmail VARCHAR(100)) BEGIN UPDATE Users SET Email = newEmail WHERE Id = userId; END;

B. CREATE PROCEDURE UpdateUserEmail(userId INT, newEmail VARCHAR(100)) UPDATE Users SET Email = newEmail WHERE Id = userId;

C. CREATE PROCEDURE UpdateUserEmail(IN userId INT, IN newEmail VARCHAR(100)) UPDATE Users SET Email = newEmail WHERE Id = userId; END;

D. CREATE PROCEDURE UpdateUserEmail(IN userId INT, IN newEmail VARCHAR(100)) BEGIN UPDATE Users SET Email = 'newEmail' WHERE Id = userId; END;

Solution

1. Step 1: Check parameter declaration and statement block

   Parameters must have IN keyword and the statements must be inside BEGIN...END.

2. Step 2: Verify the UPDATE statement correctness

   CREATE PROCEDURE UpdateUserEmail(IN userId INT, IN newEmail VARCHAR(100)) BEGIN UPDATE Users SET Email = newEmail WHERE Id = userId; END; correctly uses parameters without quotes around newEmail, so it updates with the variable value. CREATE PROCEDURE UpdateUserEmail(IN userId INT, IN newEmail VARCHAR(100)) BEGIN UPDATE Users SET Email = 'newEmail' WHERE Id = userId; END; incorrectly uses quotes, making it a string literal.

3. Final Answer:

   CREATE PROCEDURE UpdateUserEmail(IN userId INT, IN newEmail VARCHAR(100)) BEGIN UPDATE Users SET Email = newEmail WHERE Id = userId; END; -> Option A

4. Quick Check:

   Parameters with IN and BEGIN...END, no quotes on variables = D [OK]

Hint: Use IN parameters and BEGIN...END; no quotes on variables [OK]

Common Mistakes:

• Omitting BEGIN...END block
• Forgetting IN keyword for parameters
• Using quotes around parameter variables in SQL