Stored Procedures
Write reusable server-side logic with stored procedures and functions.
What you'll learn
- What stored procedures are and when to use them
- Creating procedures with parameters and control flow
- Stored functions vs stored procedures
- Benefits: performance, security, and encapsulation
- Drawbacks: portability and testability concerns
Concept
What Is a Stored Procedure?
A stored procedure is a named block of SQL (and sometimes procedural language extensions like PL/pgSQL, T-SQL, or PL/SQL) stored in the database. You call it by name, optionally passing parameters. The procedure runs on the database server, can contain loops, conditionals, and error handling, and can perform multiple operations in one call.
Creating a Procedure
Procedures are defined with CREATE PROCEDURE (or CREATE FUNCTION for a function that returns a value). They accept input parameters, can return result sets or output parameters, and use procedural constructs like IF, WHILE, LOOP, and EXCEPTION blocks. The exact syntax varies by database — PostgreSQL uses PL/pgSQL, MySQL uses its own procedural SQL, SQL Server uses T-SQL.
Functions vs Procedures
A stored function returns a single value and can be used in a SELECT statement (SELECT calculate_tax(price) FROM products). A stored procedure does not return a value inline — it can return result sets via OUT parameters or a SELECT inside the procedure, and is called with CALL. Functions are more restrictive (no transaction control); procedures are more powerful.
Benefits
- Performance — the procedure is parsed and planned once, then reused. It avoids sending multiple round-trips from the application.
- Security — you can grant execute access to a procedure without granting access to the underlying tables. The procedure acts as a controlled API.
- Encapsulation — complex business logic lives in one place, callable from any application.
Drawbacks
- Portability — procedural SQL is database-specific. Moving from PostgreSQL to MySQL means rewriting procedures.
- Testability — procedures are harder to unit test than application code. Debugging requires database-specific tools.
- Scalability — heavy logic in the database can become a bottleneck. Modern practice often prefers keeping logic in the application layer.
Example
-- PostgreSQL: a stored function to calculate discounted price
CREATE OR REPLACE FUNCTION discounted_price(
original_price DECIMAL(10, 2),
discount_percent DECIMAL(5, 2)
) RETURNS DECIMAL(10, 2) AS $$
BEGIN
RETURN original_price * (1 - discount_percent / 100);
END;
$$ LANGUAGE plpgsql;
-- Use the function in a query
SELECT name, price, discounted_price(price, 10) AS price_with_10pct_off
FROM products WHERE in_stock = TRUE;
-- PostgreSQL: a stored procedure to place an order
CREATE OR REPLACE PROCEDURE place_order(
p_user_id INTEGER,
p_product_id INTEGER,
p_quantity INTEGER
) AS $$
DECLARE
v_price DECIMAL(10, 2);
v_total DECIMAL(10, 2);
BEGIN
SELECT price INTO v_price FROM products WHERE id = p_product_id;
v_total := v_price * p_quantity;
INSERT INTO orders (user_id, total) VALUES (p_user_id, v_total);
INSERT INTO order_items (order_id, product_id, quantity)
VALUES (currval('orders_id_seq'), p_product_id, p_quantity);
END;
$$ LANGUAGE plpgsql;
-- Call the procedure
CALL place_order(1, 42, 3);
A function that computes discounted price (usable in SELECT), and a procedure that places an order by inserting into two tables atomically.
Try it
- SQL Formatter
Format stored procedure and function definitions.
- Code Diff
Compare procedure versions when updating logic.
Common mistakes
The mistake
Putting all business logic in stored procedures
The fix
Procedures are hard to test, version, and port. Keep them for data-intensive operations that benefit from running close to the data. Put most logic in the application layer.
The mistake
Not handling errors inside procedures
The fix
Use EXCEPTION blocks (PL/pgSQL) or DECLARE HANDLER (MySQL) to catch and handle errors. An unhandled exception aborts the procedure and propagates to the caller.
Look up unfamiliar terms
A beginner-friendly glossary explains jargon in plain English — variables, functions, DOM, Promise, and more.
Build real projects
Apply what you learned by building guided projects with starter code and solutions.
Decode error messages
Plain-English explanations of common errors — what they mean, why they happen, and how to fix them.