SQL 83 🛢️ Stored Procedures Creation and Execution - KandZ

⏱️ Estimated read: 17 min | |

A stored procedure is a named program that lives in the database catalog. It is invoked by name with the CALL statement, it runs as a unit, and it can return values through output parameters, a result set, or a return code. Unlike a user-defined function, a procedure is allowed to modify the database state: it can insert, update, and delete rows, it can start and commit transactions, and it can raise errors. That permission is what makes it the right tool for a business operation that touches several tables, and what makes it the wrong tool for a value the query needs to compute.

Key point: A stored procedure is not a function. It runs with CALL, not in a SELECT list. It can modify data, manage transactions, and return multiple result sets. A procedure is a program; a function is an expression.

Why stored procedures exist

The multi-statement problem. A business operation rarely touches one row in one table. A transfer moves money from one account to another: an update to the source, an update to the destination, an insert into the ledger, and a check that the balance is not negative. The operation must be a single unit, and it must be atomic. A procedure is the unit.

The transaction problem. The operation must run in a transaction, and the transaction must be managed by the code that knows the business rule. A procedure can begin the transaction, commit it, or roll it back. A function cannot, because a function cannot control the transaction.

The security problem. A procedure can be granted the EXECUTE permission without granting the underlying table permissions. The user runs the procedure, and the procedure does the privileged work. The tables remain protected.

The parameter problem. The operation takes parameters and returns values. The procedure declares the input parameters, the output parameters, and the return code. The caller passes the values and reads the results.

The performance problem. The procedure body is stored in the database, and the plan is cached. The repeated calls avoid the parsing and the planning of a large statement. The gain is real for a complex operation that runs frequently.

The encapsulation problem. The business rule is in the procedure, not in the application. When the rule changes, the procedure changes, and the application does not. The application calls the procedure by name.

The audit problem. The procedure call is logged, and the procedure body is the record of the operation. The audit shows what was run, not just which table was touched.

a. Creating a stored procedure

The syntax differs between databases. The common structure is the name, the parameters, the body, and the BEGIN/END block.

The PostgreSQL syntax

PostgreSQL uses the CREATE PROCEDURE statement, which was introduced in version 11. The body is a LANGUAGE plpgsql block.

CREATE OR REPLACE PROCEDURE transfer_funds(
    from_account INT,
    to_account   INT,
    amount       NUMERIC
)
LANGUAGE plpgsql
AS $$
BEGIN
    UPDATE accounts SET balance = balance - amount WHERE id = from_account;
    UPDATE accounts SET balance = balance + amount WHERE id = to_account;
    INSERT INTO ledger (from_id, to_id, amount, created_at)
    VALUES (from_account, to_account, amount, NOW());
END;
$$;

The CALL statement invokes the procedure.

CALL transfer_funds(1, 2, 100.00);

The SQL Server syntax

SQL Server uses CREATE PROCEDURE (or CREATE PROC), and the body is a BEGIN/END block.

CREATE PROCEDURE dbo.TransferFunds
    @FromAccount INT,
    @ToAccount   INT,
    @Amount      DECIMAL(12,2)
AS
BEGIN
    SET NOCOUNT ON;

    UPDATE accounts SET balance = balance - @Amount WHERE id = @FromAccount;
    UPDATE accounts SET balance = balance + @Amount WHERE id = @ToAccount;

    INSERT INTO ledger (from_id, to_id, amount, created_at)
    VALUES (@FromAccount, @ToAccount, @Amount, GETDATE());
END;

The EXEC statement invokes the procedure.

EXEC dbo.TransferFunds @FromAccount = 1, @ToAccount = 2, @Amount = 100.00;

The MySQL syntax

MySQL uses CREATE PROCEDURE, and the body is a BEGIN/END block. The delimiter must be changed when the body contains semicolons.

DELIMITER //

CREATE PROCEDURE transfer_funds(
    IN from_account INT,
    IN to_account   INT,
    IN amount       DECIMAL(12,2)
)
BEGIN
    UPDATE accounts SET balance = balance - amount WHERE id = from_account;
    UPDATE accounts SET balance = balance + amount WHERE id = to_account;
    INSERT INTO ledger (from_id, to_id, amount, created_at)
    VALUES (from_account, to_account, amount, NOW());
