HSHimanshu Sharma
Interview Questions

Stored procedures vs functions in MySQL: parameters, errors and when to use each

Himanshu Sharma · · 9 min read

Advertisement

A stored routine is a named block of SQL saved inside the database and run by name. MySQL has two kinds: stored procedures and stored functions. They look alike, and interviewers love asking how they differ. This post explains both with code that was run on MySQL 8.4: parameters, return values, how each is called, error handling and loops, the limits of functions, and an honest look at when keeping logic in the database is a good idea.

The sample table

CREATE TABLE employees (
  employee_id INT PRIMARY KEY, first_name VARCHAR(30),
  department VARCHAR(20), salary DECIMAL(10,2));
INSERT INTO employees VALUES
  (1,'John','HR',50000), (2,'Jane','IT',70000),
  (3,'Robert','HR',45000), (4,'Emily','Sales',52000);

Changing the delimiter

A routine body contains semicolons, but the mysql client also uses a semicolon to end a statement, so it would cut your definition short. The workaround is to temporarily change the client's delimiter. You will see this in every example:

DELIMITER //
CREATE PROCEDURE ... BEGIN ... END //
DELIMITER ;

DELIMITER is a client command, not SQL. Database tools and PHP's PDO send the whole CREATE PROCEDURE statement in one piece, so they don't need it.

Stored procedures

A procedure is a set of statements you run with CALL. It can read and change data, run several statements, use variables, loops and conditions, and manage transactions.

DELIMITER //
CREATE PROCEDURE raise_salary(IN dept VARCHAR(20), IN pct DECIMAL(5,2))
BEGIN
  UPDATE employees
  SET salary = ROUND(salary * (1 + pct / 100), 2)
  WHERE department = dept;
  SELECT ROW_COUNT() AS rows_updated;
END //
DELIMITER ;

CALL raise_salary('HR', 10);

It returned rows_updated = 2, and John's salary went from 50,000 to 55,000.00, Robert's from 45,000 to 49,500.00. A procedure may return result sets (the SELECT inside), as many as it likes.

Parameter modes

Mode Direction Meaning
IN (default) into the procedure caller passes a value; changes inside are not visible outside
OUT out of the procedure starts as NULL; the procedure sets it for the caller
INOUT both ways caller passes a value; the procedure may change it 
DELIMITER //
CREATE PROCEDURE dept_stats(IN dept VARCHAR(20), OUT head_count INT, OUT avg_pay DECIMAL(10,2))
BEGIN
  SELECT COUNT(*), AVG(salary) INTO head_count, avg_pay
  FROM employees WHERE department = dept;
END //

CREATE PROCEDURE bump(INOUT n INT)
BEGIN
  SET n = n + 1;
END //
DELIMITER ;

CALL dept_stats('HR', @n, @avg);
SELECT @n AS head_count, @avg AS avg_pay;
+------------+----------+
| head_count | avg_pay  |
+------------+----------+
|          2 | 52250.00 |
+------------+----------+

The @n and @avg are session variables that receive the OUT values. For the INOUT example, SET @c = 5; CALL bump(@c); CALL bump(@c); SELECT @c; printed 7.

Variables, conditions and loops

Inside the body you can declare local variables (DECLARE i INT DEFAULT 1;) and use IF ... THEN ... ELSEIF ... END IF, CASE, WHILE, REPEAT and LOOP. A small example builds a FizzBuzz table:

DELIMITER //
CREATE PROCEDURE fizz(IN n INT)
BEGIN
  DECLARE i INT DEFAULT 1;
  DROP TEMPORARY TABLE IF EXISTS out_rows;
  CREATE TEMPORARY TABLE out_rows (i INT, label VARCHAR(10));
  WHILE i <= n DO
    INSERT INTO out_rows VALUES
      (i, IF(i % 15 = 0, 'FizzBuzz', IF(i % 3 = 0, 'Fizz', IF(i % 5 = 0, 'Buzz', i))));
    SET i = i + 1;
  END WHILE;
  SELECT * FROM out_rows;
END //
DELIMITER ;

CALL fizz(6);

which gave 1, 2, Fizz, 4, Buzz, Fizz. Row-by-row loops are slow compared with set-based SQL, so loop only when you must.

Error handling

A handler says what to do when an error occurs. This one catches MySQL error 1062 (duplicate key) and reports it instead of failing:

DELIMITER //
CREATE PROCEDURE safe_insert(IN id INT, IN nm VARCHAR(30))
BEGIN
  DECLARE EXIT HANDLER FOR 1062
    SELECT CONCAT('id ', id, ' already exists') AS message;
  INSERT INTO employees VALUES (id, nm, 'IT', 40000);
  SELECT 'inserted' AS message;
END //
DELIMITER ;

CALL safe_insert(9, 'Mina');   -- message: inserted
CALL safe_insert(9, 'Mina');   -- message: id 9 already exists

For anything that changes several tables, combine a handler with a transaction so that an error rolls everything back; the transfer procedure in the transactions post shows the pattern with EXIT HANDLER FOR SQLEXCEPTION and ROLLBACK.

Stored functions

A function returns exactly one value through RETURNS and RETURN, and because it returns a value, you can use it inside a query, like UPPER() or ROUND().

DELIMITER //
CREATE FUNCTION annual_pay(monthly DECIMAL(10,2), months INT)
RETURNS DECIMAL(12,2) DETERMINISTIC
BEGIN
  RETURN monthly * months;
END //

CREATE FUNCTION grade(sal DECIMAL(10,2)) RETURNS VARCHAR(10) DETERMINISTIC
BEGIN
  RETURN CASE WHEN sal >= 60000 THEN 'high'
              WHEN sal >= 48000 THEN 'mid'
              ELSE 'low' END;
END //
DELIMITER ;

