SQL Stored Procedures | Tutorial Reference

Learn how to create, use, and optimize SQL stored procedures in MySQL, PostgreSQL, and SQL Server with parameter handling, control flow, and error management.

As your application grows, you will find certain database operations that involve multiple SQL statements, conditional logic, and business rules that must execute together reliably. You could write all this logic in your application code and send each statement to the database individually, but there is a better way. SQL stored procedures let you bundle multiple SQL statements, control flow logic, and error handling into a named, reusable program that lives inside the database and executes directly on the database server.

This guide covers what stored procedures are and why they exist, the CREATE PROCEDURE syntax across MySQL, PostgreSQL, and SQL Server, how to use IN, OUT, and INOUT parameters, and how to call procedures from SQL and application code. Every concept includes practical, working examples with clear outputs so you can start writing stored procedures immediately.

What Are Stored Procedures?

A stored procedure is a named collection of SQL statements and procedural logic that is saved in the database and can be executed on demand. Think of it as a function that lives inside the database server rather than in your application code.

When you call a stored procedure, the database:

Looks up the procedure definition (already parsed and stored)
Executes all the SQL statements inside it on the server
Returns results or output values to the caller

Why Stored Procedures Exist

Benefit	Explanation
Reduced network traffic	One call to the database instead of multiple round-trips for each statement
Code reuse	Define the logic once, call it from any application, language, or script
Security	Grant users permission to execute a procedure without giving them direct table access
Centralized business logic	Critical rules live in one place, enforced regardless of which application accesses the data
Performance	Pre-parsed and optimized execution plan (varies by database)
Atomicity	Multiple statements can be wrapped in a transaction inside the procedure

Stored Procedure vs Inline SQL

-- Without a stored procedure: application sends multiple statements
-- Round-trip 1
SELECT balance FROM accounts WHERE id = 1;
-- Round-trip 2 (application checks balance)
UPDATE accounts SET balance = balance - 500 WHERE id = 1;
-- Round-trip 3
UPDATE accounts SET balance = balance + 500 WHERE id = 2;
-- Round-trip 4
INSERT INTO audit_log (action, details) VALUES ('transfer', 'Account 1 → 2: $500');

-- With a stored procedure: one call does everything
CALL transfer_funds(1, 2, 500.00);
-- Single round-trip. All logic executes on the server.

Four network round-trips become one. The logic is encapsulated, reusable, and consistent.

The Sample Data

We will use these tables throughout the guide:

accounts table:

id	owner	balance
1	Alice	5000.00
2	Bob	3000.00
3	Charlie	1500.00

CREATE TABLE accounts (
    id INT PRIMARY KEY,
    owner VARCHAR(50) NOT NULL,
    balance DECIMAL(12,2) NOT NULL
);

-- Insert sample data
INSERT INTO accounts (id, owner, balance) VALUES
(1, 'Alice', 5000.00),
(2, 'Bob', 3000.00),
(3, 'Charlie', 1500.00);

products table:

id	name	stock	price
101	Widget Pro	10	79.99
102	Gadget Max	5	149.99
103	Desk Lamp	0	45.00

CREATE TABLE products (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    stock INT NOT NULL,
    price DECIMAL(10,2) NOT NULL
);

-- Insert sample data
INSERT INTO products (id, name, stock, price) VALUES
(101, 'Widget Pro', 10, 79.99),
(102, 'Gadget Max', 5, 149.99),
(103, 'Desk Lamp', 0, 45.00);

audit_log table:

id	action	details	created_at