END //

DELIMITER ;

The CALL statement invokes the procedure.

CALL transfer_funds(1, 2, 100.00);

The parameter modes

A procedure parameter has a mode: IN, OUT, or INOUT.

Mode	Direction	Purpose
`IN`	Input	The caller passes a value
`OUT`	Output	The procedure sets a value
`INOUT`	Both	The caller passes and the procedure modifies

The output parameter

CREATE OR REPLACE PROCEDURE get_account_balance(
    account_id INT,
    OUT balance NUMERIC
)
LANGUAGE plpgsql
AS $$
BEGIN
    SELECT a.balance INTO balance FROM accounts a WHERE a.id = account_id;
END;
$$;

The caller passes a variable for the output parameter.

DO $$
DECLARE
    result NUMERIC;
BEGIN
    CALL get_account_balance(1, result);
    RAISE NOTICE 'Balance: %', result;
END $$;

The return code

A procedure can return an integer code.

CREATE OR REPLACE PROCEDURE transfer_funds(
    from_account INT,
    to_account   INT,
    amount       NUMERIC,
    OUT status   INT
)
LANGUAGE plpgsql
AS $$
BEGIN
    IF amount <= 0 THEN
        status := 1;
        RETURN;
    END IF;

    UPDATE accounts SET balance = balance - amount WHERE id = from_account;
    UPDATE accounts SET balance = balance + amount WHERE id = to_account;
    status := 0;
END;
$$;

The status is 0 for success and 1 for the invalid amount.

The transaction control

PostgreSQL procedures can commit and roll back. The syntax is COMMIT; and ROLLBACK;.

CREATE OR REPLACE PROCEDURE transfer_funds(
    from_account INT,
    to_account   INT,
    amount       NUMERIC
)
LANGUAGE plpgsql
AS $$
BEGIN
    UPDATE accounts SET balance = balance - amount WHERE id = from_account;

    IF (SELECT balance FROM accounts WHERE id = from_account) < 0 THEN
        ROLLBACK;
        RAISE EXCEPTION 'Insufficient funds';
    END IF;

    UPDATE accounts SET balance = balance + amount WHERE id = to_account;
    COMMIT;
END;
$$;

The procedure starts an implicit transaction and commits it explicitly.

The error handling

PostgreSQL uses the EXCEPTION block. SQL Server uses TRY/CATCH. MySQL uses DECLARE ... HANDLER.

-- PostgreSQL
BEGIN
    UPDATE accounts SET balance = balance - amount WHERE id = from_account;
EXCEPTION
    WHEN OTHERS THEN
        RAISE NOTICE 'Error: %', SQLERRM;
        ROLLBACK;
END;

-- SQL Server
BEGIN TRY
    UPDATE accounts SET balance = balance - amount WHERE id = @FromAccount;
END TRY
BEGIN CATCH
    PRINT ERROR_MESSAGE();
    IF @@TRANCOUNT > 0 ROLLBACK;
END CATCH;

-- MySQL
DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
    ROLLBACK;
    RESIGNAL;
END;

The temporary procedure

A temporary procedure exists only for the session.

-- PostgreSQL
CREATE OR REPLACE PROCEDURE pg_temp.my_proc()
LANGUAGE plpgsql
AS $$ BEGIN ... END; $$;

-- SQL Server
CREATE PROCEDURE #MyProc
AS BEGIN ... END;

The # prefix creates a temporary procedure in SQL Server. In PostgreSQL, the pg_temp schema prefix creates a temporary procedure.

b. Executing a stored procedure

The CALL statement (or EXEC in SQL Server) invokes the procedure. The arguments are passed positionally or by name.

The positional arguments

CALL transfer_funds(1, 2, 100.00);

The arguments are matched to the parameters in order.

The named arguments

CALL transfer_funds(
    from_account => 1,
    to_account   => 2,
    amount       => 100.00
);

The => operator is the named-argument form in PostgreSQL. SQL Server uses @name = value.

EXEC dbo.TransferFunds @FromAccount = 1, @ToAccount = 2, @Amount = 100.00;

The output parameter in PostgreSQL

DO $$
DECLARE
    status INT;
BEGIN
    CALL transfer_funds(1, 2, 100.00, status);
    RAISE NOTICE 'Status: %', status;
END $$;

