SQL 84 🛢️ Stored Procedure Input and Output Parameters - KandZ

⏱️ Estimated read: 17 min | |

A stored procedure without parameters is a fixed program. It does the same thing every time it runs, on the same data, and it can only be reused if the operation happens to be identical. Parameters change that. An input parameter passes a value into the procedure, so the same program serves a range of inputs. An output parameter passes a value back out, so the caller receives the result of the computation. The two together turn a procedure from a script into a reusable function of its inputs, and the mode of each parameter is what tells the database which direction the value flows.

Key point: The parameter mode is not optional in most databases. PostgreSQL uses the IN, OUT, and INOUT keywords. SQL Server uses OUTPUT for output parameters and DEFAULT for default values. MySQL uses IN, OUT, and INOUT. The mode determines the direction of the value, and getting it wrong produces a compile error or a silent null.

Why parameters matter

The reuse problem. A procedure that hardcodes the account ID and the amount is a procedure for one transfer. A procedure that takes the account IDs and the amount as parameters serves every transfer. The parameters are the difference between a script and a program.

The injection problem. A procedure that builds SQL by concatenating user input is vulnerable to SQL injection. A procedure with parameters passes the values through the database’s binding mechanism, which treats them as data, not as code. The parameters are the first line of defense against injection.

The plan-reuse problem. The database caches the query plan for a parameterized procedure. The same plan is reused for every call, regardless of the parameter values. The hardcoded version plans once per value.

The return problem. A procedure that computes a value must return it. The RETURN statement returns an integer, and the OUT parameter returns any type. A procedure that returns several values uses several OUT parameters, or a result set.

The caller problem. The caller passes the arguments in the order the parameters are declared, or by name. The named form is the more readable, and the positional form is the more compact. The database supports both in most cases.

The default problem. A parameter with a default value can be omitted by the caller. The procedure substitutes the default. The feature reduces the number of overloads and simplifies the API.

The variadic problem. A parameter that accepts a variable number of arguments is the variadic form. The caller passes a list, and the procedure iterates. The feature is useful for a procedure that operates on a set of values.

a. The input parameters

An input parameter passes a value from the caller into the procedure. It is the default mode in most databases, and it is the most common.

The PostgreSQL input parameter

CREATE OR REPLACE PROCEDURE greet(name TEXT)
LANGUAGE plpgsql
AS $$
BEGIN
    RAISE NOTICE 'Hello, %!', name;
END;
$$;

CALL greet('Alice');
-- NOTICE: Hello, Alice!

The parameter name is IN by default. The CALL passes the value, and the procedure uses it.

The SQL Server input parameter

CREATE PROCEDURE dbo.Greet @Name NVARCHAR(100)
AS
BEGIN
    PRINT 'Hello, ' + @Name + '!';
END;

EXEC dbo.Greet @Name = 'Alice';

The @ prefix is required. The parameter is input by default.

The MySQL input parameter

DELIMITER //

CREATE PROCEDURE greet(IN name VARCHAR(100))
BEGIN
    SELECT CONCAT('Hello, ', name, '!') AS message;
END //

DELIMITER ;

CALL greet('Alice');

The IN keyword is explicit in MySQL. The procedure selects the message.

The multiple input parameters

CREATE OR REPLACE PROCEDURE create_order(
    customer_id INT,
    order_date  DATE,
    total       NUMERIC
)
LANGUAGE plpgsql
AS $$
BEGIN
    INSERT INTO orders (customer_id, order_date, total)
    VALUES (customer_id, order_date, total);
END;
$$;

The procedure takes three parameters and inserts a row.

The parameter with a default

A parameter with a default value can be omitted by the caller.

CREATE OR REPLACE PROCEDURE create_order(
    customer_id INT,
    order_date  DATE DEFAULT CURRENT_DATE,
    total       NUMERIC DEFAULT 0
)
LANGUAGE plpgsql
AS $$
BEGIN
    INSERT INTO orders (customer_id, order_date, total)
    VALUES (customer_id, order_date, total);
END;
$$;

-- All parameters
CALL create_order(42, '2026-01-15', 100.00);

