Stored Procedures

Advanced

⏱️ 15 mins read

What You'll Learn

A stored procedure is a reusable block of SQL code stored in the database. It accepts IN (input), OUT (output), and INOUT parameters, can contain conditional logic, loops, and transactions. Benefits: reduces network round-trips, centralizes business logic, enables access control.

Syntax

CREATE PROCEDURE name(IN param TYPE)
BEGIN
  -- SQL statements
END;

CALL procedure_name(args);

Example

DELIMITER $$
CREATE PROCEDURE GetDeptReport(
  IN dept_name VARCHAR(100),
  IN min_salary DECIMAL(10,2)
)
BEGIN
  SELECT
    name, salary, hire_date,
    DATEDIFF(NOW(), hire_date) AS days_employed
  FROM employees
  WHERE department = dept_name
    AND salary >= min_salary
  ORDER BY salary DESC;
END$$
DELIMITER ;

-- Call it
CALL GetDeptReport('Engineering', 80000);

Common Mistakes

Stored procedures are harder to version-control, test, and debug than application code. They couple business logic to the database. Over-using them can make the system harder to migrate to a different database.

Interview Tips

Know the difference: Stored Procedure (can have side effects, doesn't always return a value) vs Function (always returns a value, can be used in SELECT). Mention that procedures reduce network latency for complex multi-step operations.

Practice

Write a stored procedure that takes a start date and end date, and returns: daily revenue, order count, average order value, and the top-selling product for each day in that period.

Back to Full Roadmap