The output parameter is passed as a variable, and the procedure sets it.

The output parameter in SQL Server

DECLARE @Status INT;
EXEC dbo.TransferFunds
    @FromAccount = 1,
    @ToAccount = 2,
    @Amount = 100.00,
    @Status = @Status OUTPUT;
SELECT @Status;

The OUTPUT keyword is required in SQL Server.

The return code in SQL Server

DECLARE @rc INT;
EXEC @rc = dbo.TransferFunds @FromAccount = 1, @ToAccount = 2, @Amount = 100.00;
SELECT @rc;

The EXEC @rc = ... captures the return code.

The result set

A procedure can return a result set with a SELECT statement.

CREATE OR REPLACE PROCEDURE list_customer_orders(customer_id INT)
LANGUAGE plpgsql
AS $$
BEGIN
    RETURN QUERY
    SELECT id, order_date, total
    FROM orders
    WHERE customer_id = customer_id;
END;
$$;

The caller retrieves the result set as if the procedure were a query.

CALL list_customer_orders(42);

The multiple result sets

SQL Server procedures can return multiple result sets.

CREATE PROCEDURE dbo.GetCustomerAndOrders @CustomerId INT
AS
BEGIN
    SELECT * FROM customers WHERE id = @CustomerId;
    SELECT * FROM orders WHERE customer_id = @CustomerId;
END;

The client reads the result sets in order.

The dynamic SQL

A procedure can execute dynamic SQL with EXECUTE in PostgreSQL or EXEC in SQL Server.

-- PostgreSQL
CREATE OR REPLACE PROCEDURE drop_table(table_name TEXT)
LANGUAGE plpgsql
AS $$
BEGIN
    EXECUTE 'DROP TABLE IF EXISTS ' || quote_ident(table_name);
END;
$$;

-- SQL Server
CREATE PROCEDURE dbo.DropTable @TableName NVARCHAR(128)
AS
BEGIN
    EXEC('DROP TABLE IF EXISTS ' + QUOTENAME(@TableName));
END;

The dynamic SQL is the mechanism for operations that the static SQL cannot express, such as a table name that is a parameter.

The privilege

The EXECUTE privilege is granted to a user or a role.

GRANT EXECUTE ON PROCEDURE transfer_funds TO alice;
GRANT EXECUTE ON PROCEDURE transfer_funds TO developers;

The grant allows the user to call the procedure without the underlying table permissions.

c. The procedures versus the functions

The two are different tools. The table below summarizes the differences.

The comparison

Aspect	Procedure	Function
Invocation	`CALL name(...)`	`SELECT name(...)`
Return	Output parameters, result set, return code	A single value or a table
Side effects	Allowed	Prohibited
Transaction control	Allowed	Prohibited
Used in `SELECT`	No	Yes
Used in `FROM`	No	Yes
Multiple result sets	Yes	No
Error handling	`TRY`/`CATCH`, `EXCEPTION`	Limited
Performance	Cached plan	Black box in a query
Use case	Business operation	Computed value

The procedure use cases

A procedure is the right tool for the following.

Use case	Reason
Fund transfer	Multiple updates in one transaction
Order placement	Inserts across several tables
Data migration	Bulk operations with error handling
Batch job	Scheduled maintenance
Complex validation	Business rules with multiple steps

The function use cases

A function is the right tool for the following.

Use case	Reason
Tax calculation	A value per row
Status label	A `CASE` expression
Row set from a parameter	A table-valued function
Unnesting	A table-valued function

The anti-patterns

Anti-pattern	Problem
A procedure that returns one value	A function is the right tool
A procedure called in a loop	The loop should be a set-based query
A procedure with dynamic SQL for a static operation	The static SQL is safer
A procedure with too many parameters	The design is wrong
A procedure that duplicates the application logic	The logic should live in one place

The naming convention

The procedures are named with a verb: transfer_funds, place_order, process_payment. The functions are named with a noun or an adjective: calculate_tax, order_status_label, customer_orders. The convention makes the intent clear.

The schema prefix

The procedures are stored in a schema. The dbo. prefix is the convention in SQL Server, the public. schema is the default in PostgreSQL, and the database name is the default in MySQL.

EXEC dbo.TransferFunds ...;
CALL public.transfer_funds(...);
CALL mydb.transfer_funds(...);

