Creating stored procedures in MySQL - Practice Exercises

Challenge - 5 Problems

❓ query_result

intermediate

Output of a simple stored procedure call

Consider the following stored procedure in MySQL:

DELIMITER $$
CREATE PROCEDURE GetEmployeeCount()
BEGIN
  SELECT COUNT(*) AS TotalEmployees FROM employees;
END$$
DELIMITER ;

What will be the output when you execute CALL GetEmployeeCount(); assuming the employees table has 50 rows?

MySQL

CALL GetEmployeeCount();

A A single row with column TotalEmployees and value 50

B No output, only a success message

C An error because stored procedures cannot return SELECT results

D A list of all employee names

💡 Hint

Stored procedures can return result sets using SELECT statements.

The procedure runs a SELECT COUNT(*) query and returns the count as a result set. Since there are 50 rows, the output is one row with TotalEmployees = 50.

📝 Syntax

intermediate

Identify the syntax error in stored procedure creation

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

MySQL

CREATE PROCEDURE SampleProc()
BEGIN
  SELECT * FROM users;
END;

A CREATE PROCEDURE SampleProc() BEGIN SELECT * FROM users END;

B CREATE PROCEDURE SampleProc() BEGIN SELECT * FROM users; END;

C CREATE PROCEDURE SampleProc() BEGIN SELECT * FROM users; END

D CREATE PROCEDURE SampleProc() BEGIN SELECT * FROM users; END;;

💡 Hint

Check for missing semicolons inside the procedure body.

Option A is missing a semicolon after the SELECT statement, causing a syntax error.

❓ optimization

advanced

Optimizing a stored procedure with parameters

You have a stored procedure that takes a department ID and returns employees in that department:

CREATE PROCEDURE GetEmployeesByDept(IN dept_id INT)
BEGIN
  SELECT * FROM employees WHERE department_id = dept_id;
END;

Which option improves this procedure to avoid confusion between parameter and column names?

A Use <code>WHERE department_id = dept_id</code> as is

B Use <code>WHERE department_id = employees.dept_id</code>

C Use <code>WHERE department_id = GetEmployeesByDept.dept_id</code>

D Use <code>WHERE department_id = IN_dept_id</code> and rename parameter to IN_dept_id

💡 Hint

Parameter names can conflict with column names; renaming helps clarity.

Renaming the parameter to a unique name like IN_dept_id avoids ambiguity and ensures the correct value is used in the WHERE clause.

🔧 Debug

advanced

Debugging a stored procedure with variable assignment

What error will occur when running this stored procedure?

CREATE PROCEDURE CalcTotal()
BEGIN
  DECLARE total INT;
  SET total = SELECT COUNT(*) FROM orders;
  SELECT total;
END;

A No error, returns total count of orders

B Syntax error near 'SELECT COUNT(*) FROM orders;'

C Runtime error: variable 'total' not declared

D Error: SELECT statement cannot be used in SET

💡 Hint

Check the syntax for assigning query results to variables.

You cannot assign a SELECT statement directly in SET. You must use SELECT ... INTO to assign query results to variables.

🧠 Conceptual

expert

Understanding transaction control in stored procedures

Which statement about transaction control inside MySQL stored procedures is correct?

A You cannot use transaction control statements inside stored procedures at all.

B Stored procedures automatically commit after each statement; explicit COMMIT is not allowed.

C You can use COMMIT and ROLLBACK inside stored procedures to control transactions.

D Transactions inside stored procedures are controlled only by the client application, not the procedure.

💡 Hint

Think about how transactions work in MySQL and stored procedures.

MySQL allows COMMIT and ROLLBACK inside stored procedures to manage transactions explicitly.

Practice

1. What is the main purpose of a stored procedure in MySQL?

easy

A. To create a new database

B. To save a set of SQL commands for reuse later

C. To delete tables automatically

D. To backup the entire database

Solution

1. Step 1: Understand stored procedure concept

   A stored procedure is a saved set of SQL commands that can be run multiple times without rewriting.

2. Step 2: Compare options with definition

   Only To save a set of SQL commands for reuse later correctly describes this purpose; others describe unrelated tasks.

3. Final Answer:

   To save a set of SQL commands for reuse later -> Option B

4. Quick Check:

   Stored procedure = saved SQL commands [OK]

Hint: Stored procedures store SQL commands to run later [OK]

Common Mistakes:

• Confusing stored procedures with database creation
• Thinking stored procedures delete tables
• Assuming stored procedures backup data

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

easy

A. CREATE PROCEDURE GetUsers() { SELECT * FROM users; }

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

C. CREATE PROCEDURE GetUsers() SELECT * FROM users;

D. CREATE PROCEDURE GetUsers() BEGIN SELECT * FROM users; END;

Solution

1. Step 1: Recall correct stored procedure syntax

   MySQL requires parentheses for parameters (empty if none), BEGIN and END to mark procedure body.

2. Step 2: Check each option

   CREATE PROCEDURE GetUsers() BEGIN SELECT * FROM users; END; uses parentheses and BEGIN...END correctly. Others miss parentheses, BEGIN/END, or use wrong braces.

