Stored Procedures: Benefits and Types (Input/Output Parameters) | Database & SQL

Q: What is a stored procedure?

A stored procedure is created with CREATE PROCEDURE and holds a body written in the engine's procedural language: PL/SQL in Oracle, PL/pgSQL in PostgreSQL, TSQL in SQL Server, or SQL/PSM in MySQL. The database parses and compiles it once, caches an execution plan, and repeated calls skip reparsing.

Q: Why do we need stored procedures?

Stored procedures exist because multistep database operations that run as separate clientserver calls are slow, errorprone, and hard to keep atomic. A nightly batch that touches millions of rows runs far faster executed serverside than rowbyrow from the application. Without a procedure: multiple rou

Q: How do IN, OUT, and INOUT parameters work?

IN parameters pass values into the procedure and are readonly inside it. OUT parameters are written by the procedure and returned to the caller. INOUT parameters do both: the caller passes an initial value that the procedure can read and then overwrite. PostgreSQL: procedure with IN and INOUT parame

Q: How is a stored procedure different from a function?

Function: can be used inside a SELECT expression CREATE OR REPLACE FUNCTION fn_calculate_interest( p_balance NUMERIC, p_rate NUMERIC ) RETURNS NUMERIC LANGUAGE plpgsql AS $$ BEGIN RETURN ROUND(p_balance p_rate / 100, 2); END; $$; SELECT account_id, balance, fn_calculate_interest(balance, 7.5) AS ann

Q: When should you use stored procedures?

Use stored procedures for heavy setbased data operations close to the data, batch processing, and operations that must be atomic across many steps. Keep general business logic, validation, and orchestration in the application layer where it is easier to test, version, and scale. Good use: nightly in

Q: What about error handling in stored procedures?

