SQL Stored Procedures Explained Simply (SQL Server, MySQL and PostgreSQL)

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.

Diagram of an application calling a stored procedure that runs several steps inside the database

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?

BenefitWhat it means in practice
Reusable logicWrite the business rule once, call it from every application
SecurityUsers can run the procedure without having direct access to the tables
Fewer round tripsOne call runs many statements on the server instead of sending each one over the network
Protection from SQL injectionParameters are passed as values, not glued into SQL text
Easier changesFix 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

ViewStored procedureFunction
What it isA saved SELECTA saved block of statementsA saved calculation that returns a value or table
Takes parameters?NoYesYes
Can change data?Usually notYesUsually not
Used inside a SELECT?Yes, like a tableNo, you run it with EXEC or CALLYes
Best forSimplifying and securing readsMulti-step jobs and business rulesReusable 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

DatabaseCreate or update a procedure in one script
SQL Server 2016 SP1+CREATE OR ALTER PROCEDURE ...
MySQL / MariaDBDROP PROCEDURE IF EXISTS ...; then CREATE PROCEDURE ...
PostgreSQLCREATE 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 TransferMoney or GetCustomerOrders.
  • 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

Scroll to Top