3. Final Answer:

   CREATE PROCEDURE GetUsers() BEGIN SELECT * FROM users; END; -> Option D

4. Quick Check:

   Syntax needs () and BEGIN...END [OK]

Hint: Use () even if no parameters, and BEGIN...END block [OK]

Common Mistakes:

• Omitting parentheses after procedure name
• Missing BEGIN and END keywords
• Using curly braces instead of BEGIN...END

3. Given the stored procedure:

CREATE PROCEDURE AddNumbers(IN a INT, IN b INT, OUT sum INT)
BEGIN
  SET sum = a + b;
END;

What will be the value of sum after calling CALL AddNumbers(3, 5, @result); SELECT @result;?

medium

A. 8

B. 35

C. NULL

D. Syntax error

Solution

1. Step 1: Understand procedure parameters and call

   The procedure takes two input integers a and b, and outputs their sum into the OUT parameter sum.

2. Step 2: Analyze the call and output

   Calling with 3 and 5 sets sum = 3 + 5 = 8. The OUT parameter is stored in @result, so SELECT @result returns 8.

3. Final Answer:

   8 -> Option A

4. Quick Check:

   3 + 5 = 8 [OK]

Hint: OUT parameters store results in user variables like @result [OK]

Common Mistakes:

• Confusing concatenation with addition
• Expecting sum to be NULL before assignment
• Forgetting to use user variable for OUT parameter

4. Identify the error in this stored procedure:

CREATE PROCEDURE GetCount()
BEGIN
  SELECT COUNT(*) FROM users
END;

medium

A. Missing semicolon after SELECT statement

B. Procedure name cannot have parentheses

C. BEGIN and END keywords are not allowed

D. COUNT(*) is invalid syntax

Solution

1. Step 1: Check syntax inside procedure body

   Each statement inside BEGIN...END must end with a semicolon in MySQL.

2. Step 2: Identify missing semicolon

   The SELECT COUNT(*) FROM users statement lacks a semicolon before END; causing syntax error.

3. Final Answer:

   Missing semicolon after SELECT statement -> Option A

4. Quick Check:

   Statements inside BEGIN...END need semicolons [OK]

Hint: End each statement with semicolon inside BEGIN...END [OK]

Common Mistakes:

• Omitting semicolon after SQL statements
• Thinking parentheses are not allowed in procedure name
• Believing COUNT(*) is invalid

5. You want to create a stored procedure UpdateSalary that increases an employee's salary by a given percentage. Which of the following is the correct way to write it?

hard

A. CREATE PROCEDURE UpdateSalary(IN empId INT, IN pct INT) UPDATE employees SET salary = salary * pct WHERE id = empId;

B. CREATE PROCEDURE UpdateSalary(empId INT, pct DECIMAL(5,2)) BEGIN UPDATE employees SET salary = salary + pct WHERE id = empId; END;

C. CREATE PROCEDURE UpdateSalary(IN empId INT, IN pct DECIMAL(5,2)) BEGIN UPDATE employees SET salary = salary * (1 + pct/100) WHERE id = empId; END;

D. CREATE PROCEDURE UpdateSalary(IN empId INT, IN pct DECIMAL(5,2)) BEGIN UPDATE employees SET salary = salary + pct WHERE id = empId END

Solution

1. Step 1: Check parameter declarations and body structure

   CREATE PROCEDURE UpdateSalary(IN empId INT, IN pct DECIMAL(5,2)) BEGIN UPDATE employees SET salary = salary * (1 + pct/100) WHERE id = empId; END; correctly declares input parameters with IN and uses BEGIN...END block.

2. Step 2: Verify salary update logic and syntax

   CREATE PROCEDURE UpdateSalary(IN empId INT, IN pct DECIMAL(5,2)) BEGIN UPDATE employees SET salary = salary * (1 + pct/100) WHERE id = empId; END; updates salary by multiplying with (1 + pct/100), which correctly increases salary by percentage. It also ends statements with semicolon.

3. Step 3: Identify errors in other options

   CREATE PROCEDURE UpdateSalary(empId INT, pct DECIMAL(5,2)) BEGIN UPDATE employees SET salary = salary + pct WHERE id = empId; END; lacks IN keywords and adds pct directly (not percentage). CREATE PROCEDURE UpdateSalary(IN empId INT, IN pct INT) UPDATE employees SET salary = salary * pct WHERE id = empId; misses BEGIN...END and uses wrong pct type. CREATE PROCEDURE UpdateSalary(IN empId INT, IN pct DECIMAL(5,2)) BEGIN UPDATE employees SET salary = salary + pct WHERE id = empId END misses semicolon before END.

4. Final Answer:

   CREATE PROCEDURE UpdateSalary(IN empId INT, IN pct DECIMAL(5,2)) BEGIN UPDATE employees SET salary = salary * (1 + pct/100) WHERE id = empId; END; -> Option C

5. Quick Check:

   Use IN params, BEGIN...END, and correct percentage formula [OK]

Hint: Use IN params and multiply salary by (1 + pct/100) [OK]

Common Mistakes:

• Forgetting IN keyword for parameters
• Adding percentage directly instead of multiplying
• Missing BEGIN...END or semicolons