STORED PROCEDURE

Programmability
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

Filter by Database
SQL STORED PROCEDURE Compatibility Across Databases
Database System Support Status Since Version Notes
MySQL Native 5.0 CREATE PROCEDURE with BEGIN...END. Supports IN, OUT, INOUT parameters. Called with CALL proc_name(...).
PostgreSQL Native 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 Native all CREATE PROCEDURE (or PROC). Rich T-SQL with TRY/CATCH, temp tables, and dynamic SQL. Called with EXEC or EXECUTE.
Oracle Native 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: Native since 5.0 PostgreSQL: Native since 11 SQL Server: Native in all listed versions Oracle: Native in all listed versions SQLite: Not supported

Per-Database Syntax & Notes

MySQL Native syntax

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 Native syntax

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 Native syntax

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 Native syntax

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 Alternative syntax

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

-- Not supported.