Complete Example Session

-- ============================================
-- PART 1: THE BASIC PROCEDURE
-- ============================================

CREATE OR REPLACE PROCEDURE transfer_funds(
    from_account INT,
    to_account   INT,
    amount       NUMERIC
)
LANGUAGE plpgsql
AS $$
BEGIN
    UPDATE accounts SET balance = balance - amount WHERE id = from_account;
    UPDATE accounts SET balance = balance + amount WHERE id = to_account;
    INSERT INTO ledger (from_id, to_id, amount, created_at)
    VALUES (from_account, to_account, amount, NOW());
END;
$$;

-- ============================================
-- PART 2: THE CALL
-- ============================================

CALL transfer_funds(1, 2, 100.00);

-- ============================================
-- PART 3: THE OUTPUT PARAMETER
-- ============================================

CREATE OR REPLACE PROCEDURE get_balance(
    account_id INT,
    OUT balance NUMERIC
)
LANGUAGE plpgsql
AS $$
BEGIN
    SELECT a.balance INTO balance FROM accounts a WHERE a.id = account_id;
END;
$$;

DO $$
DECLARE
    result NUMERIC;
BEGIN
    CALL get_balance(1, result);
    RAISE NOTICE 'Balance: %', result;
END $$;

-- ============================================
-- PART 4: THE TRANSACTION CONTROL
-- ============================================

CREATE OR REPLACE PROCEDURE transfer_funds_checked(
    from_account INT,
    to_account   INT,
    amount       NUMERIC
)
LANGUAGE plpgsql
AS $$
DECLARE
    current_balance NUMERIC;
BEGIN
    UPDATE accounts SET balance = balance - amount WHERE id = from_account
    RETURNING balance INTO current_balance;

    IF current_balance < 0 THEN
        ROLLBACK;
        RAISE EXCEPTION 'Insufficient funds';
    END IF;

    UPDATE accounts SET balance = balance + amount WHERE id = to_account;
    COMMIT;
END;
$$;

-- ============================================
-- PART 5: THE ERROR HANDLING
-- ============================================

CREATE OR REPLACE PROCEDURE safe_transfer(
    from_account INT,
    to_account   INT,
    amount       NUMERIC
)
LANGUAGE plpgsql
AS $$
BEGIN
    UPDATE accounts SET balance = balance - amount WHERE id = from_account;
    UPDATE accounts SET balance = balance + amount WHERE id = to_account;
EXCEPTION
    WHEN OTHERS THEN
        RAISE NOTICE 'Transfer failed: %', SQLERRM;
        ROLLBACK;
END;
$$;

-- ============================================
-- PART 6: THE RESULT SET
-- ============================================

CREATE OR REPLACE PROCEDURE list_customer_orders(customer_id INT)
LANGUAGE plpgsql
AS $$
BEGIN
    RETURN QUERY
    SELECT id, order_date, total
    FROM orders
    WHERE customer_id = customer_id;
END;
$$;

CALL list_customer_orders(42);

-- ============================================
-- PART 7: THE SQL SERVER VERSION
-- ============================================

CREATE PROCEDURE dbo.TransferFunds
    @FromAccount INT,
    @ToAccount   INT,
    @Amount      DECIMAL(12,2),
    @Status      INT OUTPUT
AS
BEGIN
    SET NOCOUNT ON;

    BEGIN TRY
        BEGIN TRANSACTION;

        UPDATE accounts SET balance = balance - @Amount WHERE id = @FromAccount;
        UPDATE accounts SET balance = balance + @Amount WHERE id = @ToAccount;

        COMMIT;
        SET @Status = 0;
    END TRY
    BEGIN CATCH
        IF @@TRANCOUNT > 0 ROLLBACK;
        SET @Status = 1;
        THROW;
    END CATCH
END;

DECLARE @Status INT;
EXEC dbo.TransferFunds @FromAccount = 1, @ToAccount = 2, @Amount = 100.00, @Status = @Status OUTPUT;

-- ============================================
-- PART 8: THE MYSQL VERSION
-- ============================================

DELIMITER //

