SQL Stored Procedures

A stored procedure is a named block of SQL statements kept in the database and run on request, with CALL in PostgreSQL and MySQL or EXEC in SQL Server. It takes parameters, can run several statements in order, can change data, and in most engines can use variables, loops, and error handling.

The point is to put a whole operation ("ship this order") behind one name, so every application performs it the same way.

Procedure or function

Most databases have both, and they are easy to confuse. A function computes a value and is used inside a query; a procedure performs an action and is called on its own.

How other databases write one

A procedure that marks an order shipped and logs the change, in PostgreSQL:

create procedure ship_order(p_order_id integer)
language plpgsql
as $$
begin
  insert into order_status_log (order_id, old_status, new_status)
  select order_id, status, 'shipped'
  from orders
  where order_id = p_order_id;

  update orders
  set status = 'shipped'
  where order_id = p_order_id;
end;
$$;

call ship_order(4);

The same in SQL Server, where parameters start with @:

create procedure dbo.ship_order @order_id int
as
begin
  set nocount on;

  insert into order_status_log (order_id, old_status, new_status)
  select order_id, status, 'shipped'
  from orders
  where order_id = @order_id;

  update orders
  set status = 'shipped'
  where order_id = @order_id;
end;

exec dbo.ship_order @order_id = 4;

Why teams use them, and why some avoid them

For: one definition of an operation shared by every application; fewer round trips, since several statements run inside the database; and permissions granted on the procedure instead of the tables, so a user can ship an order without being able to update orders freely.

Against: the logic lives outside the application's code, version control, and tests unless you put it there deliberately; procedural SQL dialects are the least portable part of any database; and, like triggers, a procedure can change several tables behind one innocent-looking name.

DuckDB: no procedures

create procedure ship_order(p_order_id integer)
language sql
as $$ update orders set status = 'shipped' where order_id = p_order_id $$

It fails with Parser Error: syntax error at or near "procedure". DuckDB does have CALL, but only for built-in table functions such as call pragma_table_info('orders'); call ship_order(13) fails with Catalog Error: Table Function with name ship_order does not exist! DuckDB is usually embedded in a program or run from a script, and the reusable parts of a procedure map onto three things.

Reusable calculations: macros

In DuckDB, CREATE FUNCTION is another name for CREATE MACRO. A scalar macro returns a value:

create function order_total(oid) as (
    select sum(quantity * unit_price)
    from order_items
    where order_id = oid
);

select order_id, order_total(order_id) as total
from orders
where customer_id = 1
order by order_id

Customer 1's three orders come to 54, 44, and 33.5. A table macro returns rows, which covers procedures whose only job is to run a query with parameters:

create function orders_for(cid) as table
select order_id, order_date, status
from orders
where customer_id = cid;

select *
from orders_for(1)
order by order_id

duckdb_functions() lists them with a function_type of macro and table_macro. A macro body is one expression or one query, never an INSERT or UPDATE: create macro ... as table update ... is a Parser Error. See macros for parameters, defaults, and overloads.

A data change with parameters: a prepared statement

PREPARE names a statement with numbered placeholders, and EXECUTE runs it with values. Unlike a macro, it can change data:

prepare ship_order as
update orders
set status = 'shipped'
where order_id = $1;

execute ship_order(13);

select order_id, status
from orders
where order_id = 13

Order 13 was pending and is now shipped. A prepared statement lasts only as long as the connection that prepared it, and deallocate ship_order removes it sooner. In the SQLShed playground every Run opens a new connection, so prepare and execute in the same Run.

Several statements as one operation: a script

The procedure's real body (log the change, then make it, all or nothing) becomes a script with an explicit transaction. The triggers page builds exactly that for order 4, and the rerunnable scripts lesson shows how to write a script that is safe to run twice. In an application, the script lives in a function in your own code that runs the statements on one connection.

What can go wrong

• Porting a procedure as a macro. A macro cannot change data, loop, or hold variables. Anything a procedure did beyond a single query has to move into a script or the application.
• A prepared statement that is gone. Executing one from a different connection, or after deallocate, fails with Binder Error: Prepared statement "ship_order" does not exist.
• Copying MySQL procedure code with its delimiter. delimiter // is a command for the MySQL client, not SQL. Pasted into another engine it is a syntax error.
• A procedure name that hides a data change. Wherever the operation lives, name it for what it changes (ship_order, not process_order), and keep its statements where someone reviewing the data can find them.