-- The defaults
CALL create_order(42);

The second call uses the current date and a total of zero.

The default in SQL Server

SQL Server uses the = syntax for the default.

CREATE PROCEDURE dbo.CreateOrder
    @CustomerId INT,
    @OrderDate  DATE = NULL,
    @Total      DECIMAL(12,2) = 0
AS
BEGIN
    SET @OrderDate = ISNULL(@OrderDate, CAST(GETDATE() AS DATE));
    INSERT INTO orders (customer_id, order_date, total)
    VALUES (@CustomerId, @OrderDate, @Total);
END;

The = NULL allows the caller to omit the parameter, and the procedure substitutes the current date.

The default in MySQL

MySQL does not support default parameter values. The workaround is a conditional inside the procedure.

DELIMITER //

CREATE PROCEDURE create_order(
    IN p_customer_id INT,
    IN p_order_date DATE,
    IN p_total DECIMAL(12,2)
)
BEGIN
    IF p_order_date IS NULL THEN
        SET p_order_date = CURDATE();
    END IF;

    INSERT INTO orders (customer_id, order_date, total)
    VALUES (p_customer_id, p_order_date, p_total);
END //

DELIMITER ;

The procedure checks for NULL and substitutes the current date.

The named arguments

PostgreSQL and SQL Server support the named-argument form.

-- PostgreSQL
CALL create_order(customer_id => 42, total => 100.00);

-- SQL Server
EXEC dbo.CreateOrder @CustomerId = 42, @Total = 100.00;

The named form is more readable, and it allows the caller to skip the parameters with defaults.

The positional arguments

The positional form passes the arguments in the declaration order.

CALL create_order(42, '2026-01-15', 100.00);

The positional form is more compact, but it depends on the parameter order.

b. The output parameters

An output parameter passes a value from the procedure back to the caller. The caller passes a variable, and the procedure sets it.

The PostgreSQL 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 balance parameter.

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

The result variable receives the value.

The SQL Server output parameter

CREATE PROCEDURE dbo.GetAccountBalance
    @AccountId INT,
    @Balance   DECIMAL(12,2) OUTPUT
AS
BEGIN
    SELECT @Balance = balance FROM accounts WHERE id = @AccountId;
END;

The OUTPUT keyword marks the parameter as output. The caller must also use OUTPUT.

DECLARE @Balance DECIMAL(12,2);
EXEC dbo.GetAccountBalance @AccountId = 42, @Balance = @Balance OUTPUT;
SELECT @Balance;

The DECLARE statement creates the variable, and the EXEC passes it with the OUTPUT keyword.

The MySQL output parameter

DELIMITER //

CREATE PROCEDURE get_account_balance(
    IN p_account_id INT,
    OUT p_balance DECIMAL(12,2)
)
BEGIN
    SELECT balance INTO p_balance FROM accounts WHERE id = p_account_id;
END //

DELIMITER ;

CALL get_account_balance(42, @balance);
SELECT @balance;

MySQL uses the session variable syntax @balance for the output.

The multiple output parameters

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

    SELECT COUNT(*), MAX(created_at)::DATE
    INTO transaction_count, last_transaction
    FROM transactions WHERE account_id = account_id;
END;
$$;

The procedure sets three output parameters.

DO $$
DECLARE
    bal NUMERIC;
    cnt INT;
    last DATE;
BEGIN
    CALL get_account_summary(42, bal, cnt, last);
    RAISE NOTICE 'Balance: %, Count: %, Last: %', bal, cnt, last;
END $$;

The output parameter with a default

An output parameter can have a default value in PostgreSQL.

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

The default value is used if the procedure does not set the parameter.

The return code

A procedure can return an integer code in addition to the output parameters.

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.

c. The INOUT parameters

An INOUT parameter passes a value into the procedure and receives the modified value back. It is the combination of IN and OUT.

The PostgreSQL INOUT parameter

CREATE OR REPLACE PROCEDURE increment_counter(
    INOUT counter INT,
    IN step INT DEFAULT 1
)
LANGUAGE plpgsql
AS $$
BEGIN
    counter := counter + step;
