Stored Procedures Cheat Sheet
Stored procedure syntax, parameters, control flow, error handling, and best practices across SQL dialects.
Databases
sql
stored-procedure
database
A stored procedure is a named block of SQL logic stored and executed on the database server. It encapsulates reusable operations, reduces round trips, and centralizes business rules.
Basic syntax (PostgreSQL)
sql
CREATE OR REPLACE FUNCTION add_user(
p_name TEXT,
p_email TEXT
) RETURNS INTEGER AS $$
DECLARE
new_id INTEGER;
BEGIN
INSERT INTO users (name, email) VALUES (p_name, p_email)
RETURNING id INTO new_id;
RETURN new_id;
END;
$$ LANGUAGE plpgsql;
Basic syntax (MySQL)
sql
DELIMITER $$
CREATE PROCEDURE add_user(IN p_name VARCHAR(255), IN p_email VARCHAR(255))
BEGIN
INSERT INTO users (name, email) VALUES (p_name, p_email);
SELECT LAST_INSERT_ID();
END$$
DELIMITER ;
Parameter modes
Table
| Mode | Meaning |
|---|---|
IN | Input only (default). |
OUT | Output only. |
INOUT | Input and output. |
Control flow
sql
IF condition THEN
-- ...
ELSIF other THEN
-- ...
ELSE
-- ...
END IF;
FOR i IN 1..10 LOOP
-- ...
END LOOP;
Error handling
sql
BEGIN
-- risky statement
EXCEPTION
WHEN unique_violation THEN
RAISE NOTICE 'Duplicate key';
END;
Best practices
Table
| Practice | Why |
|---|---|
| Use parameters | Prevent SQL injection. |
Set SET NOCOUNT ON (SQL Server) | Reduce round trips. |
| Keep logic simple | Complex logic is hard to test. |
| Add comments | Document intent. |
| Version with migrations | Track changes in source control. |