Stored Procedures

As databases grow more complex, repeating the same SQL logic across applications becomes inefficient and risky.

Stored Procedures allow you to store SQL logic inside the database and execute it whenever needed.

What is a Stored Procedure?

A stored procedure is a named block of SQL code that is stored in the database and executed on demand.

It can contain:

• SQL statements
• Conditional logic
• Loops
• Parameters

Stored procedures run directly on the database server, making them efficient and secure.

Why Use Stored Procedures?

Stored procedures provide several benefits:

• Reusable SQL logic
• Improved performance
• Reduced network traffic
• Better security and access control

They are widely used in enterprise applications.

Basic Stored Procedure Syntax (MySQL)

MySQL requires changing the delimiter when creating stored procedures.

DELIMITER $$

CREATE PROCEDURE get_all_employees()
BEGIN
  SELECT * FROM employees;
END $$

DELIMITER ;
  

Once created, the procedure can be executed using:

CALL get_all_employees();
  

Stored Procedure with Input Parameters

Procedures can accept input parameters.

Example: Get employees from a specific department.

DELIMITER $$

CREATE PROCEDURE get_employees_by_dept(IN dept_id INT)
BEGIN
  SELECT name, salary
  FROM employees
  WHERE department_id = dept_id;
END $$

DELIMITER ;
  

Call the procedure:

CALL get_employees_by_dept(1);
  

Stored Procedure with Multiple Parameters

Example: Employees with salary greater than a value.

DELIMITER $$

CREATE PROCEDURE get_high_salary_employees(
  IN min_salary INT,
  IN dept_id INT
)
BEGIN
  SELECT name, salary
  FROM employees
  WHERE salary >= min_salary
    AND department_id = dept_id;
END $$

DELIMITER ;
  

IN, OUT, and INOUT Parameters

OUT Parameter Example

DELIMITER $$

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

DELIMITER ;
  

Call the procedure:

CALL get_employee_count(@count);
SELECT @count;
  

Error Handling (Basic)

Stored procedures can handle errors using handlers.

DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
SET @error = 'An error occurred';
  

Error handling becomes important in production systems.

Dropping a Stored Procedure

DROP PROCEDURE IF EXISTS get_all_employees;
  

Stored Procedures vs Views

Common Beginner Mistakes

• Forgetting to change delimiter
• Hardcoding values instead of parameters
• Overusing procedures for simple queries
• Not documenting procedures

When Should You Use Stored Procedures?

Use stored procedures when:

• Business logic is reused
• Security is critical
• Complex operations must be centralized

What’s Next?

In the next lesson, we will explore SQL Functions, which return values and can be used inside queries.