SQL Stored Procedures
A stored procedure is a named, reusable block of SQL stored inside the database. You call it like a function.
Create
SQL
-- SQL Server style CREATE PROCEDURE GetActiveCustomers AS BEGIN SELECT * FROM customers WHERE active = 1; END;
Call
SQL
EXEC GetActiveCustomers; -- SQL Server CALL GetActiveCustomers(); -- MySQL / Postgres / MariaDB
Procedure with parameters
SQL
CREATE PROCEDURE GetCustomersByCountry @country VARCHAR(2) AS BEGIN SELECT * FROM customers WHERE country = @country; END; EXEC GetCustomersByCountry @country = 'AU';
Why (and why not)
| Pros | Cons |
|---|---|
| Encapsulates complex logic close to the data. | Logic lives outside your version-controlled app code. |
| One round-trip can run many statements. | Harder to test and refactor. |
| Permission boundary — grant EXEC, not direct SELECT. | Vendor lock-in — every flavour has its own dialect. |
Tip: Modern web apps usually keep business logic in the application layer (Laravel, Django, Rails) and use procedures sparingly — for batch jobs and heavy data crunching where round-trips matter.
Example
Example
CREATE PROCEDURE GetActiveCustomers AS BEGIN SELECT * FROM customers WHERE active = 1; END;Try it Yourself »
Exercise
In MySQL / PostgreSQL, the keyword that invokes a stored procedure is…
GetActiveCustomers();
Four letters.
Discussion
Loading…