CREATE PROCEDURE transfer_funds(
    IN from_account INT,
    IN to_account   INT,
    IN amount       DECIMAL(12,2)
)
BEGIN
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        RESIGNAL;
    END;

    START TRANSACTION;
    UPDATE accounts SET balance = balance - amount WHERE id = from_account;
    UPDATE accounts SET balance = balance + amount WHERE id = to_account;
    COMMIT;
END //

DELIMITER ;

CALL transfer_funds(1, 2, 100.00);

-- ============================================
-- PART 9: THE GRANT
-- ============================================

GRANT EXECUTE ON PROCEDURE transfer_funds TO alice;

-- ============================================
-- PART 10: THE COMPLETE PATTERN
-- ============================================

-- 1. Use a procedure for a business operation.
-- 2. Use a function for a computed value.
-- 3. Declare the parameters with the correct mode.
-- 4. Manage the transaction inside the procedure.
-- 5. Handle the errors with TRY/CATCH or EXCEPTION.
-- 6. Return the result set with a SELECT.
-- 7. Grant the EXECUTE permission.
-- 8. Call the procedure with CALL or EXEC.

The ten parts showed the basic procedure, the call, the output parameter, the transaction control, the error handling, the result set, the SQL Server version, the MySQL version, the grant, and the complete pattern.

Quick Reference

The CREATE PROCEDURE Syntax

Database	Syntax
PostgreSQL	`CREATE PROCEDURE name(args) LANGUAGE plpgsql AS $$ BEGIN ... END; $$;`
SQL Server	`CREATE PROCEDURE name @arg type AS BEGIN ... END;`
MySQL	`CREATE PROCEDURE name(IN arg type) BEGIN ... END;`

The Parameter Modes

Mode	Direction
`IN`	Input
`OUT`	Output
`INOUT`	Both

The Call Syntax

Database	Syntax
PostgreSQL	`CALL name(args);`
SQL Server	`EXEC name @arg = value;`
MySQL	`CALL name(args);`

The Transaction Control

Database	Begin	Commit	Rollback
PostgreSQL	implicit	`COMMIT;`	`ROLLBACK;`
SQL Server	`BEGIN TRANSACTION;`	`COMMIT;`	`ROLLBACK;`
MySQL	`START TRANSACTION;`	`COMMIT;`	`ROLLBACK;`

The Error Handling

Database	Syntax
PostgreSQL	`EXCEPTION WHEN OTHERS THEN ...`
SQL Server	`BEGIN TRY ... END TRY BEGIN CATCH ... END CATCH`
MySQL	`DECLARE EXIT HANDLER FOR SQLEXCEPTION ...`

The Procedure vs Function

Aspect	Procedure	Function
Invocation	`CALL`	`SELECT`
Side effects	Allowed	Prohibited
Transactions	Allowed	Prohibited
Multiple result sets	Yes	No
Use in `SELECT`	No	Yes

Best Practices

✅ Do This:

-- Use a procedure for a business operation.
CREATE PROCEDURE transfer_funds(...)  -- ✅

-- Declare the parameters with the correct mode.
CREATE PROCEDURE get_balance(account_id INT, OUT balance NUMERIC)  -- ✅

-- Manage the transaction inside the procedure.
BEGIN
    UPDATE accounts SET balance = balance - amount WHERE id = from_account;
    IF (SELECT balance FROM accounts WHERE id = from_account) < 0 THEN
        ROLLBACK;
        RAISE EXCEPTION 'Insufficient funds';
    END IF;
    COMMIT;
END;  -- ✅

-- Handle the errors with the database's mechanism.
EXCEPTION WHEN OTHERS THEN ROLLBACK;  -- ✅

-- Grant the EXECUTE permission.
GRANT EXECUTE ON PROCEDURE transfer_funds TO alice;  -- ✅

-- Name the procedure with a verb.
transfer_funds, place_order, process_payment  -- ✅

❌ Don’t Do This:

-- Don't use a procedure for a computed value.
CREATE PROCEDURE calculate_tax(...)  -- ❌ Use a function

-- Don't forget the transaction control.
UPDATE accounts SET balance = balance - amount;  -- ❌ No commit or rollback

-- Don't use dynamic SQL for a static operation.
EXECUTE 'UPDATE accounts SET balance = balance - ' || amount;  -- ❌ SQL injection risk

-- Don't use a procedure in a SELECT.
SELECT transfer_funds(1, 2, 100.00);  -- ❌ Not allowed