END;
$$;

The caller passes the initial value and reads the result.

DO $$
DECLARE
    value INT := 10;
BEGIN
    CALL increment_counter(value, 5);
    RAISE NOTICE 'Result: %', value;  -- 15
END $$;

The SQL Server INOUT parameter

SQL Server does not have an explicit INOUT mode. The OUTPUT keyword on a parameter makes it both input and output.

CREATE PROCEDURE dbo.IncrementCounter
    @Counter INT OUTPUT,
    @Step    INT = 1
AS
BEGIN
    SET @Counter = @Counter + @Step;
END;

The caller passes the initial value and the OUTPUT keyword.

DECLARE @Counter INT = 10;
EXEC dbo.IncrementCounter @Counter = @Counter OUTPUT, @Step = 5;
SELECT @Counter;  -- 15

The MySQL INOUT parameter

DELIMITER //

CREATE PROCEDURE increment_counter(
    INOUT counter INT,
    IN step INT
)
BEGIN
    SET counter = counter + step;
END //

DELIMITER ;

SET @counter = 10;
CALL increment_counter(@counter, 5);
SELECT @counter;  -- 15

The INOUT keyword is explicit.

The INOUT use case

The INOUT parameter is the right tool when the procedure modifies a value that the caller passes. A counter, an accumulator, a state machine, a transaction ID. The pattern is the in-place update.

The INOUT anti-pattern

The INOUT parameter is overused. A procedure that takes an INOUT parameter and returns a different value is often a sign that the design should use a return value or an OUT parameter.

-- ❌ The INOUT is used as an OUT.
CREATE PROCEDURE get_balance(account_id INT, INOUT balance NUMERIC) ...

-- ✅ The OUT is the right mode.
CREATE PROCEDURE get_balance(account_id INT, OUT balance NUMERIC) ...

The rule is to use INOUT only when the parameter is both an input and an output.

Complete Example Session

-- ============================================
-- PART 1: THE INPUT PARAMETER
-- ============================================

CREATE OR REPLACE PROCEDURE greet(name TEXT)
LANGUAGE plpgsql
AS $$
BEGIN
    RAISE NOTICE 'Hello, %!', name;
END;
$$;

CALL greet('Alice');

-- ============================================
-- PART 2: THE MULTIPLE INPUT PARAMETERS
-- ============================================

CREATE OR REPLACE PROCEDURE create_order(
    customer_id INT,
    order_date  DATE,
    total       NUMERIC
)
LANGUAGE plpgsql
AS $$
BEGIN
    INSERT INTO orders (customer_id, order_date, total)
    VALUES (customer_id, order_date, total);
END;
$$;

CALL create_order(42, '2026-01-15', 100.00);

-- ============================================
-- PART 3: THE DEFAULT PARAMETER
-- ============================================

CREATE OR REPLACE PROCEDURE create_order(
    customer_id INT,
    order_date  DATE DEFAULT CURRENT_DATE,
    total       NUMERIC DEFAULT 0
)
LANGUAGE plpgsql
AS $$
BEGIN
    INSERT INTO orders (customer_id, order_date, total)
    VALUES (customer_id, order_date, total);
END;
$$;

CALL create_order(42);

-- ============================================
-- PART 4: 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;
$$;

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

-- ============================================
-- PART 5: THE MULTIPLE OUTPUT PARAMETERS
-- ============================================

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

DO $$
DECLARE
    bal NUMERIC;
    cnt INT;
BEGIN
    CALL get_account_summary(42, bal, cnt);
    RAISE NOTICE 'Balance: %, Count: %', bal, cnt;
END $$;

-- ============================================
-- PART 6: THE INOUT PARAMETER
-- ============================================

CREATE OR REPLACE PROCEDURE increment_counter(
    INOUT counter INT,
    IN step INT DEFAULT 1
)
LANGUAGE plpgsql
AS $$
BEGIN
    counter := counter + step;
END;
$$;

DO $$
DECLARE
    value INT := 10;
BEGIN
    CALL increment_counter(value, 5);
    RAISE NOTICE 'Result: %', value;
END $$;

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

