Stored Procedures

A stored procedure is a named program of SQL statements and procedural logic (variables, IF, loops, error handling) stored in the database and executed with a call.

Concept

A stored procedure is a named program of SQL statements and procedural logic (variables, IF, loops, error handling) stored in the database and executed with a call. Procedures can accept parameters, modify data, manage transactions (in most DBMSs), and return result sets or output parameters.

PL/pgSQL (also PL/Python, PL/Perl...) CREATE PROCEDURE (11+, can COMMIT); functions for most logic MySQL / MariaDB SQL/PSM-style stored programs CREATE PROCEDURE CREATE PROCEDURE / CREATE PROC

CREATE PROCEDURE, usually inside packages No stored procedures (logic lives in the application)

Why It Matters

Encapsulation: complex business operations (place order, transfer funds) become one call with consistent rules.

Performance: fewer network round trips; plans may be cached.

Security: grant EXECUTE on a procedure without granting direct table access.

Consistency: every application uses the same logic.

Trade-offs: logic split between app and DB, harder version control/testing if not managed carefully, vendor lock-in.

Syntax

-- MySQL
DELIMITER //
CREATE PROCEDURE proc_name(IN p1 INT, OUT p2 DECIMAL(10,2), INOUT p3 INT)
BEGIN
    DECLARE v_local INT DEFAULT 0;
    -- statements
END //
DELIMITER ;
CALL proc_name(1, @out_value, @inout_value);

-- SQL Server
CREATE PROCEDURE dbo.proc_name
    @p1 INT,
    @p2 DECIMAL(10,2) OUTPUT
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @v_local INT = 0;
    -- statements
END;
EXEC dbo.proc_name @p1 = 1, @p2 = @result OUTPUT;

-- PostgreSQL 11+
CREATE OR REPLACE PROCEDURE proc_name(p1 INT, INOUT p2 NUMERIC DEFAULT NULL)
LANGUAGE plpgsql
AS $$
DECLARE
    v_local INT := 0;
BEGIN
    -- statements
END;
$$;
CALL proc_name(1, NULL);

-- Oracle
CREATE OR REPLACE PROCEDURE proc_name(p1 IN NUMBER, p2 OUT NUMBER) AS
    v_local NUMBER := 0;
BEGIN
    -- statements
    NULL;
END;
/
EXEC proc_name(1, :result);   -- SQL*Plus; or BEGIN proc_name(1, v); END;

Example