CREATE TABLE audit_log (
    id INT PRIMARY KEY AUTO_INCREMENT,
    action VARCHAR(100) NOT NULL,
    details TEXT,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

orders table:

id	customer_id	product_id	quantity	total	status

CREATE TABLE orders (
    id INT PRIMARY KEY AUTO_INCREMENT,
    customer_id INT NOT NULL,
    product_id INT NOT NULL,
    quantity INT NOT NULL,
    total DECIMAL(10,2) NOT NULL,
    status VARCHAR(20) DEFAULT 'pending',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

CREATE PROCEDURE Syntax

Each database has its own procedural language and syntax. Let's cover the three most popular.

MySQL

MySQL uses BEGIN ... END blocks with its own procedural extensions:

DELIMITER //

CREATE PROCEDURE procedure_name (
    IN param1 datatype,
    OUT param2 datatype
)
BEGIN
    -- SQL statements and logic here
END //

DELIMITER ;

PostgreSQL

PostgreSQL uses PL/pgSQL (Procedural Language/PostgreSQL) inside a $$ ... $$ block:

CREATE OR REPLACE PROCEDURE procedure_name (
    IN param1 datatype,
    INOUT param2 datatype
)
LANGUAGE plpgsql
AS $$
BEGIN
    -- SQL statements and logic here
END;
$$;

PostgreSQL Procedures vs Functions

PostgreSQL added CREATE PROCEDURE in version 11. Before that, everything was done with CREATE FUNCTION. The key differences:

Feature	PROCEDURE	FUNCTION
Can manage transactions (`COMMIT`/`ROLLBACK`)?	Yes	No
Returns a value?	No (uses `OUT`/`INOUT` params)	Yes (`RETURNS` clause)
Called with	`CALL`	`SELECT function_name()`

Use PROCEDURE for operations that modify data and need transaction control. Use FUNCTION for computations and queries that return values.

SQL Server

SQL Server uses T-SQL (Transact-SQL):

CREATE PROCEDURE procedure_name
    @param1 datatype,
    @param2 datatype OUTPUT
AS
BEGIN
    -- SQL statements and logic here
END;

SQL Server does not require a special delimiter or language declaration. Parameters are prefixed with @.

Your First Stored Procedure

Let's create a simple procedure that logs an action to the audit log.

MySQL

DELIMITER //

CREATE PROCEDURE log_action (
    IN p_action VARCHAR(100),
    IN p_details VARCHAR(500)
)
BEGIN
    INSERT INTO audit_log (action, details, created_at)
    VALUES (p_action, p_details, NOW());
END //

DELIMITER ;

PostgreSQL

CREATE OR REPLACE PROCEDURE log_action (
    IN p_action VARCHAR(100),
    IN p_details VARCHAR(500)
)
LANGUAGE plpgsql
AS $$
BEGIN
    INSERT INTO audit_log (action, details, created_at)
    VALUES (p_action, p_details, NOW());
END;
$$;

SQL Server

CREATE PROCEDURE log_action
    @p_action VARCHAR(100),
    @p_details VARCHAR(500)
AS
BEGIN
    INSERT INTO audit_log (action, details, created_at)
    VALUES (@p_action, @p_details, GETDATE());
END;

Calling the Procedure

-- MySQL / PostgreSQL
CALL log_action('user_login', 'User Alice logged in from 192.168.1.1');

-- SQL Server
EXEC log_action 'user_login', 'User Alice logged in from 192.168.1.1';
-- or
EXECUTE log_action @p_action = 'user_login', @p_details = 'User Alice logged in';

After calling, the audit_log table contains:

id	action	details	created_at
1	user_login	User Alice logged in from 192.168.1.1	2024-04-15 10:30:00

Parameters: IN, OUT, INOUT

Parameters allow you to pass data into a procedure and receive data back. There are three parameter modes.

IN Parameters (Input)

IN parameters pass values into the procedure. They are read-only inside the procedure. This is the default mode.

-- The procedure receives values but does not send anything back
CREATE PROCEDURE deactivate_account (
    IN p_account_id INT
)

You have already seen IN parameters in the examples above.

OUT Parameters (Output)

OUT parameters send values back to the caller. The procedure assigns a value to the OUT parameter, and the caller can read it after the procedure completes.

MySQL

DELIMITER //

CREATE PROCEDURE get_account_balance (
    IN p_account_id INT,
    OUT p_balance DECIMAL(10,2),
    OUT p_owner VARCHAR(100)
)
BEGIN
    SELECT balance, owner
    INTO p_balance, p_owner
    FROM accounts
    WHERE id = p_account_id;
END //

DELIMITER ;

Calling with OUT parameters in MySQL:

CALL get_account_balance(1, @bal, @own);
SELECT @bal AS balance, @own AS owner;

Output:

balance	owner
5000.00	Alice

MySQL uses session variables (@variable) to receive OUT parameter values.

PostgreSQL

PostgreSQL uses INOUT parameters for procedures (pure OUT is supported in functions but not in procedures called with CALL):

CREATE OR REPLACE PROCEDURE get_account_balance (
    IN p_account_id INT,
    INOUT p_balance DECIMAL(10,2) DEFAULT 0,
    INOUT p_owner VARCHAR(100) DEFAULT ''
)
LANGUAGE plpgsql
AS $$
BEGIN
    SELECT balance, owner
    INTO p_balance, p_owner
    FROM accounts
    WHERE id = p_account_id;
END;
$$;

Calling:

CALL get_account_balance(1, NULL, NULL);

Output:

p_balance	p_owner
5000.00	Alice

SQL Server

CREATE PROCEDURE get_account_balance
    @p_account_id INT,
    @p_balance DECIMAL(10,2) OUTPUT,
    @p_owner VARCHAR(100) OUTPUT
AS
BEGIN
    SELECT @p_balance = balance, @p_owner = owner
    FROM accounts
    WHERE id = @p_account_id;
END;

Calling:

DECLARE @bal DECIMAL(10,2);
DECLARE @own VARCHAR(100);

EXEC get_account_balance 1, @bal OUTPUT, @own OUTPUT;

SELECT @bal AS balance, @own AS owner;

Output:

balance	owner
5000.00	Alice

INOUT Parameters (Input and Output)

INOUT parameters serve double duty: they pass a value in and receive a modified value back out. The procedure can read the initial value and then overwrite it.

MySQL

DELIMITER //

CREATE PROCEDURE apply_discount (
    INOUT p_price DECIMAL(10,2),
    IN p_discount_pct INT
)
BEGIN
    SET p_price = p_price * (1 - p_discount_pct / 100.0);
END //

DELIMITER ;

Calling:

SET @item_price = 100.00;
CALL apply_discount(@item_price, 20);  -- 20% discount
SELECT @item_price AS discounted_price;

Output:

discounted_price
80.00

The variable started at 100.00, was passed into the procedure, modified (100 * 0.80 = 80.00), and the updated value was returned.

PostgreSQL

CREATE OR REPLACE PROCEDURE apply_discount (
    INOUT p_price DECIMAL(10,2),
    IN p_discount_pct INT
)
LANGUAGE plpgsql
AS $$
BEGIN
    p_price := p_price * (1 - p_discount_pct / 100.0);
END;
$$;

Calling:

CALL apply_discount(100.00, 20);

Output:

p_price
80.00

SQL Server

SQL Server uses OUTPUT for both OUT and INOUT behavior. To use it as INOUT, simply pass a variable that already has a value:

CREATE PROCEDURE apply_discount
    @p_price DECIMAL(10,2) OUTPUT,
    @p_discount_pct INT
AS
BEGIN
    SET @p_price = @p_price * (1 - @p_discount_pct / 100.0);
END;

Calling:

DECLARE @price DECIMAL(10,2) = 100.00;
EXEC apply_discount @price OUTPUT, 20;
SELECT @price AS discounted_price;

Output:

discounted_price
80.00

Parameter Modes Summary

Mode	Direction	Can Read Input?	Can Set Output?	Default?
IN	Caller → Procedure	Yes	No	Yes (MySQL, PostgreSQL)
OUT	Procedure → Caller	No (starts as NULL)	Yes	No
INOUT	Both directions	Yes	Yes	No

Stored Procedures with Control Flow

Stored procedures become truly powerful when you add conditional logic, loops, and error handling.

IF / ELSE

MySQL

DELIMITER //

CREATE PROCEDURE transfer_funds (
    IN p_from_id INT,
    IN p_to_id INT,
    IN p_amount DECIMAL(10,2),
    OUT p_status VARCHAR(50)
)
BEGIN
    DECLARE v_balance DECIMAL(10,2);

    -- Check source balance
    SELECT balance INTO v_balance
    FROM accounts WHERE id = p_from_id;

    IF v_balance IS NULL THEN
        SET p_status = 'ERROR: Source account not found';
    ELSEIF p_amount <= 0 THEN
        SET p_status = 'ERROR: Amount must be positive';
    ELSEIF v_balance < p_amount THEN
        SET p_status = 'ERROR: Insufficient funds';
    ELSE
        -- Perform the transfer
        UPDATE accounts SET balance = balance - p_amount WHERE id = p_from_id;
        UPDATE accounts SET balance = balance + p_amount WHERE id = p_to_id;

        INSERT INTO audit_log (action, details, created_at)
        VALUES ('transfer', CONCAT('$', p_amount, ' from #', p_from_id, ' to #', p_to_id), NOW());

        SET p_status = 'SUCCESS';
    END IF;
END //

DELIMITER ;

Calling:

-- Successful transfer
CALL transfer_funds(1, 2, 500.00, @status);
SELECT @status;
-- Returns: SUCCESS

-- Insufficient funds
CALL transfer_funds(3, 1, 5000.00, @status);
SELECT @status;
-- Returns: ERROR: Insufficient funds

-- Invalid amount
CALL transfer_funds(1, 2, -100.00, @status);
SELECT @status;
-- Returns: ERROR: Amount must be positive

PostgreSQL

CREATE OR REPLACE PROCEDURE transfer_funds (
    IN p_from_id INT,
    IN p_to_id INT,
    IN p_amount DECIMAL(10,2),
    INOUT p_status VARCHAR(50) DEFAULT ''
)
LANGUAGE plpgsql
AS $$
DECLARE
    v_balance DECIMAL(10,2);
BEGIN
    -- Check source balance
    SELECT balance INTO v_balance
    FROM accounts WHERE id = p_from_id;

    IF v_balance IS NULL THEN
        p_status := 'ERROR: Source account not found';
        RETURN;
    ELSIF p_amount <= 0 THEN
        p_status := 'ERROR: Amount must be positive';
        RETURN;
    ELSIF v_balance < p_amount THEN
        p_status := 'ERROR: Insufficient funds';
        RETURN;
    END IF;

    -- Perform the transfer
    UPDATE accounts SET balance = balance - p_amount WHERE id = p_from_id;
    UPDATE accounts SET balance = balance + p_amount WHERE id = p_to_id;

    INSERT INTO audit_log (action, details, created_at)
    VALUES ('transfer', FORMAT('$%s from #%s to #%s', p_amount, p_from_id, p_to_id), NOW());

    p_status := 'SUCCESS';
END;
$$;

Calling:

CALL transfer_funds(1, 2, 500.00, NULL);

Output:

p_status
SUCCESS

SQL Server

CREATE PROCEDURE transfer_funds
    @p_from_id INT,
    @p_to_id INT,
    @p_amount DECIMAL(10,2),
    @p_status VARCHAR(50) OUTPUT
AS
BEGIN
    DECLARE @v_balance DECIMAL(10,2);

    SELECT @v_balance = balance
    FROM accounts WHERE id = @p_from_id;

    IF @v_balance IS NULL
    BEGIN
        SET @p_status = 'ERROR: Source account not found';
        RETURN;
    END

    IF @p_amount <= 0
    BEGIN
        SET @p_status = 'ERROR: Amount must be positive';
        RETURN;
    END

    IF @v_balance < @p_amount
    BEGIN
        SET @p_status = 'ERROR: Insufficient funds';
        RETURN;
    END

    -- Perform the transfer
    UPDATE accounts SET balance = balance - @p_amount WHERE id = @p_from_id;
    UPDATE accounts SET balance = balance + @p_amount WHERE id = @p_to_id;

    INSERT INTO audit_log (action, details, created_at)
    VALUES ('transfer',
            CONCAT('$', @p_amount, ' from #', @p_from_id, ' to #', @p_to_id),
            GETDATE());

    SET @p_status = 'SUCCESS';
END;

Loops

MySQL: WHILE Loop

DELIMITER //

CREATE PROCEDURE generate_test_accounts (
    IN p_count INT
)
BEGIN
    DECLARE v_counter INT DEFAULT 1;

    WHILE v_counter <= p_count DO
        INSERT INTO accounts (owner, balance)
        VALUES (CONCAT('TestUser_', v_counter), ROUND(RAND() * 10000, 2));

        SET v_counter = v_counter + 1;
    END WHILE;
END //

DELIMITER ;

CALL generate_test_accounts(5);

PostgreSQL: FOR Loop

CREATE OR REPLACE PROCEDURE generate_test_accounts (
    IN p_count INT
)
LANGUAGE plpgsql
AS $$
BEGIN
    FOR i IN 1..p_count LOOP
        INSERT INTO accounts (owner, balance)
        VALUES ('TestUser_' || i, ROUND((RANDOM() * 10000)::numeric, 2));
    END LOOP;
END;
$$;

SQL Server: WHILE Loop

CREATE PROCEDURE generate_test_accounts
    @p_count INT
AS
BEGIN
    DECLARE @counter INT = 1;

    WHILE @counter <= @p_count
    BEGIN
        INSERT INTO accounts (owner, balance)
        VALUES (CONCAT('TestUser_', @counter), ROUND(RAND() * 10000, 2));

        SET @counter = @counter + 1;
    END
END;

Error Handling in Stored Procedures

Proper error handling prevents procedures from leaving the database in an inconsistent state when something goes wrong.

MySQL: DECLARE HANDLER

DELIMITER //

CREATE PROCEDURE safe_transfer (
    IN p_from_id INT,
    IN p_to_id INT,
    IN p_amount DECIMAL(10,2),
    OUT p_status VARCHAR(200)
)
BEGIN
    DECLARE v_from_balance DECIMAL(12,2);
    DECLARE v_to_balance DECIMAL(12,2);

    -- Handler for general errors
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        SET p_status = 'ERROR: Transaction failed';
    END;

    -- Handler for not found accounts
    DECLARE EXIT HANDLER FOR NOT FOUND
    BEGIN
        ROLLBACK;
        SET p_status = 'ERROR: Account not found';
    END;

    START TRANSACTION;

    -- Check if accounts exist
    SELECT balance INTO v_from_balance FROM accounts WHERE id = p_from_id;
    SELECT balance INTO v_to_balance FROM accounts WHERE id = p_to_id;

    -- Check sufficient balance
    IF v_from_balance < p_amount THEN
        ROLLBACK;
        SET p_status = 'ERROR: Insufficient funds';
    ELSE
        -- Perform transfer
        UPDATE accounts SET balance = balance - p_amount WHERE id = p_from_id;
        UPDATE accounts SET balance = balance + p_amount WHERE id = p_to_id;

        -- Log in audit
        INSERT INTO audit_log (action, details)
        VALUES ('transfer', CONCAT('$', p_amount, ' transferred from account ', p_from_id, ' to account ', p_to_id));

        COMMIT;
        SET p_status = 'SUCCESS';
    END IF;
END //

DELIMITER ;

MySQL uses handler declarations at the beginning of the BEGIN ... END block. The handler type determines what happens when the error occurs:

Handler Type	Behavior
`EXIT HANDLER`	Exits the current `BEGIN...END` block after handling
`CONTINUE HANDLER`	Continues execution at the next statement after the error

PostgreSQL: EXCEPTION Block

CREATE OR REPLACE PROCEDURE safe_transfer (
    IN p_from_id INT,
    IN p_to_id INT,
    IN p_amount DECIMAL(10,2),
    INOUT p_status VARCHAR(200) DEFAULT ''
)
LANGUAGE plpgsql
AS $$
BEGIN
    UPDATE accounts SET balance = balance - p_amount WHERE id = p_from_id;
    UPDATE accounts SET balance = balance + p_amount WHERE id = p_to_id;

    INSERT INTO audit_log (action, details, created_at)
    VALUES ('transfer', FORMAT('$%s transferred', p_amount), NOW());

    p_status := 'SUCCESS';

EXCEPTION
    WHEN check_violation THEN
        p_status := 'ERROR: Insufficient funds (check constraint violated)';
    WHEN foreign_key_violation THEN
        p_status := 'ERROR: Invalid account reference';
    WHEN OTHERS THEN
        p_status := 'ERROR: ' || SQLERRM;
END;
$$;

PostgreSQL's EXCEPTION block automatically creates an implicit savepoint before the block and rolls back to it if an error occurs. You can catch specific error conditions or use WHEN OTHERS as a catch-all.

Common PostgreSQL error conditions:

Condition	When It Fires
`check_violation`	CHECK constraint violated
`unique_violation`	UNIQUE constraint violated
`foreign_key_violation`	FOREIGN KEY constraint violated
`not_null_violation`	NOT NULL constraint violated
`division_by_zero`	Division by zero
`OTHERS`	Any unhandled error

SQL Server: TRY / CATCH

CREATE PROCEDURE safe_transfer
    @p_from_id INT,
    @p_to_id INT,
    @p_amount DECIMAL(10,2),
    @p_status VARCHAR(200) OUTPUT
AS
BEGIN
    BEGIN TRY
        BEGIN TRANSACTION;

        UPDATE accounts SET balance = balance - @p_amount WHERE id = @p_from_id;
        UPDATE accounts SET balance = balance + @p_amount WHERE id = @p_to_id;

        INSERT INTO audit_log (action, details, created_at)
        VALUES ('transfer', CONCAT('$', @p_amount, ' transferred'), GETDATE());

        COMMIT TRANSACTION;
        SET @p_status = 'SUCCESS';
    END TRY
    BEGIN CATCH
        IF @@TRANCOUNT > 0
            ROLLBACK TRANSACTION;

        SET @p_status = CONCAT('ERROR: ', ERROR_MESSAGE());
    END CATCH
END;

SQL Server's TRY/CATCH is the most similar to modern programming languages. Error information functions available in the CATCH block:

Function	Returns
`ERROR_MESSAGE()`	Error message text
`ERROR_NUMBER()`	Error number
`ERROR_SEVERITY()`	Error severity level
`ERROR_STATE()`	Error state
`ERROR_LINE()`	Line number where the error occurred
`ERROR_PROCEDURE()`	Name of the procedure where the error occurred

Real-World Example: Order Processing

Let's build a complete order processing procedure that validates inventory, creates the order, deducts stock, and handles errors.

MySQL

DELIMITER //

CREATE PROCEDURE place_order (
    IN p_customer_id INT,
    IN p_product_id INT,
    IN p_quantity INT,
    OUT p_order_id INT,
    OUT p_status VARCHAR(200)
)
BEGIN
    DECLARE v_stock INT;
    DECLARE v_price DECIMAL(10,2);
    DECLARE v_total DECIMAL(10,2);
    DECLARE v_product_name VARCHAR(100);

    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        SET p_order_id = NULL;
        SET p_status = 'ERROR: Unexpected database error';
    END;

    -- Validate quantity
    IF p_quantity <= 0 THEN
        SET p_status = 'ERROR: Quantity must be positive';
        SET p_order_id = NULL;
        LEAVE;  -- Exit the procedure (MySQL specific)
    END IF;

    START TRANSACTION;

    -- Check inventory (with row lock)
    SELECT stock, price, name
    INTO v_stock, v_price, v_product_name
    FROM products
    WHERE id = p_product_id
    FOR UPDATE;

    IF v_stock IS NULL THEN
        ROLLBACK;
        SET p_status = 'ERROR: Product not found';
        SET p_order_id = NULL;
    ELSEIF v_stock < p_quantity THEN
        ROLLBACK;
        SET p_status = CONCAT('ERROR: Insufficient stock. Available: ', v_stock);
        SET p_order_id = NULL;
    ELSE
        -- Calculate total
        SET v_total = v_price * p_quantity;

        -- Create the order
        INSERT INTO orders (customer_id, product_id, quantity, total, status)
        VALUES (p_customer_id, p_product_id, p_quantity, v_total, 'confirmed');

        SET p_order_id = LAST_INSERT_ID();

        -- Deduct inventory
        UPDATE products SET stock = stock - p_quantity WHERE id = p_product_id;

        -- Log the order
        INSERT INTO audit_log (action, details, created_at)
        VALUES ('order_placed',
                CONCAT('Order #', p_order_id, ': ', p_quantity, 'x ', v_product_name,
                       ' ($', v_total, ')'),
                NOW());

        COMMIT;
        SET p_status = 'SUCCESS';
    END IF;
END //

DELIMITER ;

Calling:

-- Successful order
CALL place_order(1, 101, 3, @oid, @status);
SELECT @oid AS order_id, @status AS status;

order_id	status
1	SUCCESS

-- Out of stock
CALL place_order(2, 103, 5, @oid, @status);
SELECT @oid AS order_id, @status AS status;

order_id	status
NULL	ERROR: Insufficient stock. Available: 0

PostgreSQL

CREATE OR REPLACE PROCEDURE place_order (
    IN p_customer_id INT,
    IN p_product_id INT,
    IN p_quantity INT,
    INOUT p_order_id INT DEFAULT NULL,
    INOUT p_status VARCHAR(200) DEFAULT ''
)
LANGUAGE plpgsql
AS $$
DECLARE
    v_stock INT;
    v_price DECIMAL(10,2);
    v_total DECIMAL(10,2);
    v_product_name VARCHAR(100);
BEGIN
    -- Validate quantity
    IF p_quantity <= 0 THEN
        p_status := 'ERROR: Quantity must be positive';
        RETURN;
    END IF;

    -- Check inventory (with row lock)
    SELECT stock, price, name
    INTO v_stock, v_price, v_product_name
    FROM products
    WHERE id = p_product_id
    FOR UPDATE;

    IF NOT FOUND THEN
        p_status := 'ERROR: Product not found';
        RETURN;
    END IF;

    IF v_stock < p_quantity THEN
        p_status := FORMAT('ERROR: Insufficient stock. Available: %s', v_stock);
        RETURN;
    END IF;

    -- Calculate total
    v_total := v_price * p_quantity;

    -- Create the order
    INSERT INTO orders (customer_id, product_id, quantity, total, status)
    VALUES (p_customer_id, p_product_id, p_quantity, v_total, 'confirmed')
    RETURNING id INTO p_order_id;

    -- Deduct inventory
    UPDATE products SET stock = stock - p_quantity WHERE id = p_product_id;

    -- Log the order
    INSERT INTO audit_log (action, details, created_at)
    VALUES ('order_placed',
            FORMAT('Order #%s: %sx %s ($%s)', p_order_id, p_quantity, v_product_name, v_total),
            NOW());

    p_status := 'SUCCESS';

EXCEPTION
    WHEN OTHERS THEN
        p_order_id := NULL;
        p_status := 'ERROR: ' || SQLERRM;
END;
$$;

Calling:

CALL place_order(1, 101, 3, NULL, NULL);

Output:

p_order_id	p_status
1	SUCCESS

SQL Server

CREATE PROCEDURE place_order
    @p_customer_id INT,
    @p_product_id INT,
    @p_quantity INT,
    @p_order_id INT OUTPUT,
    @p_status VARCHAR(200) OUTPUT
AS
BEGIN
    DECLARE @v_stock INT;
    DECLARE @v_price DECIMAL(10,2);
    DECLARE @v_total DECIMAL(10,2);
    DECLARE @v_product_name VARCHAR(100);

    IF @p_quantity <= 0
    BEGIN
        SET @p_status = 'ERROR: Quantity must be positive';
        SET @p_order_id = NULL;
        RETURN;
    END

    BEGIN TRY
        BEGIN TRANSACTION;

        SELECT @v_stock = stock, @v_price = price, @v_product_name = name
        FROM products WITH (UPDLOCK, ROWLOCK)
        WHERE id = @p_product_id;

        IF @v_stock IS NULL
        BEGIN
            ROLLBACK TRANSACTION;
            SET @p_status = 'ERROR: Product not found';
            SET @p_order_id = NULL;
            RETURN;
        END

        IF @v_stock < @p_quantity
        BEGIN
            ROLLBACK TRANSACTION;
            SET @p_status = CONCAT('ERROR: Insufficient stock. Available: ', @v_stock);
            SET @p_order_id = NULL;
            RETURN;
        END

        SET @v_total = @v_price * @p_quantity;

        INSERT INTO orders (customer_id, product_id, quantity, total, status)
        VALUES (@p_customer_id, @p_product_id, @p_quantity, @v_total, 'confirmed');

        SET @p_order_id = SCOPE_IDENTITY();

        UPDATE products SET stock = stock - @p_quantity WHERE id = @p_product_id;

        INSERT INTO audit_log (action, details, created_at)
        VALUES ('order_placed',
                CONCAT('Order #', @p_order_id, ': ', @p_quantity, 'x ', @v_product_name,
                       ' ($', @v_total, ')'),
                GETDATE());

        COMMIT TRANSACTION;
        SET @p_status = 'SUCCESS';
    END TRY
    BEGIN CATCH
        IF @@TRANCOUNT > 0
            ROLLBACK TRANSACTION;

        SET @p_order_id = NULL;
        SET @p_status = CONCAT('ERROR: ', ERROR_MESSAGE());
    END CATCH
END;

Modifying and Dropping Procedures

Modifying an Existing Procedure

-- PostgreSQL: CREATE OR REPLACE
CREATE OR REPLACE PROCEDURE log_action (
    IN p_action VARCHAR(100),
    IN p_details VARCHAR(500)
)
LANGUAGE plpgsql
AS $$
BEGIN
    -- Updated: added severity level
    INSERT INTO audit_log (action, details, severity, created_at)
    VALUES (p_action, p_details, 'INFO', NOW());
END;
$$;

-- MySQL: Must DROP and recreate
DROP PROCEDURE IF EXISTS log_action;
-- Then CREATE PROCEDURE again...

-- SQL Server: ALTER PROCEDURE
ALTER PROCEDURE log_action
    @p_action VARCHAR(100),
    @p_details VARCHAR(500)
AS
BEGIN
    INSERT INTO audit_log (action, details, severity, created_at)
    VALUES (@p_action, @p_details, 'INFO', GETDATE());
END;

Dropping a Procedure

-- All databases
DROP PROCEDURE procedure_name;

-- Safe version (no error if it doesn't exist)
DROP PROCEDURE IF EXISTS procedure_name;  -- PostgreSQL, MySQL
-- SQL Server:
IF OBJECT_ID('procedure_name', 'P') IS NOT NULL
    DROP PROCEDURE procedure_name;

Viewing Existing Procedures

-- PostgreSQL
SELECT routine_name, routine_type
FROM information_schema.routines
WHERE routine_schema = 'public' AND routine_type = 'PROCEDURE';

-- View the source code
SELECT prosrc FROM pg_proc WHERE proname = 'transfer_funds';

-- MySQL
SHOW PROCEDURE STATUS WHERE Db = 'your_database';
SHOW CREATE PROCEDURE transfer_funds;

-- SQL Server
SELECT name, type_desc FROM sys.procedures;
EXEC sp_helptext 'transfer_funds';

Calling Procedures from Application Code

Python (PostgreSQL with psycopg2)

import psycopg2

conn = psycopg2.connect("dbname=mydb")
cur = conn.cursor()

cur.execute("CALL place_order(%s, %s, %s, NULL, NULL)", (1, 101, 3))
result = cur.fetchone()
order_id, status = result[0], result[1]
print(f"Order ID: {order_id}, Status: {status}")

conn.commit()
conn.close()

Python (MySQL with mysql-connector)

import mysql.connector

conn = mysql.connector.connect(database='mydb')
cur = conn.cursor()

args = (1, 101, 3, 0, '')
result = cur.callproc('place_order', args)
order_id = result[3]  # p_order_id
status = result[4]    # p_status
print(f"Order ID: {order_id}, Status: {status}")

conn.commit()
conn.close()

Node.js (PostgreSQL with pg)

const { Pool } = require('pg');
const pool = new Pool();

async function placeOrder(customerId, productId, quantity) {
    const client = await pool.connect();
    try {
        const result = await client.query(
            'CALL place_order($1, $2, $3, NULL, NULL)',
            [customerId, productId, quantity]
        );
        const { p_order_id, p_status } = result.rows[0];
        console.log(`Order ID: ${p_order_id}, Status: ${p_status}`);
        return { orderId: p_order_id, status: p_status };
    } finally {
        client.release();
    }
}

Java (JDBC)

try (CallableStatement stmt = conn.prepareCall("{CALL place_order(?, ?, ?, ?, ?)}")) {
    stmt.setInt(1, 1);       // customer_id
    stmt.setInt(2, 101);     // product_id
    stmt.setInt(3, 3);       // quantity
    stmt.registerOutParameter(4, Types.INTEGER);  // order_id OUT
    stmt.registerOutParameter(5, Types.VARCHAR);  // status OUT

    stmt.execute();

    int orderId = stmt.getInt(4);
    String status = stmt.getString(5);
    System.out.println("Order: " + orderId + ", Status: " + status);
}

Common Mistakes to Avoid

Mistake 1: Missing Transaction Handling

-- BAD: No transaction wrapping in a multi-statement procedure
CREATE PROCEDURE bad_transfer (IN p_from INT, IN p_to INT, IN p_amount DECIMAL(10,2))
BEGIN
    UPDATE accounts SET balance = balance - p_amount WHERE id = p_from;
    -- If this next statement fails, the debit above is already committed!
    UPDATE accounts SET balance = balance + p_amount WHERE id = p_to;
END;

Fix: Always wrap multi-statement operations in explicit transactions with error handling:

-- GOOD: Transaction with rollback on error
BEGIN
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
    END;

    START TRANSACTION;
    UPDATE accounts SET balance = balance - p_amount WHERE id = p_from;
    UPDATE accounts SET balance = balance + p_amount WHERE id = p_to;
    COMMIT;
END;

Mistake 2: Putting Too Much Business Logic in Procedures

-- BAD: Entire application logic in a stored procedure
CREATE PROCEDURE process_checkout (...)
BEGIN
    -- Validate user session
    -- Check cart items
    -- Calculate taxes for 50 different jurisdictions
    -- Apply 12 different coupon types
    -- Process payment via external API
    -- Generate PDF receipt
    -- Send email notification
    -- Update analytics dashboards
    -- ... 500 lines of T-SQL
END;

This creates a procedure that is impossible to test, debug, and maintain. Stored procedures should handle data operations, not entire application workflows.

Fix: Keep procedures focused on data operations. Handle business logic, API calls, and presentation in application code:

-- GOOD: Focused on data operations only
CREATE PROCEDURE reserve_inventory (
    IN p_product_id INT,
    IN p_quantity INT,
    OUT p_success BOOLEAN
)
-- Just checks and deducts inventory. Nothing else.

Mistake 3: Not Validating Inputs

-- BAD: No input validation
CREATE PROCEDURE withdraw (IN p_id INT, IN p_amount DECIMAL)
BEGIN
    UPDATE accounts SET balance = balance - p_amount WHERE id = p_id;
    -- What if p_amount is negative? This would ADD money!
    -- What if p_id doesn't exist? Silent no-op!
END;

Fix: Always validate inputs at the start of the procedure:

CREATE PROCEDURE withdraw (IN p_id INT, IN p_amount DECIMAL)
BEGIN
    IF p_amount <= 0 THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Amount must be positive';
    END IF;

    IF NOT EXISTS (SELECT 1 FROM accounts WHERE id = p_id) THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Account not found';
    END IF;

    UPDATE accounts SET balance = balance - p_amount WHERE id = p_id;
END;

Mistake 4: Forgetting DELIMITER in MySQL

-- BAD: MySQL interprets the first ; as end of statement
CREATE PROCEDURE my_proc()
BEGIN
    SELECT 1;   -- MySQL thinks the CREATE ends here!
    SELECT 2;
END;
-- ERROR: You have an error in your SQL syntax

-- GOOD: Use DELIMITER
DELIMITER //
CREATE PROCEDURE my_proc()
BEGIN
    SELECT 1;
    SELECT 2;
END //
DELIMITER ;

Stored Procedures vs Alternatives

Approach	Best For	Limitations
Stored Procedure	Multi-step data operations, security, performance-critical paths	Database-specific syntax, harder to version control
Application Code + SQL	Complex business logic, external integrations	Multiple round-trips, logic spread across layers
ORM	Simple CRUD, rapid development	Performance overhead, limited control
Database Function	Computations, data transformations used in queries	Cannot manage transactions (PostgreSQL), single return value
Trigger	Automatic reactions to data changes	Hidden logic, can cause unexpected side effects

Summary

SQL stored procedures are named, reusable programs that execute inside the database server:

CREATE PROCEDURE defines a stored procedure. Syntax varies across MySQL (uses DELIMITER, BEGIN...END), PostgreSQL (uses LANGUAGE plpgsql, $$ ... $$), and SQL Server (uses T-SQL, @ parameters).
Parameters come in three modes: IN (input, read-only), OUT (output, procedure sets the value), and INOUT (bidirectional, caller provides initial value, procedure can modify it).
Calling procedures uses CALL procedure_name(args) in MySQL and PostgreSQL, and EXEC procedure_name args in SQL Server.
Control flow (IF/ELSE, WHILE, FOR) enables conditional logic and iteration within procedures.
Error handling uses DECLARE HANDLER (MySQL), EXCEPTION blocks (PostgreSQL), or TRY/CATCH (SQL Server) to catch errors and roll back gracefully.
Always wrap multi-statement operations in transactions with proper error handling inside procedures.
Keep procedures focused on data operations. Handle complex business logic, external API calls, and presentation in application code.
Validate all inputs at the start of every procedure.