-- Don't forget the error handling.
-- An unhandled error leaves the transaction open.  -- ❌

-- Don't use a procedure to wrap a single statement.
CREATE PROCEDURE update_balance(...) AS UPDATE accounts ...  -- ⚠️ A view or a trigger is better

Common Pitfalls

Pitfall	Why It Happens	Fix
Transaction left open	No commit or rollback	Add the transaction control
Error not handled	No exception block	Add the error handling
SQL injection	Dynamic SQL with concatenation	Use `quote_ident` or `QUOTENAME`
Procedure in a `SELECT`	Wrong tool	Use a function
Output parameter not set	Missing the `OUT` assignment	Set the value in the body
Grant missing	No `EXECUTE` privilege	`GRANT EXECUTE`
Delimiter not changed	MySQL with semicolons in the body	Change the delimiter

Real-World Examples

1. Basic Procedure

CREATE PROCEDURE transfer_funds(from_account INT, to_account INT, amount NUMERIC)
LANGUAGE plpgsql AS $$ BEGIN ... END; $$;

2. The Call

CALL transfer_funds(1, 2, 100.00);

3. Output Parameter

CREATE PROCEDURE get_balance(account_id INT, OUT balance NUMERIC) ...

4. Transaction Control

BEGIN; UPDATE ...; COMMIT;

5. Error Handling

EXCEPTION WHEN OTHERS THEN ROLLBACK;

6. Result Set

RETURN QUERY SELECT id, total FROM orders WHERE customer_id = customer_id;

7. SQL Server

CREATE PROCEDURE dbo.TransferFunds @FromAccount INT, ... AS BEGIN ... END;

8. MySQL

DELIMITER // CREATE PROCEDURE transfer_funds(...) BEGIN ... END // DELIMITER ;

9. Grant

GRANT EXECUTE ON PROCEDURE transfer_funds TO alice;

10. The Anti-Pattern

-- Don't use a procedure for a single statement.

Visual

The Procedure Structure

┌────────────────────────────────────────────────────────────┐
│  THE PROCEDURE STRUCTURE                                   │
│                                                            │
│  ┌──────────────────────────────────────────────────┐     │
│  │  CREATE PROCEDURE transfer_funds(                  │     │
│  │    from_account INT,     ← parameters              │     │
│  │    to_account   INT,                               │     │
│  │    amount       NUMERIC                            │     │
│  │  )                                                 │     │
│  │  LANGUAGE plpgsql                                  │     │
│  │  AS $$                                             │     │
│  │  BEGIN                                             │     │
│  │    UPDATE ...;    ← the body                       │     │
│  │    INSERT ...;                                     │     │
│  │    COMMIT;                                         │     │
│  │  END;                                              │     │
│  │  $$;                                               │     │
│  └──────────────────────────────────────────────────┘     │
│                                                            │
│  The procedure has the name, the parameters, and the body. │
└────────────────────────────────────────────────────────────┘

The Parameter Modes

┌────────────────────────────────────────────────────────────┐
│  THE PARAMETER MODES                                       │
│                                                            │
│  IN                                                        │
│  ┌──────────────────────────────────────────────────┐     │
│  │  CALL proc(42);                                   │     │
│  │  The caller passes the value.                     │     │
│  └──────────────────────────────────────────────────┘     │
│                                                            │
│  OUT                                                       │
│  ┌──────────────────────────────────────────────────┐     │
│  │  CALL proc(42, result);                           │     │
│  │  The procedure sets the value.                    │     │
│  └──────────────────────────────────────────────────┘     │
│                                                            │
│  INOUT                                                     │
│  ┌──────────────────────────────────────────────────┐     │
│  │  CALL proc(counter);                              │     │
│  │  The caller passes, the procedure modifies.       │     │
│  └──────────────────────────────────────────────────┘     │
└────────────────────────────────────────────────────────────┘

The Transaction Control