Error handling varies by engine. PostgreSQL and Oracle use EXCEPTION WHEN ... THEN blocks. SQL Server uses BEGIN TRY / BEGIN CATCH with XACT_STATE(). PostgreSQL: error handling with EXCEPTION block CREATE OR REPLACE PROCEDURE sp_safe_debit( p_account_id VARCHAR, p_amount NUMERIC, INOUT p_status VARC

Definition

A stored procedure is named, precompiled SQL logic living in the database, called with IN/OUT/INOUT parameters, giving performance, security, reuse, and encapsulation near the data.

Stored procedure – a named, precompiled block of SQL and procedural logic stored in the database and invoked by name.
IN parameter – passes a value into the procedure; read-only inside the body; the default mode.
OUT parameter – written by the procedure and read by the caller after it returns; used to hand back results or status.
INOUT parameter – the caller passes an initial value that the procedure can read and then overwrite.
Performance – precompiled execution plan, cached by the engine, fewer client-server round trips.
Security – grant EXECUTE on the procedure while denying direct table access; bound parameters resist SQL injection.

A stored procedure is like a standard operating procedure kept inside the National Bank of India back office. Instead of a clerk sending ten separate instructions over the phone to move money, they call one named procedure, "TransferFunds," hand it the account numbers and amount (the IN parameters), and it does all the steps inside the vault and hands back a success flag and the new balance (the OUT parameters). The procedure lives where the money is, so it is fast, consistent, and the clerk never needs direct access to the vault's drawers.

Understanding the Concepts

What is a stored procedure?

A stored procedure is created with CREATE PROCEDURE and holds a body written in the engine's procedural language: PL/SQL in Oracle, PL/pgSQL in PostgreSQL, T-SQL in SQL Server, or SQL/PSM in MySQL. The database parses and compiles it once, caches an execution plan, and repeated calls skip reparsing.

SQL

-- PostgreSQL: simple stored procedure for National Bank of India
CREATE OR REPLACE PROCEDURE sp_credit_account(
    p_account_id VARCHAR,
    p_amount     NUMERIC
)
LANGUAGE plpgsql
AS $$
BEGIN
    UPDATE accounts
    SET balance = balance + p_amount
    WHERE account_id = p_account_id;

    IF NOT FOUND THEN
        RAISE EXCEPTION 'Account % not found', p_account_id;
    END IF;
END;
$$;

-- Call the procedure
CALL sp_credit_account('SAV2001', 50000.00);

Why do we need stored procedures?

Stored procedures exist because multi-step database operations that run as separate client-server calls are slow, error-prone, and hard to keep atomic. A nightly batch that touches millions of rows runs far faster executed server-side than row-by-row from the application.

SQL

-- Without a procedure: multiple round trips from the application
-- Round trip 1: Check balance
-- Round trip 2: Debit source
-- Round trip 3: Credit target
-- Round trip 4: Insert audit log
-- If the app crashes between step 2 and 3, money disappears

-- With a procedure: one call, all steps atomic
CALL sp_transfer_funds('SAV2001', 'SAV2002', 25000.00);

How do IN, OUT, and INOUT parameters work?

IN parameters pass values into the procedure and are read-only inside it. OUT parameters are written by the procedure and returned to the caller. INOUT parameters do both: the caller passes an initial value that the procedure can read and then overwrite.

SQL

-- PostgreSQL: procedure with IN and INOUT parameters
CREATE OR REPLACE PROCEDURE sp_transfer_funds(
    IN  p_source_id  VARCHAR,
    IN  p_target_id  VARCHAR,
    IN  p_amount     NUMERIC,
    INOUT p_status   VARCHAR DEFAULT 'PENDING'
)
LANGUAGE plpgsql
AS $$
DECLARE
    v_source_balance NUMERIC;
BEGIN
    -- Check source balance
    SELECT balance INTO v_source_balance
    FROM accounts
    WHERE account_id = p_source_id
    FOR UPDATE;

    IF v_source_balance IS NULL THEN
        p_status := 'FAILED_SOURCE_NOT_FOUND';
        RETURN;
    END IF;

    IF v_source_balance < p_amount THEN
        p_status := 'FAILED_INSUFFICIENT_BALANCE';
        RETURN;
    END IF;

    -- Debit and credit
    UPDATE accounts SET balance = balance - p_amount
    WHERE account_id = p_source_id;

    UPDATE accounts SET balance = balance + p_amount
    WHERE account_id = p_target_id;

    p_status := 'SUCCESS';
END;
$$;

-- Call and read the status
CALL sp_transfer_funds('SAV2001', 'SAV2002', 10000.00, NULL);

TEXT

Output:
 p_status
----------
 SUCCESS

How is a stored procedure different from a function?

| Aspect | Stored Procedure | Function |
|---|---|---|
| Purpose | Performs actions (DML, transactions) | Computes and returns a value |
| Return | Via OUT/INOUT parameters, status | Via RETURN value |
| Usable in SELECT | No | Yes |
| Transaction control | Can COMMIT/ROLLBACK | Usually cannot (engine-dependent) |
| Invocation | CALL procedure_name(...) | SELECT function_name(...) |

SQL

-- Function: can be used inside a SELECT expression
CREATE OR REPLACE FUNCTION fn_calculate_interest(
    p_balance NUMERIC,
    p_rate    NUMERIC
) RETURNS NUMERIC
LANGUAGE plpgsql
AS $$
BEGIN
    RETURN ROUND(p_balance * p_rate / 100, 2);
END;
$$;

SELECT account_id, balance,
       fn_calculate_interest(balance, 7.5) AS annual_interest
FROM accounts
WHERE status = 'ACTIVE';

What are the benefits of stored procedures?

The benefits are performance, security, reusability, and encapsulation. Performance comes from precompiled plans and fewer round trips. Security comes from granting EXECUTE without table access. Reusability comes from centralizing a business rule so every caller applies it identically.

JAVA

package com.bank.service;

import java.math.BigDecimal;
import java.sql.*;
import java.util.logging.Logger;

public class TransferService {

    private static final Logger LOG = Logger.getLogger(TransferService.class.getName());
    private final Connection connection;

    public TransferService(Connection connection) {
        this.connection = connection;
    }

    public String transfer(String sourceId, String targetId, BigDecimal amount)
            throws SQLException {
        // One round trip instead of four
        String sql = "CALL sp_transfer_funds(?, ?, ?, NULL)";
        try (CallableStatement cs = connection.prepareCall(sql)) {
            cs.setString(1, sourceId);
            cs.setString(2, targetId);
            cs.setBigDecimal(3, amount);
            cs.execute();
            ResultSet rs = cs.getResultSet();
            if (rs.next()) {
                String status = rs.getString(1);
                LOG.info("Transfer status: " + status);
                return status;
            }
            return "UNKNOWN";
        }
    }
}

What are the trade-offs and disadvantages?

Stored procedures scatter business logic between the database and the application. They are harder to version-control and unit-test than application code. They create vendor lock-in through engine-specific procedural languages. They can complicate horizontal scaling because the logic is tied to the database server.

DIAGRAM

Trade-off summary:

  Benefit                      Trade-off
  ─────────────────────────    ───────────────────────────────
  Precompiled, fast            Vendor lock-in (PL/SQL vs T-SQL)
  Fewer round trips            Harder to unit-test
  EXECUTE-only security        Business logic scattered
  Central business rule        Version control is awkward
  Atomic multi-step logic      Complicates horizontal scaling

When should you use stored procedures?

Use stored procedures for heavy set-based data operations close to the data, batch processing, and operations that must be atomic across many steps. Keep general business logic, validation, and orchestration in the application layer where it is easier to test, version, and scale.

SQL

-- Good use: nightly interest calculation over millions of accounts
CREATE OR REPLACE PROCEDURE sp_calculate_monthly_interest()
LANGUAGE plpgsql
AS $$
BEGIN
    UPDATE accounts
    SET balance = balance + ROUND(balance * annual_rate / 1200, 2),
        last_interest_date = CURRENT_DATE
    WHERE status = 'ACTIVE'
      AND account_type = 'SAVINGS'
      AND (last_interest_date IS NULL
           OR last_interest_date < DATE_TRUNC('month', CURRENT_DATE));

    RAISE NOTICE 'Interest calculated for % accounts', ROW_COUNT;
END;
$$;

CALL sp_calculate_monthly_interest();

What about error handling in stored procedures?

Error handling varies by engine. PostgreSQL and Oracle use EXCEPTION WHEN ... THEN blocks. SQL Server uses BEGIN TRY / BEGIN CATCH with XACT_STATE().

SQL

-- PostgreSQL: error handling with EXCEPTION block
CREATE OR REPLACE PROCEDURE sp_safe_debit(
    p_account_id VARCHAR,
    p_amount     NUMERIC,
    INOUT p_status VARCHAR DEFAULT 'PENDING'
)
LANGUAGE plpgsql
AS $$
BEGIN
    UPDATE accounts
    SET balance = balance - p_amount
    WHERE account_id = p_account_id
      AND balance >= p_amount;

    IF NOT FOUND THEN
        p_status := 'FAILED_INSUFFICIENT_BALANCE';
        RETURN;
    END IF;

    p_status := 'SUCCESS';
EXCEPTION
    WHEN OTHERS THEN
        p_status := 'FAILED_' || SQLERRM;
        RAISE NOTICE 'Debit failed: %', SQLERRM;
END;
$$;

What are common interview follow-up questions?

Interviewers often ask about the difference between procedures and functions, how to handle transactions inside a procedure, what happens when a procedure calls another procedure, and how to version-control procedures with tools like Flyway or Liquibase.

SQL

-- Flyway migration file: V5__create_transfer_procedure.sql
CREATE OR REPLACE PROCEDURE sp_transfer_funds(
    IN  p_source_id  VARCHAR,
    IN  p_target_id  VARCHAR,
    IN  p_amount     NUMERIC,
    INOUT p_status   VARCHAR DEFAULT 'PENDING'
)
LANGUAGE plpgsql
AS $$
-- ... procedure body ...
$$;

Visual Overview

DIAGRAM

┌──────────────────────────────────────────────────┐
│        Stored Procedure Execution Flow           │
├──────────────────────────────────────────────────┤
│                                                  │
│  Application (Rajesh's code)                     │
│  ┌───────────────────────┐                       │
│  │ CALL sp_transfer_funds│                       │
│  │  ('SAV2001',          │                       │
│  │   'SAV2002',          │                       │
│  │   10000.00, NULL)     │  ── one round trip ─► │
│  └───────────────────────┘                       │
│                                                  │
│  Database Server                                 │
│  ┌───────────────────────────────────────┐       │
│  │  1. Look up cached execution plan     │       │
│  │  2. Read IN params (source, target,   │       │
│  │     amount)                           │       │
│  │  3. Check source balance (FOR UPDATE) │       │
│  │  4. Debit source account              │       │
│  │  5. Credit target account             │       │
│  │  6. Write audit log                   │       │
│  │  7. Set OUT status = 'SUCCESS'        │       │
│  └───────────────────────────────────────┘       │
│          │                                       │
│          ▼                                       │
│  ┌───────────────────────┐                       │
│  │ Return status to app  │  ◄── one response ── │
│  └───────────────────────┘                       │
│                                                  │
├──────────────────────────────────────────────────┤
│  Parameter Modes                                 │
│                                                  │
│  IN ────►  Procedure Body  ────► OUT             │
│  (read)                          (write)         │
│                                                  │
│  INOUT ──► Procedure Body  ────► INOUT           │
│  (read)    (can modify)          (overwritten)   │
└──────────────────────────────────────────────────┘

Key Takeaway

A stored procedure is a named, precompiled block of SQL and procedural logic that lives inside the database and is invoked by name, written in the engine's procedural language: PL/SQL in Oracle, PL/pgSQL in PostgreSQL, T-SQL in SQL Server. The database compiles it once and caches a plan, so repeated calls skip reparsing and a multi-step operation runs in a single server-side call instead of many client-server round trips.

Parameters come in three modes. An IN parameter passes a value in and is read-only inside the procedure. An OUT parameter is written by the procedure and read by the caller after it returns. An INOUT parameter does both. The distinction from a function is that a function returns a value usable inside an expression, whereas a procedure performs an action and reports through OUT parameters and status.

At the National Bank of India, Pankaj encapsulated fund transfers in a sp_transfer_funds procedure that took the source account, target account, and amount as IN parameters and returned a status as an INOUT parameter, validating, debiting, crediting, and auditing atomically in one call instead of four round trips that risked partial updates. The benefits were performance from the cached plan, security from granting EXECUTE without table access, and consistency from centralizing the transfer rule.

The trade-offs are real: procedures scatter business logic between the database and the application, are harder to version-control and unit-test, and create vendor lock-in. The balanced approach is to keep heavy set-based data operations in procedures and general business logic in the application layer.

Remember one sentence: a stored procedure is named, precompiled SQL logic in the database, called with IN, OUT, and INOUT parameters, giving performance, security, reuse, and encapsulation near the data.

---