25.1 Simple procedure with an IN parameter (returns a result set) (DELIMITER // in the MySQL client temporarily changes the statement terminator so the ; inside the body does not end the CREATE statement.) 25.2 OUT parameters 25.3 INOUT parameters

SQL Server has no INOUT keyword; an OUTPUT parameter is both input and output.

25.4 Variables, conditional logic, and loops Walk through for threshold 20: products under 20 are Laptop (15) and Standing Desk (0). Standing Desk → URGENT. Laptop: is 15 < 20 / 2 = 10? No → "OK-ish". Two products restocked. (The loop order is not guaranteed without ORDER BY in the loop query.)

Prefer set-based SQL (one UPDATE for many rows) over loops whenever possible; loops process rows one by one and are much slower on large data. The PostgreSQL example above could be a single UPDATE ... WHERE stock < p_threshold.

25.5 Error handling and transactions: place-order procedure When an exception is raised, all changes made by the call are rolled back (the CALL runs within a transaction). To catch errors inside PL/pgSQL:

MySQL error handling:

SQL Server uses BEGIN TRY ... END TRY BEGIN CATCH ... END CATCH with THROW (Module 21). Oracle uses EXCEPTION WHEN ... THEN blocks and RAISE_APPLICATION_ERROR(-20001, 'message').

25.6 Calling and dropping procedures CALL proc(args); DROP PROCEDURE [IF EXISTS] proc(arg types); DROP PROCEDURE [IF EXISTS] proc; EXEC proc @p = v; DROP PROCEDURE [IF EXISTS] dbo.proc;

EXEC proc(args); / BEGIN proc(args); END; / CALL proc(args) DROP PROCEDURE proc;

-- MySQL
DELIMITER //
CREATE PROCEDURE get_customer_orders(IN p_customer_id INT)
BEGIN
    SELECT order_id, order_date, status, total_amount
    FROM orders
    WHERE customer_id = p_customer_id
    ORDER BY order_date;
END //
DELIMITER ;

CALL get_customer_orders(1);

-- SQL Server
CREATE PROCEDURE dbo.get_customer_orders @customer_id INT
AS
BEGIN
    SET NOCOUNT ON;
    SELECT order_id, order_date, status, total_amount
    FROM orders
    WHERE customer_id = @customer_id
    ORDER BY order_date;
END;

EXEC dbo.get_customer_orders @customer_id = 1;

-- MySQL
DELIMITER //
CREATE PROCEDURE customer_stats(
    IN  p_customer_id INT,
    OUT p_order_count INT,
    OUT p_total_spent DECIMAL(12,2)
)
BEGIN
    SELECT COUNT(*), COALESCE(SUM(total_amount), 0)
    INTO p_order_count, p_total_spent
    FROM orders
    WHERE customer_id = p_customer_id AND status <> 'CANCELLED';
END //
DELIMITER ;

CALL customer_stats(2, @cnt, @total);
SELECT @cnt AS order_count, @total AS total_spent;

-- SQL Server
CREATE PROCEDURE dbo.customer_stats
    @customer_id INT,
    @order_count INT OUTPUT,
    @total_spent DECIMAL(12,2) OUTPUT
AS
BEGIN
    SET NOCOUNT ON;
    SELECT @order_count = COUNT(*), @total_spent = COALESCE(SUM(total_amount), 0)
    FROM orders
    WHERE customer_id = @customer_id AND status <> 'CANCELLED';
END;

DECLARE @c INT, @t DECIMAL(12,2);
EXEC dbo.customer_stats @customer_id = 2, @order_count = @c OUTPUT, @total_spent = @t OUTPUT;
SELECT @c AS order_count, @t AS total_spent;

-- MySQL
DELIMITER //
CREATE PROCEDURE apply_discount(INOUT p_amount DECIMAL(12,2), IN p_percent DECIMAL(5,2))
BEGIN
    SET p_amount = ROUND(p_amount * (1 - p_percent / 100), 2);
END //
DELIMITER ;

SET @amt = 18000.00;
CALL apply_discount(@amt, 10);
SELECT @amt;     -- 16200.00

-- PostgreSQL (procedures support IN and INOUT; OUT supported from PostgreSQL 14)
CREATE OR REPLACE PROCEDURE apply_discount(INOUT p_amount NUMERIC, p_percent NUMERIC)
LANGUAGE plpgsql AS $$
BEGIN
    p_amount := ROUND(p_amount * (1 - p_percent / 100), 2);
END;
$$;
CALL apply_discount(18000.00, 10);   -- returns p_amount = 16200.00

-- PostgreSQL: restock low-inventory products, categorized by urgency
CREATE OR REPLACE PROCEDURE restock_low_inventory(p_threshold INT, p_restock_qty INT)
LANGUAGE plpgsql
AS $$
DECLARE
    rec          RECORD;
    v_restocked  INT := 0;
BEGIN
    FOR rec IN SELECT product_id, product_name, stock FROM products WHERE stock < p_threshold
    LOOP
        IF rec.stock = 0 THEN
            RAISE NOTICE 'URGENT: % is out of stock', rec.product_name;
        ELSIF rec.stock < p_threshold / 2 THEN
            RAISE NOTICE 'LOW: % has % units', rec.product_name, rec.stock;
        ELSE
            RAISE NOTICE 'OK-ish: % has % units', rec.product_name, rec.stock;
        END IF;

        UPDATE products SET stock = stock + p_restock_qty WHERE product_id = rec.product_id;
        v_restocked := v_restocked + 1;
    END LOOP;

    RAISE NOTICE 'Restocked % products', v_restocked;
END;
$$;

CALL restock_low_inventory(20, 50);
-- NOTICE:  OK-ish: Laptop has 15 units
-- NOTICE:  URGENT: Standing Desk is out of stock
-- NOTICE:  Restocked 2 products

-- MySQL: WHILE loop generating a calendar table
DELIMITER //
CREATE PROCEDURE fill_calendar(IN p_start DATE, IN p_end DATE)
BEGIN
    DECLARE d DATE DEFAULT p_start;
    WHILE d <= p_end DO
        INSERT INTO calendar (cal_date, is_weekend)
        VALUES (d, DAYOFWEEK(d) IN (1, 7));
        SET d = DATE_ADD(d, INTERVAL 1 DAY);
    END WHILE;
END //
DELIMITER ;

-- SQL Server: IF / WHILE
DECLARE @i INT = 1;
WHILE @i <= 5
BEGIN
    IF @i % 2 = 0 PRINT CONCAT(@i, ' is even');
    ELSE PRINT CONCAT(@i, ' is odd');
    SET @i += 1;
END;

-- PostgreSQL 11+ procedure
CREATE OR REPLACE PROCEDURE place_order(
    p_order_id     INT,
    p_customer_id  INT,
    p_product_id   INT,
    p_quantity     INT
)
LANGUAGE plpgsql
AS $$
DECLARE
    v_price  NUMERIC(10,2);
    v_stock  INT;
BEGIN
    SELECT price, stock INTO v_price, v_stock
    FROM products
    WHERE product_id = p_product_id
    FOR UPDATE;                                   -- lock the product row

    IF NOT FOUND THEN
        RAISE EXCEPTION 'Product % does not exist', p_product_id;
    END IF;

    IF v_stock < p_quantity THEN
        RAISE EXCEPTION 'Insufficient stock for product %: requested %, available %',
                        p_product_id, p_quantity, v_stock;
    END IF;

    INSERT INTO orders (order_id, customer_id, order_date, status, total_amount)
    VALUES (p_order_id, p_customer_id, CURRENT_DATE, 'PENDING', v_price * p_quantity);

    INSERT INTO order_items (order_id, product_id, quantity, unit_price)
    VALUES (p_order_id, p_product_id, p_quantity, v_price);

    UPDATE products SET stock = stock - p_quantity WHERE product_id = p_product_id;
END;
$$;

CALL place_order(3001, 3, 105, 2);     -- succeeds: Monitor stock 25 → 23
CALL place_or
…

BEGIN
    -- risky statements
EXCEPTION
    WHEN unique_violation THEN
        RAISE NOTICE 'Duplicate order id';
    WHEN OTHERS THEN
        RAISE NOTICE 'Error: %', SQLERRM;
        RAISE;   -- re-throw
END;

DELIMITER //
CREATE PROCEDURE safe_transfer(IN p_from VARCHAR(20), IN p_to VARCHAR(20), IN p_amount DECIMAL(15,2))
BEGIN
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        RESIGNAL;                            -- re-raise to the caller
    END;

    START TRANSACTION;
    UPDATE bank_accounts SET balance = balance - p_amount WHERE account_no = p_from AND balance >= p_amount;
    IF ROW_COUNT() = 0 THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Insufficient funds or unknown account';
    END IF;
    UPDATE bank_accounts SET balance = balance + p_amount WHERE account_no = p_to;
    COMMIT;
END //
DELIMITER ;

Expected Result

After CALL place_order(3001, 3, 105, 2);:

SELECT o.order_id, o.total_amount, p.stock
FROM orders o JOIN order_items oi ON oi.order_id = o.order_id
JOIN products p ON p.product_id = oi.product_id
WHERE o.order_id = 3001;

Common Mistakes

Forgetting DELIMITER in the MySQL client.

Row-by-row loops where a single set-based statement would do.

Swallowing errors (catching and not re-raising), hiding failures from the caller.

Dynamic SQL built by concatenating parameters (SQL injection inside the database — use EXECUTE ... USING in PostgreSQL, sp_executesql with parameters in SQL Server, prepared statements in MySQL).

Parameter names equal to column names (WHERE customer_id = customer_id is always true in some dialects). Prefix parameters (p_, @).

Not keeping procedure source in version control.

Best Practices

Use procedures for multi-step business operations that must be atomic.

Keep business logic set-based; loops only when unavoidable.

Validate inputs and raise meaningful errors.

Use SET NOCOUNT ON in SQL Server procedures.

Version, review, and test procedures like application code (migration tools, unit tests like tSQLt, pgTAP, utPLSQL).

Interview Relevance

Medium-high (especially for SQL Server/Oracle roles): "What is a stored procedure?", "Advantages and disadvantages?", "IN vs OUT vs INOUT?", "Procedure vs function?", "How do you handle errors?".

Practice Questions

Write a procedure that returns all employees in a given department.

Write a procedure with an OUT parameter returning the number of products in a category.

Write a procedure that raises salaries by a percentage for a department, rejecting percentages above 20.

Rewrite a cursor loop that updates rows one by one as a single UPDATE.

Add error handling to the transfer procedure so that it logs failed attempts to an error_log table.