Stored Procedure

A Stored Procedure in MySQL is a set of SQL statements that are stored in the database and can be executed later by calling its name.

It’s useful because:

• You can reuse logic without rewriting queries.
• It improves performance (less network traffic between app and DB).
• It improves security (you can give permission to execute a procedure without exposing the underlying tables directly).
• It helps modularize code (similar to functions in programming).

Creating a Stored Procedure

The basic syntax is:

DELIMITER //

CREATE PROCEDURE procedureName (parameters)
BEGIN
   -- SQL statements go here
END //

DELIMITER ;

• DELIMITER: By default, MySQL ends statements with ;. Since procedures have multiple statements separated by ;, we temporarily change the delimiter to // (or some other symbol).
• procedure_name: The name of the stored procedure.
• parameters: You can define parameters with IN, OUT, or INOUT.
  • IN → Input only (default).
    • Caller sends a value into the procedure.
    • Cannot be modified inside the procedure (only read).
  • OUT → Output only.
    • Caller provides a variable.
    • Procedure sets a value for it.
  • INOUT → Input and output.
    • Caller sends an initial value.
    • Procedure can read and change it, and the updated value is returned.
• BEGIN … END: Defines the body of the procedure.

A Simple Procedure (No Parameters)

Let’s create a procedure that shows all employees from a table.

DELIMITER //

CREATE PROCEDURE getAllEmployees()
BEGIN
   SELECT * FROM employees;
END //

DELIMITER ;

To call this procedure:

CALL getAllEmployees();

Procedure with IN Parameter

Suppose you want to get employees of a particular department:

DELIMITER //

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

DELIMITER ;

To call this procedure:

CALL getEmployeesByDept(2);

Procedure with OUT Parameter

Now let’s create a procedure that returns the total number of employees.

DELIMITER //

CREATE PROCEDURE getEmployeeCount(OUT total INT)
BEGIN
   SELECT COUNT(*) INTO total FROM employees;
END //

DELIMITER ;

To call this procedure:

CALL getEmployeeCount(@empCount);
SELECT @empCount AS 'Total Employees';

Procedure with INOUT Parameter

Let’s say we want to increase a number by 10 and return it.

DELIMITER //

CREATE PROCEDURE increaseNumber(INOUT num INT)
BEGIN
   SET num = num + 10;
END //

DELIMITER ;

To call this procedure:

SET @x = 5;
CALL increaseNumber(@x);
SELECT @x AS 'New Value';

Since @x starts as 5, after the procedure call, the result will be 15.

Managing Stored Procedures

• Show all procedures: SHOW PROCEDURE STATUS WHERE Db = 'your_database_name';
• View procedure code: SHOW CREATE PROCEDURE GetAllEmployees;
• Delete a procedure: DROP PROCEDURE GetAllEmployees;

Error Handling Stored Procedures

In MySQL, error handling inside stored procedures (and functions) is done using:

1. DECLARE HANDLER
   • Defines what to do when certain conditions occur (e.g., errors, warnings).
   • Two types:
     • CONTINUE HANDLER → Ignore the error and continue execution.
     • EXIT HANDLER → Exit the procedure immediately when the condition happens.
2. Condition Types you can handle:
   • SQLEXCEPTION → Catches all SQL errors.
   • SQLWARNING → Catches all warnings.
   • NOT FOUND → Usually used in cursors when no more rows are available.
   • Or specific error codes / SQLSTATE values (e.g., 1062 for duplicate entry).
3. GET DIAGNOSTICS (MySQL 5.6+)
   • Used to retrieve detailed error information inside the handler.

Syntax for Declaring a Handler

DECLARE handler_type HANDLER FOR condition_value statement;

• handler_type:

  • CONTINUE → continue execution
  • EXIT → exit procedure

• condition_value:

  • SQLEXCEPTION, SQLWARNING, NOT FOUND or SQLSTATE

EXIT HANDLER (stop procedure on error)

DELIMITER //

CREATE PROCEDURE insertEmployeeSafe(IN emp_id INT, IN emp_name VARCHAR(50))
BEGIN
   DECLARE EXIT HANDLER FOR SQLEXCEPTION
   BEGIN
      -- What to do on error
      SELECT 'An error occurred while inserting employee.' AS ErrorMessage;
   END;

   INSERT INTO accounts (emp_id, emp_name)
   VALUES (emp_id, emp_name);

   SELECT 'Employee inserted successfully!' AS SuccessMessage;
END //

DELIMITER ;

• If an error occurs during INSERT (e.g., duplicate employee_id), the EXIT HANDLER is triggered.
• Procedure stops and displays the error message.
• If no error, it continues normally.

Call it:

CALL insertEmployeeSafe(1, 'John');

CONTINUE HANDLER (ignore error and keep going)

DELIMITER //

CREATE PROCEDURE insertWithIgnore(IN emp_id INT, IN emp_name VARCHAR(50))
BEGIN
   DECLARE CONTINUE HANDLER FOR 1062 -- Duplicate entry error code
   BEGIN
      SELECT 'Duplicate entry ignored.' AS WarningMessage;
   END;

   INSERT INTO employees (employee_id, first_name)
   VALUES (emp_id, emp_name);

   SELECT 'Insert attempted.' AS InfoMessage;
END //

DELIMITER ;

• If a duplicate key error (1062) occurs, the CONTINUE HANDLER executes, but the procedure continues.
• It won’t stop execution, just prints a warning.

Call it:

CALL insertWithIgnore(1, 'Alice');