SQL Stored Procedures: Reusable Logic Inside the Database

A stored procedure is a named block of SQL that is saved inside the database and run on demand. It can take parameters, run several statements, use variables and IF logic, and wrap everything in a transaction. Think of it as a function that lives in the database.

Why use stored procedures?#

• Reuse: complex operations ("place an order": insert the order, insert the items, reduce stock) are written once and called by every application.
• Fewer round trips: many statements run inside the database with a single call.
• Security: users can be allowed to run a procedure without having direct access to the tables it changes.
• Consistency: business rules live in one place.

A first procedure (MySQL)#

A procedure that gives a percentage raise to one department:

DELIMITER //

CREATE PROCEDURE give_raise(IN dept VARCHAR(50), IN pct DECIMAL(5, 2))
BEGIN
  UPDATE employees
  SET salary = salary * (1 + pct / 100)
  WHERE department = dept;
END //

DELIMITER ;

• IN dept VARCHAR(50) declares an input parameter with a type.
• BEGIN ... END holds the body, which can contain many statements.
• DELIMITER // is a client trick: it tells the MySQL command-line tool not to stop at the ; inside the body. Graphical tools usually do not need it.

Call it with CALL:

CALL give_raise('Engineering', 10);

Ravi's salary goes from 78,000 to 85,800 and Meera's from 81,000 to 89,100, exactly as if you had run the UPDATE yourself.

The same procedure in PostgreSQL#

CREATE OR REPLACE PROCEDURE give_raise(dept VARCHAR, pct NUMERIC)
LANGUAGE plpgsql
AS $$
BEGIN
  UPDATE employees
  SET salary = salary * (1 + pct / 100)
  WHERE department = dept;
END;
$$;

CALL give_raise('Engineering', 10);

The body sits between $$ markers, and LANGUAGE plpgsql names the procedural language. (PostgreSQL added CREATE PROCEDURE in version 11; before that, everything was written as functions.)

The same procedure in SQL Server#

CREATE PROCEDURE give_raise
  @dept VARCHAR(50),
  @pct  DECIMAL(5, 2)
AS
BEGIN
  UPDATE employees
  SET salary = salary * (1 + @pct / 100)
  WHERE department = @dept;
END;

EXEC give_raise @dept = 'Engineering', @pct = 10;

T-SQL parameters start with @, and procedures are run with EXEC.

Returning results#

A procedure can return rows by ending with a SELECT (MySQL and SQL Server):

-- MySQL
DELIMITER //
CREATE PROCEDURE customer_orders(IN cid INT)
BEGIN
  SELECT order_id, amount, order_date
  FROM orders
  WHERE customer_id = cid
  ORDER BY order_date;
END //
DELIMITER ;

CALL customer_orders(1);

order_id | amount | order_date
---------+--------+-----------
101      | 250    | 2026-01-05
102      | 120    | 2026-02-11

Procedures can also return single values through output parameters (OUT total DECIMAL(10,2) in MySQL, @total DECIMAL(10,2) OUTPUT in SQL Server).

Logic inside a procedure#

Procedural languages add variables, conditions and error handling. A MySQL example that refuses invalid input:

DELIMITER //
CREATE PROCEDURE set_stock(IN pid INT, IN new_stock INT)
BEGIN
  IF new_stock < 0 THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Stock cannot be negative';
  ELSE
    UPDATE products SET stock = new_stock WHERE product_id = pid;
  END IF;
END //
DELIMITER ;

Procedures vs functions#

Managing procedures#

DROP PROCEDURE IF EXISTS give_raise;

Keep the source of every procedure in version control along with your application code, so changes are reviewed and can be rolled back.

When not to use them#

Heavy use of stored procedures splits business logic between the application and the database, which can make code harder to test, debug and move to a different database. Many modern teams keep most logic in application code and use procedures selectively, for operations that benefit from running close to the data.

Common mistakes#

Practice#

DELIMITER //

CREATE PROCEDURE add_customer(IN p_name VARCHAR(100), IN p_city VARCHAR(50))
BEGIN
  INSERT INTO customers (name, city)
  VALUES (p_name, p_city);
END //

DELIMITER ;

CALL add_customer('Kiran', 'Hyderabad');

Key takeaway#