SELECT annual_pay(2500, 12);
SELECT first_name, salary, grade(salary) AS band FROM employees ORDER BY employee_id;
+----------------------+
| annual_pay(2500, 12) |
+----------------------+
|             30000.00 |
+----------------------+

+------------+----------+------+
| first_name | salary   | band |
+------------+----------+------+
| John       | 55000.00 | mid  |
| Jane       | 70000.00 | high |
| Robert     | 49500.00 | mid  |
| Emily      | 52000.00 | mid  |
+------------+----------+------+

(The salaries reflect the earlier raise_salary call.) Functions take only IN parameters. DETERMINISTIC promises that the same inputs always give the same output; say NOT DETERMINISTIC if the result depends on the time, random numbers or table data, and READS SQL DATA if the function queries tables. If binary logging is on and you leave these characteristics out, MySQL may refuse to create the function.

The two calling styles do not mix

A procedure is called with CALL; a function is called in an expression. Using the wrong one fails:

SELECT raise_salary('HR', 1);
-- ERROR 1305 (42000): FUNCTION sp.raise_salary does not exist
CALL annual_pay(1, 2);
-- ERROR 1305 (42000): PROCEDURE sp.annual_pay does not exist

Functions cannot do everything

Because a function runs in the middle of a query, MySQL restricts it. A function cannot return a result set, and it cannot start, commit or roll back a transaction:

CREATE FUNCTION bad_fn() RETURNS INT DETERMINISTIC
BEGIN
  START TRANSACTION;
  RETURN 1;
END;
-- ERROR 1422 (HY000): Explicit or implicit commit is not allowed in stored function or trigger.

Procedure versus function

Stored procedure Stored function
Called with CALL name(...) inside an expression: SELECT name(...)
Returns nothing required; OUT/INOUT parameters and any number of result sets exactly one value (RETURNS)
Parameters IN, OUT, INOUT IN only
Used in SELECT, WHERE, ORDER BY no yes
Transactions (COMMIT, ROLLBACK) allowed not allowed
Typical use business operations: transfer money, close an order, run a nightly job calculations and formatting: tax, age, grade, slug
Side effects expected should be avoided

Rule of thumb: if it does something (changes data, runs steps), write a procedure. If it computes a value you want to use inside a query, write a function.

Managing routines

SHOW PROCEDURE STATUS WHERE Db = 'shop';
SHOW CREATE PROCEDURE bump\G
DROP PROCEDURE IF EXISTS bump;
DROP FUNCTION IF EXISTS grade;

information_schema.routines lists every routine with its type and determinism (my test listed annual_pay and grade as FUNCTION with IS_DETERMINISTIC = YES, and the others as PROCEDURE). There is no ALTER that changes the body: drop and recreate it, and keep the definitions in version control (migration files), not only in the database. Permissions work separately: users need EXECUTE on a routine, and routines run with the creator's rights by default (SQL SECURITY DEFINER), which is useful for letting a limited user do one controlled operation.

Advantages and disadvantages

Advantages

  • Fewer round trips. One CALL replaces many statements sent over the network.
  • Reuse. Several applications (PHP, Python, reports) share one tested implementation.
  • Security. Users can be given EXECUTE on a procedure without access to the tables underneath, and parameters avoid SQL injection when you use them rather than building SQL text.
  • Consistency. Business rules live in one place, close to the data.
  • Performance. The server parses and plans statements inside a routine once per session instead of for every request.

Disadvantages

  • Harder to version, test and debug than application code. There is no good step debugger in plain MySQL.
  • Portability. Procedural SQL differs between MySQL, PostgreSQL (PL/pgSQL), SQL Server (T-SQL) and Oracle (PL/SQL), so moving databases means rewriting.
  • Scaling. Logic runs on the database server, which is usually the hardest tier to scale.
  • Hidden logic. Developers reading the application code may not know a procedure or trigger is changing data.
  • Slow when abused. Cursors and row-by-row loops can be much slower than one set-based statement.

Many teams use a middle path: keep complex, data-heavy operations that must be atomic (a transfer, a month-end close) in procedures, and keep everything else in application code, where tools for testing and deployment are better.

Calling a procedure from PHP

$stmt = $pdo->prepare('CALL raise_salary(?, ?)');
$stmt->execute(['HR', 10]);
$result = $stmt->fetchAll(PDO::FETCH_ASSOC);   // rows_updated
$stmt->closeCursor();                          // free the connection for the next statement

Call closeCursor() after a CALL, because procedures can return several result sets and MySQL otherwise complains of "unbuffered queries" on the next statement. In Laravel, use DB::select('CALL raise_salary(?, ?)', ['HR', 10]).

Common interview questions

  • What is a stored procedure? A named, precompiled-style block of SQL stored in the database and run with CALL.
  • What is the difference between a procedure and a function? See the table above: calling style, return value, parameters, transactions.
  • Can a function modify data? MySQL allows it with the right characteristics, but it is bad practice; use a procedure.
  • What are IN, OUT and INOUT? Parameter directions.
  • How do you handle errors in a procedure? DECLARE ... HANDLER, with ROLLBACK for transactions.
  • When would you avoid stored procedures? When you need easy testing and portability, or when logic is better in application code.

Common mistakes

  • Forgetting to change the DELIMITER in the client.
  • Using SELECT to call a procedure or CALL for a function.
  • Omitting DETERMINISTIC or READS SQL DATA and getting error 1418 when creating a function with binary logging on.
  • Writing row-by-row loops where one UPDATE would do.
  • Never saving routine definitions in version control.

Next steps

Routines often work together with transactions and views, and they are one of the senior-level themes in the top 20 SQL topics. Learn the language behind them in the free MySQL course and keep the MySQL cheat sheet open while you practise.

Advertisement