CREATE PROCEDURE dbo.GetAccountBalance
    @AccountId INT,
    @Balance   DECIMAL(12,2) OUTPUT
AS
BEGIN
    SELECT @Balance = balance FROM accounts WHERE id = @AccountId;
END;

DECLARE @Balance DECIMAL(12,2);
EXEC dbo.GetAccountBalance @AccountId = 42, @Balance = @Balance OUTPUT;
SELECT @Balance;

-- ============================================
-- PART 8: THE MYSQL OUTPUT
-- ============================================

DELIMITER //

CREATE PROCEDURE get_account_balance(
    IN p_account_id INT,
    OUT p_balance DECIMAL(12,2)
)
BEGIN
    SELECT balance INTO p_balance FROM accounts WHERE id = p_account_id;
END //

DELIMITER ;

CALL get_account_balance(42, @balance);
SELECT @balance;

-- ============================================
-- PART 9: THE RETURN 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;
$$;

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

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

-- 1. Use IN for input parameters.
-- 2. Use OUT for output parameters.
-- 3. Use INOUT for both.
-- 4. Use DEFAULT for optional parameters.
-- 5. Use named arguments for readability.
-- 6. Use the return code for the status.
-- 7. Use the output parameters for the values.
-- 8. The mode determines the direction of the value.

The ten parts showed the input parameter, the multiple input parameters, the default parameter, the output parameter, the multiple output parameters, the INOUT parameter, the SQL Server output, the MySQL output, the return code, and the complete pattern.

Quick Reference

The Parameter Modes

Mode	Direction	PostgreSQL	SQL Server	MySQL
Input	In	`IN` (default)	default	`IN`
Output	Out	`OUT`	`OUTPUT`	`OUT`
Both	In and out	`INOUT`	`OUTPUT`	`INOUT`

The Call Syntax

Database	Positional	Named
PostgreSQL	`CALL p(1, 2);`	`CALL p(a => 1, b => 2);`
SQL Server	`EXEC p 1, 2;`	`EXEC p @a = 1, @b = 2;`
MySQL	`CALL p(1, 2);`	—

The Output Parameter Syntax

Database	Declaration	Call
PostgreSQL	`OUT balance NUMERIC`	`CALL p(42, result);`
SQL Server	`@Balance DECIMAL OUTPUT`	`EXEC p @Balance = @Balance OUTPUT;`
MySQL	`OUT p_balance DECIMAL`	`CALL p(42, @balance);`

The Default Values

Database	Syntax
PostgreSQL	`param TYPE DEFAULT value`
SQL Server	`@param TYPE = value`
MySQL	Not supported

The Return Code

Database	Syntax
PostgreSQL	`OUT status INT`
SQL Server	`RETURN int`
MySQL	Not supported

Best Practices

✅ Do This:

-- Use IN for input parameters.
CREATE PROCEDURE greet(name TEXT)  -- ✅

-- Use OUT for output parameters.
CREATE PROCEDURE get_balance(id INT, OUT balance NUMERIC)  -- ✅

-- Use INOUT for both.
CREATE PROCEDURE increment(INOUT counter INT)  -- ✅

-- Use DEFAULT for optional parameters.
CREATE PROCEDURE create_order(id INT, date DATE DEFAULT CURRENT_DATE)  -- ✅

-- Use named arguments for readability.
CALL create_order(customer_id => 42, total => 100.00);  -- ✅

-- Use the return code for the status.
CALL transfer_funds(1, 2, 100.00, status);  -- ✅

❌ Don’t Do This:

-- Don't use INOUT for an output-only parameter.
CREATE PROCEDURE get_balance(id INT, INOUT balance NUMERIC)  -- ❌

-- Don't forget the OUTPUT keyword in SQL Server.
EXEC dbo.GetBalance @Id = 42, @Balance = @Balance;  -- ❌

-- Don't forget the variable in MySQL.
CALL get_balance(42, @balance);  -- ✅
CALL get_balance(42, balance);  -- ❌

-- Don't use a default in MySQL.
CREATE PROCEDURE create_order(id INT, date DATE DEFAULT CURRENT_DATE)  -- ❌

