STORED PROCEDUREProgrammability
Buy Me A Coffee

A named, reusable block of SQL and procedural logic stored in the database.

Pre-compiled SQL code stored in the database and callable by name. Can accept parameters, contain logic, and modify data. Useful for encapsulating business logic and reducing network round-trips. Database-specific implementations limit portability.

Compatibility

Show:
Database System Support Status Since Version Notes
MySQL ✓ Supported 5.0 CREATE PROCEDURE with BEGIN...END. Supports IN, OUT, INOUT parameters. Called with CALL proc_name(...).
PostgreSQL ✓ Supported 11 CREATE PROCEDURE added in PG 11. Key distinction from functions: procedures can COMMIT and ROLLBACK within their body. Called with CALL. Before PG 11, use CREATE FUNCTION with VOID return type as a workaround.
SQL Server ✓ Supported all CREATE PROCEDURE (or PROC). Rich T-SQL with TRY/CATCH, temp tables, and dynamic SQL. Called with EXEC or EXECUTE.
Oracle ✓ Supported all CREATE PROCEDURE using PL/SQL. Called with EXEC or CALL. Rich exception handling with EXCEPTION...WHEN blocks.
SQLite ✗ Not Supported — No stored procedures. All logic must live in the application. Triggers can handle some reactive logic.

Details

PostgreSQL's CREATE PROCEDURE (PG 11+) is distinct from CREATE FUNCTION — procedures can manage transactions (COMMIT/ROLLBACK inside the body). SQLite has no server-side procedural code.

Standard Syntax

-- MySQL / SQL Server style: CREATE PROCEDURE proc_name(IN param1 INT) BEGIN SELECT * FROM tbl WHERE id = param1; END;

Version Support

MySQL: Since 5.0 PostgreSQL: Since 11 SQL Server: Since all Oracle: Since all SQLite: Not supported

Per-Database Syntax & Notes

MySQL

CREATE PROCEDURE with BEGIN...END. Supports IN, OUT, INOUT parameters. Called with CALL proc_name(...).

CREATE PROCEDURE get_user(IN uid INT) BEGIN SELECT * FROM users WHERE id = uid; END; CALL get_user(42);

PostgreSQL

CREATE PROCEDURE added in PG 11. Key distinction from functions: procedures can COMMIT and ROLLBACK within their body. Called with CALL. Before PG 11, use CREATE FUNCTION with VOID return type as a workaround.

CREATE PROCEDURE update_balance(uid INT, amount NUMERIC) LANGUAGE plpgsql AS $$ BEGIN UPDATE accounts SET balance = balance + amount WHERE id = uid; COMMIT; END; $$; CALL update_balance(1, 100.00);

SQL Server

CREATE PROCEDURE (or PROC). Rich T-SQL with TRY/CATCH, temp tables, and dynamic SQL. Called with EXEC or EXECUTE.

CREATE PROCEDURE get_user @uid INT AS BEGIN SELECT * FROM users WHERE id = @uid; END; EXEC get_user 42;

Oracle

CREATE PROCEDURE using PL/SQL. Called with EXEC or CALL. Rich exception handling with EXCEPTION...WHEN blocks.

CREATE PROCEDURE get_user(uid IN NUMBER) AS BEGIN SELECT * FROM users WHERE id = uid; EXCEPTION WHEN OTHERS THEN NULL; END; EXEC get_user(42);

SQLite

No stored procedures. All logic must live in the application. Triggers can handle some reactive logic.

-- Not supported.