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.