-- Don't use the named form in MySQL.
CALL create_order(customer_id => 42);  -- ❌

-- Don't ignore the return code.
CALL transfer_funds(1, 2, 100.00, status);  -- ✅ Always check status

Common Pitfalls

Pitfall	Why It Happens	Fix
Output is null	The caller did not pass a variable	Pass the variable
SQL Server error	Missing the `OUTPUT` keyword	Add the keyword
MySQL error	Using a local variable	Use the session variable `@name`
Default not applied	The database does not support it	Use a conditional
Named argument fails	The database does not support it	Use the positional form
INOUT is wrong	The parameter is output-only	Use `OUT`
Return code ignored	The caller does not check	Check the status

Real-World Examples

1. Input Parameter

CREATE PROCEDURE greet(name TEXT) ...;
CALL greet('Alice');

2. Multiple Input Parameters

CREATE PROCEDURE create_order(id INT, date DATE, total NUMERIC) ...;
CALL create_order(42, '2026-01-15', 100.00);

3. Default Parameter

CREATE PROCEDURE create_order(id INT, date DATE DEFAULT CURRENT_DATE) ...;
CALL create_order(42);

4. Output Parameter

CREATE PROCEDURE get_balance(id INT, OUT balance NUMERIC) ...;
CALL get_balance(42, result);

5. Multiple Output Parameters

CREATE PROCEDURE get_summary(id INT, OUT bal NUMERIC, OUT cnt INT) ...;
CALL get_summary(42, bal, cnt);

6. INOUT Parameter

CREATE PROCEDURE increment(INOUT counter INT) ...;
CALL increment(value);

7. SQL Server Output

CREATE PROCEDURE dbo.GetBalance @Id INT, @Balance DECIMAL OUTPUT AS ...;
EXEC dbo.GetBalance @Id = 42, @Balance = @Balance OUTPUT;

8. MySQL Output

CREATE PROCEDURE get_balance(IN id INT, OUT balance DECIMAL) ...;
CALL get_balance(42, @balance);

9. Return Code

CREATE PROCEDURE transfer(..., OUT status INT) ...;
CALL transfer(1, 2, 100.00, st);

10. The Complete Call

CALL transfer_funds(1, 2, 100.00, status);

Visual

The Parameter Modes

┌────────────────────────────────────────────────────────────┐
│  THE PARAMETER MODES                                       │
│                                                            │
│  IN                                                        │
│  ┌──────────────────────────────────────────────────┐     │
│  │  Caller ──────▶ Procedure                         │     │
│  │  The value flows into the procedure.              │     │
│  └──────────────────────────────────────────────────┘     │
│                                                            │
│  OUT                                                       │
│  ┌──────────────────────────────────────────────────┐     │
│  │  Caller ◀────── Procedure                         │     │
│  │  The value flows out of the procedure.            │     │
│  └──────────────────────────────────────────────────┘     │
│                                                            │
│  INOUT                                                     │
│  ┌──────────────────────────────────────────────────┐     │
│  │  Caller ──────▶ Procedure                         │     │
│  │  Caller ◀────── Procedure                         │     │
│  │  The value flows in and out.                      │     │
│  └──────────────────────────────────────────────────┘     │
└────────────────────────────────────────────────────────────┘

The Call Flow

┌────────────────────────────────────────────────────────────┐
│  THE CALL FLOW                                             │
│                                                            │
│  DECLARE                                                   │
│  ┌──────────────────────────────────────────────────┐     │
│  │  result NUMERIC;                                  │     │
│  └────────────────────────┬─────────────────────────┘     │
│                           │                                │
│                           v                                │
│  CALL                                                      │
│  ┌──────────────────────────────────────────────────┐     │
│  │  CALL get_balance(42, result);                    │     │
│  │                                                   │     │
│  │  The procedure sets the result.                   │     │
│  └────────────────────────┬─────────────────────────┘     │
│                           │                                │
│                           v                                │
│  USE                                                       │
│  ┌──────────────────────────────────────────────────┐     │
│  │  RAISE NOTICE 'Balance: %', result;               │     │
│  │                                                   │     │
│  │  The value is available.                          │     │
│  └──────────────────────────────────────────────────┘     │
└────────────────────────────────────────────────────────────┘

