A view lets you save a single query. But what if the job takes several steps? Check a balance, start a transaction, update two tables, write a log entry, return a message. You don’t want every application repeating all that logic. You want to save it once, in the database, and call it by name.
That’s what a stored procedure is. I’ve used them for years in business systems, and when they’re used well they make applications simpler, safer and easier to maintain.

Table of Contents
What Is a Stored Procedure?
A stored procedure is a named block of SQL code saved inside the database. It can take input parameters, run as many statements as it needs (queries, inserts, updates, IF logic, loops, transactions) and return results or values. Applications run it with a single call instead of sending all those statements themselves.
Your First Stored Procedure
The syntax differs between databases more than most SQL does, so here are the big three. Each example creates a procedure that returns a customer’s orders.
SQL Server
CREATE PROCEDURE GetCustomerOrders
@CustomerId INT
AS
BEGIN
SET NOCOUNT ON;
SELECT order_id, order_date, amount
FROM orders
WHERE customer_id = @CustomerId
ORDER BY order_date DESC;
END;
Run it with EXEC:
EXEC GetCustomerOrders @CustomerId = 1;
SET NOCOUNT ON stops SQL Server sending “rows affected” messages for every statement. It’s a small habit that keeps things tidy, especially when the procedure is called from an application.
MySQL
DELIMITER //
CREATE PROCEDURE GetCustomerOrders(IN p_customer_id INT)
BEGIN
SELECT order_id, order_date, amount
FROM orders
WHERE customer_id = p_customer_id
ORDER BY order_date DESC;
END //
DELIMITER ;
CALL GetCustomerOrders(1);
The DELIMITER lines are there because the procedure body contains semicolons. They tell the MySQL client not to stop at the first ;.
PostgreSQL
PostgreSQL has CREATE PROCEDURE, but for returning rows people usually write a function instead:
CREATE FUNCTION get_customer_orders(p_customer_id INT)
RETURNS TABLE (order_id INT, order_date DATE, amount NUMERIC)
LANGUAGE sql
AS $$
SELECT o.order_id, o.order_date, o.amount
FROM orders o
WHERE o.customer_id = p_customer_id
ORDER BY o.order_date DESC;
$$;
SELECT * FROM get_customer_orders(1);
In PostgreSQL, procedures (called with CALL) are mainly used for jobs that change data and need to control transactions.
A More Realistic Example
Procedures really shine when there are several steps. Here’s a SQL Server money transfer that combines parameters, a check, and the transaction pattern:
CREATE PROCEDURE TransferMoney
@FromAccount INT,
@ToAccount INT,
@Amount DECIMAL(12,2)
AS
BEGIN
SET NOCOUNT ON;
IF @Amount <= 0
THROW 50001, 'Amount must be greater than zero.', 1;
IF (SELECT balance FROM accounts WHERE account_id = @FromAccount) < @Amount
THROW 50002, 'Not enough balance.', 1;
BEGIN TRY
BEGIN TRANSACTION;
UPDATE accounts SET balance = balance - @Amount WHERE account_id = @FromAccount;
UPDATE accounts SET balance = balance + @Amount WHERE account_id = @ToAccount;
INSERT INTO transfer_log (from_account, to_account, amount, created_at)
VALUES (@FromAccount, @ToAccount, @Amount, SYSDATETIME());
COMMIT;
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0 ROLLBACK;
THROW;
END CATCH;
END;
The application only needs one line:
EXEC TransferMoney @FromAccount = 1, @ToAccount = 2, @Amount = 100;
All the rules live in one place. A web app, a desktop app and a nightly job can all call the same procedure and get exactly the same behaviour.
The Hidden Race Condition in the Transfer Example
The transfer procedure above looks safe, but it has a gap that shows up under real load. The balance check runs before the transaction starts. If two transfers from the same account run at the same moment, both can read a balance of 150, both pass the “enough balance?” check for 100, and both subtract it. The account ends at −50.
The fix is to make the check and the change one step. Subtract only if the money is there, then look at how many rows were changed:
CREATE OR ALTER PROCEDURE TransferMoney
@FromAccount INT,
@ToAccount INT,
@Amount DECIMAL(12,2)
AS
BEGIN
SET NOCOUNT ON;
SET XACT_ABORT ON;
IF @Amount <= 0
THROW 50001, 'Amount must be greater than zero.', 1;
BEGIN TRY
BEGIN TRANSACTION;
UPDATE accounts
SET balance = balance - @Amount
WHERE account_id = @FromAccount
AND balance >= @Amount; -- check and change in one statement
IF @@ROWCOUNT = 0
THROW 50002, 'Not enough balance.', 1;
UPDATE accounts SET balance = balance + @Amount WHERE account_id = @ToAccount;
INSERT INTO transfer_log (from_account, to_account, amount, created_at)
VALUES (@FromAccount, @ToAccount, @Amount, SYSDATETIME());
COMMIT;
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0 ROLLBACK;
THROW;
END CATCH;
END;
The UPDATE locks the row while it checks the balance, so a second transfer has to wait and then sees the new balance. If it’s too low, @@ROWCOUNT is 0 and the procedure stops with a clear error. SET XACT_ABORT ON makes sure any unexpected error also rolls back the whole transaction. The same pattern works in MySQL (ROW_COUNT()) and PostgreSQL (IF NOT FOUND). More on why this matters: SQL Transactions and ACID Explained Simply.
Output Parameters
Besides returning rows, a procedure can hand back single values through output parameters. In SQL Server:
CREATE PROCEDURE GetCustomerTotal
@CustomerId INT,
@Total DECIMAL(12,2) OUTPUT
AS
BEGIN
SELECT @Total = SUM(amount)
FROM orders
WHERE customer_id = @CustomerId;
END;
-- Calling it
DECLARE @t DECIMAL(12,2);
EXEC GetCustomerTotal @CustomerId = 1, @Total = @t OUTPUT;
SELECT @t AS customer_total;
Why Use Stored Procedures?
| Benefit | What it means in practice |
|---|---|
| Reusable logic | Write the business rule once, call it from every application |
| Security | Users can run the procedure without having direct access to the tables |
| Fewer round trips | One call runs many statements on the server instead of sending each one over the network |
| Protection from SQL injection | Parameters are passed as values, not glued into SQL text |
| Easier changes | Fix a bug in the procedure and every app that calls it is fixed |
The Downsides
Stored procedures aren’t perfect, and it’s worth being honest about that:
- Harder to version and test than normal application code, unless you keep the scripts in source control (you should).
- Less portable. Procedure code written for SQL Server won’t run on MySQL or PostgreSQL without rewriting.
- Business logic gets split between the application and the database if you’re not disciplined about what goes where.
- Debugging is clumsier than in a modern code editor.
My rule of thumb: put data-heavy logic that must stay consistent (transfers, postings, stock movements, big data clean-ups) in procedures, and keep user interface and presentation logic in the application.
Stored Procedures vs. Views vs. Functions
| View | Stored procedure | Function | |
|---|---|---|---|
| What it is | A saved SELECT | A saved block of statements | A saved calculation that returns a value or table |
| Takes parameters? | No | Yes | Yes |
| Can change data? | Usually not | Yes | Usually not |
| Used inside a SELECT? | Yes, like a table | No, you run it with EXEC or CALL | Yes |
| Best for | Simplifying and securing reads | Multi-step jobs and business rules | Reusable calculations |
Stored Procedures in Real Projects
Writing the procedure is half the job. These are the practical details that come up once procedures are part of a real application.
Calling a procedure from your application
// Laravel (SQL Server)
$orders = DB::select('EXEC GetCustomerOrders @CustomerId = ?', [$customerId]);
// Laravel (MySQL)
$orders = DB::select('CALL GetCustomerOrders(?)', [$customerId]);
// Delphi with FireDAC (SQL Server)
FDStoredProc1.StoredProcName := 'TransferMoney';
FDStoredProc1.Prepare;
FDStoredProc1.ParamByName('@FromAccount').AsInteger := 1;
FDStoredProc1.ParamByName('@ToAccount').AsInteger := 2;
FDStoredProc1.ParamByName('@Amount').AsCurrency := 100;
FDStoredProc1.ExecProc;
In every case the values travel as parameters, never as part of the SQL text. With FireDAC on SQL Server, the parameter names keep the @. When the procedure raises an error with THROW, the application receives it as a normal exception: catch it and show the message to the user. See Delphi parameterized queries and Eloquent vs Query Builder vs raw SQL for more.
Give the application EXECUTE, not table access
One of the biggest benefits of procedures is security. The application’s login can be allowed to run procedures without being allowed to read or change tables directly:
-- SQL Server
CREATE ROLE app_role;
GRANT EXECUTE ON SCHEMA::dbo TO app_role; -- run any procedure in dbo
-- no SELECT / UPDATE permission on the tables themselves
This works because of ownership chaining: when a procedure and the tables it uses have the same owner, SQL Server doesn’t check the caller’s permissions on the tables. The user can transfer money through TransferMoney but can’t run UPDATE accounts SET balance = 1000000 from a query window.
Deploy changes safely
| Database | Create or update a procedure in one script |
|---|---|
| SQL Server 2016 SP1+ | CREATE OR ALTER PROCEDURE ... |
| MySQL / MariaDB | DROP PROCEDURE IF EXISTS ...; then CREATE PROCEDURE ... |
| PostgreSQL | CREATE OR REPLACE PROCEDURE / FUNCTION ... |
Keep every procedure as a script file in source control (Git), and deploy from those files rather than editing in the database directly. To see the code that’s actually on a server: EXEC sp_helptext 'TransferMoney' in SQL Server, SHOW CREATE PROCEDURE TransferMoney in MySQL.
Don’t start names with sp_
In SQL Server, the sp_ prefix is reserved for system procedures. SQL Server looks for sp_ procedures in the master database first, which wastes a little time on every call and can run the wrong procedure if Microsoft ever adds one with the same name. Use a plain verb-noun name (TransferMoney) or your own prefix (usp_).
When a procedure gets slow
A procedure that is fast for some customers and slow for others, or fast in SSMS and slow in the application, is usually suffering from parameter sniffing: SQL Server reused a plan built for different parameter values. SQL Server Parameter Sniffing explains how to confirm it and six ways to fix it.
Tips for Writing Good Procedures
- Use clear names that say what the procedure does, like
TransferMoneyorGetCustomerOrders. - Validate parameters at the top and fail early with a clear error message.
- Always handle errors and roll back open transactions.
- Keep them focused. One procedure, one job.
- Look at the execution plan if a procedure gets slow. Parameter sniffing in SQL Server is a classic cause (see SQL Server Parameter Sniffing); for desktop applications, also read this guide to query timeouts.
Frequently Asked Questions
What is the difference between a stored procedure and a function?
A function returns a value or a table and can be used inside a SELECT, like SELECT dbo.GetVat(amount) FROM invoices. It normally can’t change data. A stored procedure is run on its own with EXEC or CALL, can change data, manage transactions and return several result sets. Use functions for reusable calculations and procedures for actions.
Are stored procedures faster than normal queries?
Sometimes, for two reasons: the plan is cached and reused, and one call replaces many round trips between the application and the server. But a parameterized query from the application is cached too, so a slow query doesn’t become fast just by putting it in a procedure. Indexes and the query itself matter much more.
Can a stored procedure return more than one result set?
Yes, in SQL Server and MySQL. Each SELECT in the procedure becomes a separate result set, for example an invoice header and then its lines. In FireDAC, use NextRecordSet to move to the next one; in PHP’s PDO, nextRowset().
Should all database access go through stored procedures?
It’s a matter of team style. Some companies require procedures for everything, for security and a clear interface. Others use an ORM and keep procedures for heavy or critical jobs. A common middle ground: simple reads and edits from the application, and procedures for multi-step business operations such as posting, transfers and month-end jobs.
Conclusion
A stored procedure is a named, reusable block of SQL that lives in the database. It takes parameters, can run many steps and transactions, and gives every application the same trusted logic with a single call.
Start with something small, like a procedure that returns a customer’s orders, and build from there. Once you’ve used one in a real project, you’ll find plenty of places for them.
Related articles
- SQL Triggers Explained Simply: code that runs automatically when data changes.
- SQL Views Explained Simply: saved queries you can use like tables.
- SQL Transactions and ACID Explained Simply: keep multi-step changes safe.
- Why Is My Stored Procedure Slow? How to Find and Fix It in SQL Server: once your procedures are written, this shows how to find and fix the one that is running slowly.