Stored procedure

What is a stored procedure?

A stored procedure is a named routine of SQL statements that lives inside the database. You call it by name, pass a few parameters, and the database runs the whole sequence as one unit. The logic sits next to the data instead of in your application code.

A procedure can read rows, insert or update them, fill temporary tables, run checks, and wrap several statements in a single transaction. Anything you can write in SQL, you can package as a procedure and reuse.

Most databases compile the procedure the first time it runs and cache the resulting execution plan, so later calls skip the planning step and start faster.

Here is a small procedure that records a payment and adjusts the invoice balance in one transaction:

CREATE PROCEDURE record_payment
  @invoice_id INT,
  @amount     DECIMAL(10,2)
AS
BEGIN
  BEGIN TRANSACTION;
    INSERT INTO payments (invoice_id, amount, paid_at)
    VALUES (@invoice_id, @amount, GETDATE());

    UPDATE invoices
    SET balance = balance - @amount
    WHERE id = @invoice_id;
  COMMIT;
END;

You run it with a single call, for example EXEC record_payment @invoice_id = 4021, @amount = 150.00. The two writes either both succeed or both roll back.

Parameters and how you call it

Parameters are how a procedure stays general. Instead of hard-coding an invoice number, you pass it in at call time. The three standard modes are the same across most engines:

• IN passes a value into the procedure. This is the default in PostgreSQL and MySQL, and the mode you reach for most.

• OUT hands a value back to the caller. The procedure sets it, and the caller reads it once the call returns.

• INOUT does both: the caller supplies a starting value and reads the modified value afterwards.

You invoke a procedure with CALL in PostgreSQL and MySQL, and with EXEC (or EXECUTE) in SQL Server. A SQL Server procedure can also return an integer status code to signal success or failure, on top of any output parameters.

How a procedure differs from a function

People mix up procedures and SQL functions, but they are built for different jobs. The split is clearest in PostgreSQL.

A function computes and returns a value, and you use it inside a query: SELECT tax(amount) FROM orders. Because it runs as part of that query, a function cannot control transactions. It is not allowed to commit or roll back.

A procedure is the opposite. It does not have to return anything, you invoke it on its own with CALL or EXEC, and it can manage transactions directly, running COMMIT and ROLLBACK between statements. That makes a procedure the right tool for multi-step work that has to land atomically, like the payment example above. A function is the right tool for a value you want to reuse inside queries.

Plan caching and parameter sniffing

The cached execution plan that makes procedures fast can also make them unpredictable. When the database compiles a procedure, it looks at the parameter values from that first call to estimate how many rows each step will touch, then builds a plan around those estimates. SQL Server calls this parameter sniffing.

The trouble starts when the first call is not representative. Suppose a reporting procedure takes a @customer_id, and the first person to run it passes an id that matches only a handful of rows. The optimizer picks an index seek and caches it. The next caller passes an id that matches millions of rows, where a scan would be far cheaper, but the procedure reuses the cached seek plan and crawls.

This is a real production problem, not a theoretical one: the same procedure runs fast for one input and slow for another, with no code change in between. Fixes depend on the engine. In SQL Server you can force a fresh plan per call with OPTION (RECOMPILE), or rely on its newer parameter sensitive plan optimization. The thing to remember is that a stored procedure hides an execution plan you never see, and that plan was shaped by whichever values happened to arrive first.

Where stored procedure logic belongs today

Procedures buy you two real things. First, fewer round trips: the client sends one call instead of a chatty back-and-forth of statements, so only the call crosses the network. Second, a central permission boundary: callers can run the procedure without direct rights on the underlying tables, which is why banks and ERP systems have relied on them for decades.

The cost is that business logic ends up hidden in the database, where it is harder to version-control, review, and test than application code. A transformation buried in a procedure does not show up in a pull request unless someone exports it on purpose.

This is why much of that transformation work has moved out into tools like dbt, into ETL and ELT jobs, and into a data pipeline managed by a data orchestration layer. Those keep the SQL in files you can diff, test, and deploy like any other code. Procedures still earn their place for logic that has to run close to the data, for atomic multi-step writes, and as a controlled entry point in systems where direct table access is not an option. When you do keep one, put its definition in version control and log every run with its parameters, so the logic stays reviewable instead of disappearing into the database.