SQL guide · Tables, views & databases (DDL)

SQL CREATE PROCEDURE: Write and Run Stored Procedures

A stored procedure is a named block of SQL that is saved in the database and can be run on demand. You define it once with CREATE PROCEDURE, optionally giving it parameters, and then run it with EXEC (SQL Server) or CALL (MySQL, PostgreSQL, Oracle, and others). The code runs on the database server, close to the data.

Procedures are used to bundle several statements into one operation, to apply the same business rule from many applications, and to limit what callers can do. For instance, an application account can be given permission to run a procedure that inserts an order, without having direct write access to the orders table. Procedures can contain variables, conditions, loops, error handling and transactions, which makes them more capable than a single query.

Do not confuse procedures with functions. A procedure is run as its own statement and may modify data, return several result sets or output parameters. A function returns a value and is used inside queries. The procedural language also differs: SQL Server uses T-SQL, MySQL uses its own stored program syntax, PostgreSQL uses PL/pgSQL (or other languages), and Oracle uses PL/SQL, so procedure bodies are rarely portable.

Basic syntax

-- SQL Server (T-SQL)
CREATE PROCEDURE procedure_name
    @param1 datatype [= default],
    @param2 datatype
AS
BEGIN
    sql_statements;
END;

EXEC procedure_name @param1 = value1, @param2 = value2;

-- MySQL / PostgreSQL 11+
CREATE PROCEDURE procedure_name (IN param1 datatype, IN param2 datatype)
...body...;

CALL procedure_name(value1, value2);

Parameter names in SQL Server start with @ and can have defaults; in MySQL each parameter is marked IN, OUT or INOUT. The body goes between BEGIN and END in T-SQL and MySQL, and between dollar-quoted markers in PostgreSQL. Run the procedure with EXEC (or EXECUTE) in SQL Server and with CALL in MySQL, PostgreSQL 11 or later, and Oracle.

SQL CREATE PROCEDURE examples

A procedure with no parameters (SQL Server)

This saves a frequently used report query under a name. Calling it with EXEC runs the SELECT and returns its result set to the caller.

CREATE PROCEDURE GetActiveCustomers
AS
BEGIN
    SELECT customer_id, name, email
    FROM customers
    WHERE status = 'active';
END;

EXEC GetActiveCustomers;

A procedure with parameters and a default

Parameters make a procedure reusable. Here the city is required and the minimum order total has a default of zero. You can pass arguments by name, which keeps calls readable and lets you skip optional ones.

CREATE PROCEDURE GetOrdersByCity
    @city NVARCHAR(50),
    @min_total DECIMAL(10,2) = 0
AS
BEGIN
    SELECT o.order_id, c.name, o.total
    FROM orders o
    JOIN customers c ON c.customer_id = o.customer_id
    WHERE c.city = @city
      AND o.total >= @min_total;
END;

EXEC GetOrdersByCity @city = 'Austin', @min_total = 100;

A procedure that changes data (MySQL)

Procedures can modify data and use transactions. This one moves an order to a new status. In the mysql client you change the statement delimiter so the semicolons inside the body do not end the CREATE statement early. Run it with CALL.

DELIMITER //
CREATE PROCEDURE UpdateOrderStatus (IN p_order_id INT, IN p_status VARCHAR(20))
BEGIN
    UPDATE orders
    SET status = p_status
    WHERE order_id = p_order_id;
END //
DELIMITER ;

CALL UpdateOrderStatus(5021, 'shipped');

A procedure in PostgreSQL

PostgreSQL added real procedures in version 11. The body is written in PL/pgSQL inside dollar quotes, and the procedure is run with CALL. Unlike functions, procedures can commit or roll back a transaction themselves.

CREATE OR REPLACE PROCEDURE archive_old_orders(cutoff DATE)
LANGUAGE plpgsql
AS $$
BEGIN
    INSERT INTO orders_archive
    SELECT * FROM orders WHERE order_date < cutoff;

    DELETE FROM orders WHERE order_date < cutoff;
END;
$$;

CALL archive_old_orders('2024-01-01');

Where SQL dialects differ

SQL Server: Use CREATE PROCEDURE (or CREATE PROC) and run it with EXEC or EXECUTE. CREATE OR ALTER PROCEDURE is available from SQL Server 2016 SP1. Parameters start with @, and OUTPUT marks output parameters. SET NOCOUNT ON at the top of the body reduces extra messages. CREATE PROCEDURE must be the first statement in a batch.

MySQL: Use CREATE PROCEDURE with IN, OUT and INOUT parameters and run it with CALL. In the command-line client, change DELIMITER while defining a body with several statements. CREATE OR REPLACE PROCEDURE is not supported by MySQL itself (MariaDB supports it), so drop and recreate.

PostgreSQL: CREATE PROCEDURE was added in PostgreSQL 11 and is run with CALL. Before that, only functions existed. CREATE OR REPLACE PROCEDURE is supported. Procedures can control transactions, which functions cannot.

SQLite: SQLite does not support stored procedures. Put the logic in application code, use triggers for automatic actions, or use views for reusable queries.

Oracle: Uses CREATE [OR REPLACE] PROCEDURE with PL/SQL and an IS or AS block. Run it from SQL with CALL or EXEC procedure_name(args), or from PL/SQL by naming it directly.

Common mistakes

Confusing EXEC and CALL. SQL Server uses EXEC, while MySQL, PostgreSQL and Oracle use CALL, and running the wrong one produces a syntax error.
Forgetting to change the delimiter in the MySQL command-line client, so the first semicolon in the body ends the statement and the definition breaks.
Name collisions between parameters and columns. A parameter called status inside a query on a column called status can lead to ambiguous or always-true comparisons, so use a prefix like p_ or @.
Building SQL strings by concatenating parameters. This invites SQL injection, so use parameterised dynamic SQL such as sp_executesql in SQL Server or EXECUTE ... USING in PostgreSQL.
Putting heavy business logic in procedures without version control. Store the CREATE script in your repository so changes are reviewed and deployments are repeatable.

Related SQL guides

Browse all SQL guides

Need help writing the SQL?

Describe your tables and the result you want, then review the generated query against your database before running it.

Try the AI SQL generator