┌────────────────────────────────────────────────────────────┐
│  THE TRANSACTION CONTROL                                   │
│                                                            │
│  CALL transfer_funds(1, 2, 100.00);                        │
│                                                            │
│  ┌──────────────────────────────────────────────────┐     │
│  │  BEGIN (implicit)                                 │     │
│  │    UPDATE accounts SET balance = balance - 100;   │     │
│  │    UPDATE accounts SET balance = balance + 100;   │     │
│  │    INSERT INTO ledger ...;                        │     │
│  │                                                   │     │
│  │    IF balance < 0 THEN                            │     │
│  │      ROLLBACK;                                    │     │
│  │      RAISE EXCEPTION 'Insufficient funds';        │     │
│  │    END IF;                                        │     │
│  │                                                   │     │
│  │    COMMIT;                                        │     │
│  │  END                                              │     │
│  └──────────────────────────────────────────────────┘     │
│                                                            │
│  The procedure controls the transaction.                   │
└────────────────────────────────────────────────────────────┘

The Procedure vs Function

┌────────────────────────────────────────────────────────────┐
│  PROCEDURE vs FUNCTION                                     │
│                                                            │
│  PROCEDURE                                                 │
│  ┌──────────────────────────────────────────────────┐     │
│  │  CALL transfer_funds(1, 2, 100.00);               │     │
│  │                                                   │     │
│  │  Modifies the database.                           │     │
│  │  Manages the transaction.                         │     │
│  │  Returns output parameters and result sets.       │     │
│  └──────────────────────────────────────────────────┘     │
│                                                            │
│  FUNCTION                                                  │
│  ┌──────────────────────────────────────────────────┐     │
│  │  SELECT calculate_tax(total) FROM orders;         │     │
│  │                                                   │     │
│  │  Returns a value.                                 │     │
│  │  Does not modify the database.                    │     │
│  │  Used in a SELECT list.                           │     │
│  └──────────────────────────────────────────────────┘     │
│                                                            │
│  A procedure is a program. A function is an expression.    │
└────────────────────────────────────────────────────────────┘

Summary

Item	Value
Stored procedure	A named program in the catalog
`CALL`	The invocation statement
`IN`	The input parameter
`OUT`	The output parameter
`INOUT`	The input and output parameter
Transaction control	`COMMIT`, `ROLLBACK`
Error handling	`TRY`/`CATCH`, `EXCEPTION`
Result set	A `SELECT` in the body
`EXECUTE` privilege	The grant to call the procedure
`EXEC`	The SQL Server invocation

Key takeaways:

A stored procedure is a program, not an expression. It runs with CALL (or EXEC), it can modify the database, and it can manage its own transaction. A function, by contrast, is an expression that returns a value.
The parameters have modes. The IN parameter is the input, the OUT parameter is the output, and the INOUT parameter is both. The mode determines the direction of the value.
The procedure manages the transaction. It can begin, commit, and roll back. The transaction is the unit of atomicity, and the procedure is the code that knows the business rule.
The error handling is the database’s mechanism. PostgreSQL uses EXCEPTION, SQL Server uses TRY/CATCH, and MySQL uses DECLARE ... HANDLER. The mechanism is different, but the purpose is the same.
The procedure can return a result set. A SELECT in the body returns the rows to the caller. SQL Server can return multiple result sets.
The EXECUTE privilege is the security boundary. The user who has the privilege can call the procedure without the underlying table permissions. The tables remain protected.
The procedure is the right tool for a business operation. A fund transfer, an order placement, a batch job. The procedure is the unit of work, and it is the code that the application calls by name.
The function is the right tool for a computed value. The tax calculation, the status label, the table-valued row set. The function is the expression, and it is used in a query.

Remember: The stored procedure is the reusable program in the database. It is invoked with CALL, it can modify the data, it can manage the transaction, and it can return values through output parameters, a result set, or a return code. The procedure is the unit of the business operation, and the application calls it by name. The result is a schema where the complex operations are named, tested, and reused, and where the application does not need the table permissions to run them.

Stop using slow, ad-bloated tool sites! 🤮

🔎 Search “KandZ Tools” on Google to use many professional utilities for free.

KandZ.me is the ultimate minimalist hub for:
✅ Finance (Mortgage, Interest, Inflation)
✅ Tech (Base64, JSON, Dev Suite, IP)
✅ Health (BMI, BMR, TDEE)
✅ Productivity (Timer, Workspace, QR)

⚡️ Fast & Private
🔒 No data leaves your device
💎 100% Free

🔗 Use it now: https://tools.kandz.me
🔖 Bookmark it—you’ll need it later!