The Default Parameters

┌────────────────────────────────────────────────────────────┐
│  THE DEFAULT PARAMETERS                                    │
│                                                            │
│  CREATE PROCEDURE create_order(                            │
│    customer_id INT,                                        │
│    order_date  DATE DEFAULT CURRENT_DATE,                  │
│    total       NUMERIC DEFAULT 0                           │
│  )                                                         │
│                                                            │
│  CALL create_order(42);                                    │
│  ┌──────────────────────────────────────────────────┐     │
│  │  customer_id = 42                                 │     │
│  │  order_date  = CURRENT_DATE (default)             │     │
│  │  total       = 0 (default)                        │     │
│  └──────────────────────────────────────────────────┘     │
│                                                            │
│  CALL create_order(42, '2026-01-15', 100.00);              │
│  ┌──────────────────────────────────────────────────┐     │
│  │  customer_id = 42                                 │     │
│  │  order_date  = '2026-01-15'                       │     │
│  │  total       = 100.00                             │     │
│  └──────────────────────────────────────────────────┘     │
└────────────────────────────────────────────────────────────┘

The SQL Server Output

┌────────────────────────────────────────────────────────────┐
│  THE SQL SERVER OUTPUT                                     │
│                                                            │
│  CREATE PROCEDURE dbo.GetBalance                           │
│    @AccountId INT,                                         │
│    @Balance   DECIMAL(12,2) OUTPUT                         │
│  AS                                                        │
│  BEGIN                                                     │
│    SELECT @Balance = balance FROM accounts WHERE id = @AccountId;
│  END                                                       │
│                                                            │
│  DECLARE @Balance DECIMAL(12,2);                           │
│  EXEC dbo.GetBalance                                       │
│    @AccountId = 42,                                        │
│    @Balance = @Balance OUTPUT;                             │
│  SELECT @Balance;                                          │
│                                                            │
│  The @Balance variable receives the value.                 │
└────────────────────────────────────────────────────────────┘

Summary

Item	Value
`IN`	Input parameter
`OUT`	Output parameter
`INOUT`	Both input and output
`OUTPUT`	The SQL Server keyword for output
`DEFAULT`	The default value
Named arguments	`CALL p(a => 1)`
Positional arguments	`CALL p(1)`
Session variable	`@name` in MySQL
Return code	The integer status
Output parameter	A value returned to the caller

Key takeaways:

The parameter mode determines the direction of the value. The IN parameter flows into the procedure, the OUT parameter flows out, and the INOUT parameter flows both ways. The mode is declared in the procedure signature and used in the call.
The input parameter is the default. A procedure that takes a value uses an input parameter, and the caller passes the value in the CALL or EXEC statement.
The output parameter is the way to return a value. A procedure that computes a result uses an output parameter, and the caller passes a variable that receives the value.
The INOUT parameter is the combination. It is used when the procedure modifies a value that the caller passes. The counter and the accumulator are the common cases.
The default value is the optional parameter. The caller can omit the argument, and the procedure substitutes the default. The feature reduces the number of overloads.
The named arguments are more readable. The CALL p(a => 1, b => 2) form is clearer than the positional form, and it allows the caller to skip the parameters with defaults.
The return code is the integer status. The RETURN statement returns an integer, and the OUT status INT parameter is the alternative. The caller checks the value to determine the outcome.
The output parameter syntax differs between databases. PostgreSQL uses OUT, SQL Server uses OUTPUT, and MySQL uses OUT with the session variable. The mode is the same, but the syntax is not.

Remember: The stored procedure parameters are the mechanism that turns a fixed program into a reusable function of its inputs. The IN parameter passes a value in, the OUT parameter passes a value out, and the INOUT parameter does both. The default value makes a parameter optional, and the named arguments make the call readable. The return code reports the status, and the output parameters carry the results. The result is a procedure that serves a range of inputs, that returns the values the caller needs, and that the application calls by name.

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!