SQL Advanced FeaturesStored Procedures

Stored Procedures

LevelIntermediate

Duration90 mins

TopicStored Procedures

3 / 5

Parameters (IN, OUT, INOUT)

The Procedure Interface

Parameters transform stored procedures from static code blocks into flexible, reusable operations. Without parameters, every procedure would do exactly one thing with hardcoded values. With parameters, a single procedure can serve unlimited use cases—processing different orders, calculating taxes for different amounts, or validating different user inputs.

Understanding parameters means understanding data flow: how values move from calling applications into procedures (inputs), how computed results return to callers (outputs), and how bidirectional data exchange happens when procedures need to both receive and modify values.

What You Will Learn

By the end of this page, you will understand the three parameter modes (IN, OUT, INOUT), how each database system implements them, data type considerations, default values, passing patterns, and best practices for designing procedure interfaces that are both flexible and safe.

The Three Parameter Modes

Stored procedure parameters come in three fundamental modes, each defining how data flows:

IN Parameters (Input Only)

Pass values into the procedure
Cannot be modified by the procedure
The calling application provides the value
Most common parameter type

OUT Parameters (Output Only)

Receive values from the procedure
Procedure sets the value, caller reads it
Caller provides a variable to receive the output
Used for returning computation results, status codes, error messages

INOUT Parameters (Bidirectional)

Pass values in, receive modified values out
Caller provides initial value, procedure can modify it
Final value available to caller after execution
Useful for counters, accumulators, or in-place transformations

Parameter Mode Comparison
Mode	Data Direction	Caller Provides	Procedure Can Modify	Typical Use
IN	Caller → Procedure	Value	No	Search criteria, IDs, configuration values
OUT	Procedure → Caller	Variable (empty)	Yes (sets value)	Results, status codes, generated IDs
INOUT	Both directions	Initial value	Yes (modifies)	Counters, accumulators, transformations

Default Mode Varies by Database

SQL Server defaults to IN for all parameters unless OUTPUT is specified. Oracle and MySQL require explicit IN, OUT, or INOUT keywords. PostgreSQL uses IN by default for procedures, with INOUT for output returns.

IN Parameters: Passing Data In

IN parameters are the most common—they pass values from the calling application into the procedure. The procedure uses these values but cannot modify them.

Key characteristics:

Passed by value (in most systems)
Changes inside procedure don't affect caller's variable
Can be literal values, variables, or expressions
Must be provided unless a default is defined

in_parameters.sql

-- IN parameters: The default mode in SQL Server
CREATE PROCEDURE Sales.GetCustomerOrders
    @CustomerID INT,                    -- Required IN parameter
    @StartDate DATE = NULL,             -- Optional with default
    @EndDate DATE = NULL,               -- Optional with default
    @MaxResults INT = 100               -- Optional with default
AS
BEGIN
    SET NOCOUNT ON;
    
    -- Use parameters as read-only values
    SELECT TOP (@MaxResults)
        o.OrderID,
        o.OrderDate,
        o.TotalAmount,
        o.Status
    FROM Sales.Orders o
    WHERE o.CustomerID = @CustomerID
      AND (@StartDate IS NULL OR o.OrderDate >= @StartDate)
      AND (@EndDate IS NULL OR o.OrderDate <= @EndDate)
    ORDER BY o.OrderDate DESC;
END;
GO
 
-- Calling with all parameters
EXEC Sales.GetCustomerOrders 
    @CustomerID = 1001,
    @StartDate = '2024-01-01',
    @EndDate = '2024-12-31',
    @MaxResults = 50;
 
-- Calling with only required parameter (uses defaults)
EXEC Sales.GetCustomerOrders @CustomerID = 1001;
 
-- Calling with positional arguments (not recommended)
EXEC Sales.GetCustomerOrders 1001, '2024-01-01', '2024-12-31', 50;
 
-- Calling with mixed named/positional (named must come last)
EXEC Sales.GetCustomerOrders 1001, @MaxResults = 25;

OUT Parameters: Returning Data

OUT parameters allow procedures to return values to the caller—computed results, status codes, error messages, or generated identifiers. Unlike functions (which return values directly), procedures use OUT parameters for output.

Key characteristics:

Caller provides a variable to receive the value
Procedure sets the value (initial caller value is typically ignored)
Multiple OUT parameters allow returning multiple values
Common for status/error message patterns

out_parameters.sql

-- OUT parameters use the OUTPUT keyword
CREATE PROCEDURE Sales.CreateOrder
    @CustomerID INT,
    @ProductID INT,
    @Quantity INT,
    @OrderID INT OUTPUT,                  -- Returns generated order ID
    @TotalAmount DECIMAL(18,2) OUTPUT,    -- Returns calculated total
    @Success BIT OUTPUT,                  -- Returns success status
    @ErrorMessage NVARCHAR(500) OUTPUT    -- Returns error if any
AS
BEGIN
    SET NOCOUNT ON;
    
    DECLARE @UnitPrice DECIMAL(18,2);
    DECLARE @AvailableQty INT;
    
    BEGIN TRY
        -- Validate customer
        IF NOT EXISTS (SELECT 1 FROM Sales.Customers WHERE CustomerID = @CustomerID)
        BEGIN
            SET @Success = 0;
            SET @ErrorMessage = 'Customer not found';
            SET @OrderID = NULL;
            SET @TotalAmount = NULL;
            RETURN;
        END
        
        -- Get product details and validate stock
        SELECT @UnitPrice = UnitPrice, @AvailableQty = StockQuantity
        FROM Products
        WHERE ProductID = @ProductID;
        
        IF @AvailableQty < @Quantity
        BEGIN
            SET @Success = 0;
            SET @ErrorMessage = 'Insufficient stock. Available: ' + CAST(@AvailableQty AS NVARCHAR);
            SET @OrderID = NULL;
            SET @TotalAmount = NULL;
            RETURN;
        END
        
        -- Calculate total
        SET @TotalAmount = @UnitPrice * @Quantity;
        
        -- Create order
        BEGIN TRANSACTION;
        
        INSERT INTO Sales.Orders (CustomerID, OrderDate, TotalAmount, Status)
        VALUES (@CustomerID, GETDATE(), @TotalAmount, 'Pending');
        
        SET @OrderID = SCOPE_IDENTITY();  -- Get generated ID
        
        INSERT INTO Sales.OrderLines (OrderID, ProductID, Quantity, UnitPrice)
        VALUES (@OrderID, @ProductID, @Quantity, @UnitPrice);
        
        UPDATE Products
        SET StockQuantity = StockQuantity - @Quantity
        WHERE ProductID = @ProductID;
        
        COMMIT TRANSACTION;
        
        SET @Success = 1;
        SET @ErrorMessage = NULL;
        
    END TRY
    BEGIN CATCH
        IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION;
        SET @Success = 0;
        SET @ErrorMessage = ERROR_MESSAGE();
        SET @OrderID = NULL;
        SET @TotalAmount = NULL;
    END CATCH
END;
GO
 
-- Calling with OUTPUT parameters
DECLARE @NewOrderID INT;
DECLARE @Total DECIMAL(18,2);
DECLARE @OK BIT;
DECLARE @Err NVARCHAR(500);
 
EXEC Sales.CreateOrder
    @CustomerID = 1001,
    @ProductID = 5001,
    @Quantity = 3,
    @OrderID = @NewOrderID OUTPUT,      -- OUTPUT keyword required
    @TotalAmount = @Total OUTPUT,
    @Success = @OK OUTPUT,
    @ErrorMessage = @Err OUTPUT;
 
-- Check results
IF @OK = 1
    SELECT 'Order created' AS Status, @NewOrderID AS OrderID, @Total AS Total;
ELSE
    SELECT 'Order failed' AS Status, @Err AS Error;

Output Parameter Pattern

A common pattern uses three standard OUT parameters: Success (boolean), ErrorCode (string/int), and ErrorMessage (text). This provides consistent error handling across all procedures in a codebase.

INOUT Parameters: Bidirectional Data Flow

INOUT parameters (called INPUT/OUTPUT in some contexts) enable bidirectional data exchange. The caller provides an initial value, the procedure can read and modify it, and the modified value is returned to the caller.

Key characteristics:

Combines IN and OUT behavior
Caller's variable is modified by the procedure
Useful for accumulators, counters, or in-place transformations

inout_parameters.sql

-- SQL Server uses OUTPUT for both OUT and INOUT
-- The difference is whether you pass an initial value
CREATE PROCEDURE Utilities.AccumulateTotal
    @NewAmount DECIMAL(18,2),           -- IN: Value to add
    @RunningTotal DECIMAL(18,2) OUTPUT  -- INOUT: Accumulator
AS
BEGIN
    SET NOCOUNT ON;
    
    -- Read the current value, add to it, and set back
    SET @RunningTotal = ISNULL(@RunningTotal, 0) + @NewAmount;
    
    -- The modified value is returned to the caller
END;
GO
 
-- Example: Processing a batch of items with running total
DECLARE @Total DECIMAL(18,2) = 0;  -- Initial value provided
 
EXEC Utilities.AccumulateTotal @NewAmount = 100.00, @RunningTotal = @Total OUTPUT;
-- @Total is now 100.00
 
EXEC Utilities.AccumulateTotal @NewAmount = 250.50, @RunningTotal = @Total OUTPUT;
-- @Total is now 350.50
 
EXEC Utilities.AccumulateTotal @NewAmount = 75.25, @RunningTotal = @Total OUTPUT;
-- @Total is now 425.75
 
SELECT @Total AS FinalTotal;  -- 425.75
 
-- More practical example: Counter with formatting
CREATE PROCEDURE Utilities.IncrementAndFormat
    @Value INT OUTPUT,
    @Prefix VARCHAR(10) = 'Item',
    @FormattedOutput VARCHAR(50) OUTPUT
AS
BEGIN
    SET NOCOUNT ON;
    
    -- Increment the counter (INOUT behavior)
    SET @Value = ISNULL(@Value, 0) + 1;
    
    -- Format and output
    SET @FormattedOutput = @Prefix + '-' + RIGHT('0000' + CAST(@Value AS VARCHAR), 4);
END;
GO
 
-- Usage
DECLARE @Counter INT = 0;
DECLARE @Formatted VARCHAR(50);
 
EXEC Utilities.IncrementAndFormat @Counter OUTPUT, 'ORD', @Formatted OUTPUT;
SELECT @Counter AS Counter, @Formatted AS Formatted;  -- 1, 'ORD-0001'
 
EXEC Utilities.IncrementAndFormat @Counter OUTPUT, 'ORD', @Formatted OUTPUT;
SELECT @Counter AS Counter, @Formatted AS Formatted;  -- 2, 'ORD-0002'

INOUT Complexity

INOUT parameters can make code harder to reason about because the same variable serves dual purposes. Use them when genuinely appropriate (accumulators, counters), but prefer separate IN and OUT parameters for clarity in most cases.

Default Parameter Values

Default values make parameters optional—when the caller omits them, the default applies. This enables flexible procedure signatures that work with minimal arguments while supporting detailed customization.

Design principles for defaults:

Use sensible defaults that work for common cases
Order parameters: required first, optional with defaults later
Consider NULL as a default for "apply no filter" scenarios
Document default values clearly

default_values.sql

-- Comprehensive example with defaults
CREATE PROCEDURE Reports.GetSalesReport
    -- Required parameters (no defaults)
    @Year INT,
    
    -- Optional parameters with defaults
    @Month INT = NULL,                    -- NULL = all months
    @Region VARCHAR(50) = NULL,           -- NULL = all regions
    @MinAmount DECIMAL(18,2) = 0,         -- Default: no minimum
    @MaxAmount DECIMAL(18,2) = NULL,      -- NULL = no maximum
    @IncludeReturns BIT = 0,              -- Default: exclude returns
    @SortBy VARCHAR(20) = 'Amount',       -- Default sort column
    @SortOrder VARCHAR(4) = 'DESC',       -- Default sort direction
    @PageSize INT = 50,                   -- Default page size
    @PageNumber INT = 1                   -- Default to first page
AS
BEGIN
    SET NOCOUNT ON;
    
    DECLARE @Offset INT = (@PageNumber - 1) * @PageSize;
    
    SELECT 
        s.SaleDate,
        s.Region,
        s.ProductName,
        s.Amount,
        s.SaleType
    FROM Sales.SalesData s
    WHERE YEAR(s.SaleDate) = @Year
      AND (@Month IS NULL OR MONTH(s.SaleDate) = @Month)
      AND (@Region IS NULL OR s.Region = @Region)
      AND s.Amount >= @MinAmount
      AND (@MaxAmount IS NULL OR s.Amount <= @MaxAmount)
      AND (@IncludeReturns = 1 OR s.SaleType <> 'Return')
    ORDER BY
        CASE WHEN @SortBy = 'Date' AND @SortOrder = 'ASC' THEN s.SaleDate END ASC,
        CASE WHEN @SortBy = 'Date' AND @SortOrder = 'DESC' THEN s.SaleDate END DESC,
        CASE WHEN @SortBy = 'Amount' AND @SortOrder = 'ASC' THEN s.Amount END ASC,
        CASE WHEN @SortBy = 'Amount' AND @SortOrder = 'DESC' THEN s.Amount END DESC
    OFFSET @Offset ROWS FETCH NEXT @PageSize ROWS ONLY;
END;
GO
 
-- Calling with only required parameter
EXEC Reports.GetSalesReport @Year = 2024;
 
-- Calling with some optional parameters
EXEC Reports.GetSalesReport 
    @Year = 2024,
    @Month = 6,
    @Region = 'West';
 
-- Calling with pagination
EXEC Reports.GetSalesReport 
    @Year = 2024,
    @PageSize = 20,
    @PageNumber = 3;
 
-- Full customization
EXEC Reports.GetSalesReport 
    @Year = 2024,
    @Month = NULL,
    @Region = 'East',
    @MinAmount = 1000,
    @MaxAmount = 50000,
    @IncludeReturns = 1,
    @SortBy = 'Date',
    @SortOrder = 'ASC',
    @PageSize = 100,
    @PageNumber = 1;

Parameter Ordering Strategy

Order parameters by: 1) Required parameters first, 2) Optional parameters with defaults after, 3) Output parameters last. This allows callers to use positional arguments for common cases and named parameters for customization.

Parameter Data Types

Choosing appropriate data types for parameters ensures type safety, prevents data loss, and enables the database to optimize execution.

Type matching principles:

Match parameter types to column types they'll be compared against
Use sufficient precision for numeric types
Consider VARCHAR length carefully—too short truncates, too long wastes memory
Be consistent across related procedures

Common Parameter Types and Recommendations
Data Category	Recommended Type(s)	Avoid	Notes
Primary keys	INT, BIGINT	VARCHAR (for numeric IDs)	Use same type as table column
Money/Currency	DECIMAL(18,2) or MONEY	FLOAT, REAL	Never use floating-point for money
Short text	VARCHAR(50-100)	TEXT, VARCHAR(MAX)	Size appropriately
Long text	VARCHAR(MAX), TEXT	CHAR	Use for variable-length content
Boolean flags	BIT (SQL Server), BOOLEAN	INT, CHAR(1)	Native boolean types
Dates	DATE, DATETIME, TIMESTAMP	VARCHAR	Use native date types
JSON data	JSON, JSONB (PostgreSQL)	TEXT	Use JSON types if available

parameter_types.sql

SQL Server

-- Proper type usage examples
CREATE PROCEDURE Order.PlaceOrder
    -- IDs: Match table column types
    @CustomerID BIGINT,                    -- Same as Customers.CustomerID
    @ProductID INT,                        -- Same as Products.ProductID
    
    -- Numeric: Use appropriate precision
    @Quantity SMALLINT,                    -- Reasonable range for quantities
    @UnitPrice DECIMAL(18,4),              -- Extra precision for price calculations
    @DiscountPercent DECIMAL(5,2),         -- 0.00 to 100.00
    
    -- Money: DECIMAL or MONEY type
    @PaymentAmount MONEY,                  -- 4 decimal places, fixed
    
    -- Text: VARCHAR with reasonable limits
    @ShippingAddress NVARCHAR(500),        -- Unicode for internationalization
    @OrderNotes NVARCHAR(MAX) = NULL,      -- Long text, optional
    
    -- Flags: BIT for booleans
    @IsGift BIT = 0,
    @RequiresSignature BIT = 0,
    
    -- Dates: Use appropriate date types
    @RequestedDeliveryDate DATE,           -- Date only
    @OrderTimestamp DATETIME2 = NULL,      -- Will default to SYSDATETIME() if NULL
    
    -- Output: Matching return types
    @OrderID BIGINT OUTPUT,
    @EstimatedTotal MONEY OUTPUT
AS
BEGIN
    SET NOCOUNT ON;
    -- Implementation...
END;
GO
 
-- Table-Valued Parameters (SQL Server)
-- Define a custom table type
CREATE TYPE Sales.OrderLineType AS TABLE (
    ProductID INT,
    Quantity SMALLINT,
    UnitPrice DECIMAL(18,4)
);
GO
 
-- Procedure accepting multiple items
CREATE PROCEDURE Sales.CreateMultiItemOrder
    @CustomerID BIGINT,
    @OrderLines Sales.OrderLineType READONLY,  -- Table-valued parameter
    @OrderID BIGINT OUTPUT
AS
BEGIN
    SET NOCOUNT ON;
    
    -- Insert order header
    INSERT INTO Sales.Orders (CustomerID, OrderDate, Status)
    VALUES (@CustomerID, SYSDATETIME(), 'New');
    
    SET @OrderID = SCOPE_IDENTITY();
    
    -- Insert all lines from table parameter
    INSERT INTO Sales.OrderLines (OrderID, ProductID, Quantity, UnitPrice)
    SELECT @OrderID, ProductID, Quantity, UnitPrice
    FROM @OrderLines;
    
    -- Update order total
    UPDATE Sales.Orders
    SET TotalAmount = (
        SELECT SUM(Quantity * UnitPrice) FROM Sales.OrderLines WHERE OrderID = @OrderID
    )
    WHERE OrderID = @OrderID;
END;
GO
 
-- Calling with table-valued parameter
DECLARE @Lines Sales.OrderLineType;
INSERT INTO @Lines VALUES (101, 2, 29.99), (205, 1, 149.99), (308, 3, 9.99);
 
DECLARE @NewOrderID BIGINT;
EXEC Sales.CreateMultiItemOrder @CustomerID = 1001, @OrderLines = @Lines, @OrderID = @NewOrderID OUTPUT;

Type Mismatch Performance Penalty

When parameter types don't match column types, the database may perform implicit conversions. This can prevent index usage—e.g., comparing a VARCHAR parameter to an INT column forces table scans. Always match types exactly.

Parameter Validation

Never trust input parameters. Validate all inputs at the start of the procedure to fail fast with meaningful errors rather than cryptic database errors or data corruption.

Essential validations:

NULL checks for required parameters
Range validation for numeric values
Length/format validation for strings
Referential integrity checks (does this ID exist?)
Business rule validation (is this value allowed?)

parameter_validation.sql

SQL Server

CREATE PROCEDURE HR.UpdateEmployeeSalary
    @EmployeeID INT,
    @NewSalary DECIMAL(18,2),
    @EffectiveDate DATE,
    @Reason NVARCHAR(500),
    @ApprovedBy NVARCHAR(100),
    @Success BIT OUTPUT,
    @ErrorCode VARCHAR(20) OUTPUT,
    @ErrorMessage NVARCHAR(500) OUTPUT
AS
BEGIN
    SET NOCOUNT ON;
    
    -- Initialize outputs
    SET @Success = 0;
    SET @ErrorCode = NULL;
    SET @ErrorMessage = NULL;
    
    --======================================
    -- PARAMETER VALIDATION BLOCK
    --======================================
    
    -- Required parameter checks
    IF @EmployeeID IS NULL
    BEGIN
        SET @ErrorCode = 'NULL_EMPLOYEE';
        SET @ErrorMessage = 'EmployeeID is required';
        RETURN;
    END
    
    IF @NewSalary IS NULL
    BEGIN
        SET @ErrorCode = 'NULL_SALARY';
        SET @ErrorMessage = 'NewSalary is required';
        RETURN;
    END
    
    IF @EffectiveDate IS NULL
    BEGIN
        SET @ErrorCode = 'NULL_DATE';
        SET @ErrorMessage = 'EffectiveDate is required';
        RETURN;
    END
    
    IF @Reason IS NULL OR LEN(TRIM(@Reason)) < 10
    BEGIN
        SET @ErrorCode = 'INVALID_REASON';
        SET @ErrorMessage = 'Reason must be at least 10 characters';
        RETURN;
    END
    
    -- Range validation
    IF @NewSalary <= 0
    BEGIN
        SET @ErrorCode = 'INVALID_SALARY';
        SET @ErrorMessage = 'Salary must be positive';
        RETURN;
    END
    
    IF @NewSalary > 10000000  -- $10M cap
    BEGIN
        SET @ErrorCode = 'SALARY_EXCEEDS_MAX';
        SET @ErrorMessage = 'Salary exceeds maximum allowed ($10,000,000)';
        RETURN;
    END
    
    -- Date validation
    IF @EffectiveDate < CAST(GETDATE() AS DATE)
    BEGIN
        SET @ErrorCode = 'PAST_DATE';
        SET @ErrorMessage = 'EffectiveDate cannot be in the past';
        RETURN;
    END
    
    IF @EffectiveDate > DATEADD(YEAR, 1, GETDATE())
    BEGIN
        SET @ErrorCode = 'DATE_TOO_FAR';
        SET @ErrorMessage = 'EffectiveDate cannot be more than 1 year in future';
        RETURN;
    END
    
    -- Referential integrity checks
    DECLARE @CurrentSalary DECIMAL(18,2);
    DECLARE @EmployeeName NVARCHAR(200);
    DECLARE @EmployeeStatus VARCHAR(20);
    
    SELECT @CurrentSalary = Salary, 
           @EmployeeName = FirstName + ' ' + LastName,
           @EmployeeStatus = Status
    FROM HR.Employees
    WHERE EmployeeID = @EmployeeID;
    
    IF @CurrentSalary IS NULL
    BEGIN
        SET @ErrorCode = 'EMPLOYEE_NOT_FOUND';
        SET @ErrorMessage = 'Employee ID ' + CAST(@EmployeeID AS VARCHAR) + ' not found';
        RETURN;
    END
    
    IF @EmployeeStatus <> 'Active'
    BEGIN
        SET @ErrorCode = 'EMPLOYEE_INACTIVE';
        SET @ErrorMessage = 'Cannot update salary for inactive employee';
        RETURN;
    END
    
    -- Business rule validation
    DECLARE @MaxIncrease DECIMAL(5,2) = 0.50;  -- 50% max increase
    
    IF @NewSalary > @CurrentSalary * (1 + @MaxIncrease)
    BEGIN
        SET @ErrorCode = 'EXCESSIVE_INCREASE';
        SET @ErrorMessage = 'Salary increase exceeds 50% limit. Current: $' + 
            CAST(@CurrentSalary AS VARCHAR) + ', Proposed: $' + CAST(@NewSalary AS VARCHAR);
        RETURN;
    END
    
    --======================================
    -- MAIN LOGIC (after validation passes)
    --======================================
    
    BEGIN TRY
        BEGIN TRANSACTION;
        
        -- Record salary history
        INSERT INTO HR.SalaryHistory (EmployeeID, OldSalary, NewSalary, EffectiveDate, Reason, ApprovedBy)
        VALUES (@EmployeeID, @CurrentSalary, @NewSalary, @EffectiveDate, @Reason, @ApprovedBy);
        
        -- Update if effective immediately
        IF @EffectiveDate = CAST(GETDATE() AS DATE)
        BEGIN
            UPDATE HR.Employees
            SET Salary = @NewSalary, LastSalaryUpdate = GETDATE()
            WHERE EmployeeID = @EmployeeID;
        END
        
        COMMIT TRANSACTION;
        SET @Success = 1;
        
    END TRY
    BEGIN CATCH
        IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION;
        SET @ErrorCode = 'DB_ERROR';
        SET @ErrorMessage = ERROR_MESSAGE();
    END CATCH
END;

Validation Pattern

Create a consistent validation pattern: check required fields first, then format/range, then referential integrity, then business rules. Return immediately on failure with specific error codes that calling applications can handle.

Summary: Mastering Procedure Parameters

We've covered stored procedure parameters comprehensively. Here are the essential takeaways:

Key Takeaways

•Three modes define data direction — IN for input, OUT for output, INOUT for bidirectional. Syntax varies by database.
•IN parameters are most common — Pass criteria, identifiers, and configuration values into procedures.
•OUT parameters return results — Use for status codes, computed values, and generated IDs. Essential pattern for error handling.
•INOUT enables bidirectional flow — Useful for accumulators and counters, but use sparingly due to added complexity.
•Default values enable flexibility — Make non-essential parameters optional. Order required before optional.
•Type matching is critical — Match parameter types to column types exactly to enable index usage and prevent implicit conversions.
•Validate all inputs — Check for NULL, validate ranges and formats, verify referential integrity, and enforce business rules before proceeding.

What's next:

With parameters mastered, we move to control flow—the procedural constructs that enable decision-making and iteration within stored procedures. The next page covers IF/ELSE, CASE, WHILE loops, and cursors.

Page Complete

You now understand how to design procedure interfaces with IN, OUT, and INOUT parameters, how to use default values effectively, and how to validate inputs rigorously. Next, we'll learn to control execution flow within procedures.

3 / 5

Loading learning content...

SQL Advanced FeaturesStored Procedures

Stored Procedures

LevelIntermediate

Duration90 mins

TopicStored Procedures

3 / 5

Parameters (IN, OUT, INOUT)

The Procedure Interface

What You Will Learn

The Three Parameter Modes

Stored procedure parameters come in three fundamental modes, each defining how data flows:

IN Parameters (Input Only)

Pass values into the procedure
Cannot be modified by the procedure
The calling application provides the value
Most common parameter type

OUT Parameters (Output Only)

Receive values from the procedure
Procedure sets the value, caller reads it
Caller provides a variable to receive the output
Used for returning computation results, status codes, error messages

INOUT Parameters (Bidirectional)

Pass values in, receive modified values out
Caller provides initial value, procedure can modify it
Final value available to caller after execution
Useful for counters, accumulators, or in-place transformations

Parameter Mode Comparison
Mode	Data Direction	Caller Provides	Procedure Can Modify	Typical Use
IN	Caller → Procedure	Value	No	Search criteria, IDs, configuration values
OUT	Procedure → Caller	Variable (empty)	Yes (sets value)	Results, status codes, generated IDs
INOUT	Both directions	Initial value	Yes (modifies)	Counters, accumulators, transformations

Default Mode Varies by Database

IN Parameters: Passing Data In

IN parameters are the most common—they pass values from the calling application into the procedure. The procedure uses these values but cannot modify them.

Key characteristics:

Passed by value (in most systems)
Changes inside procedure don't affect caller's variable
Can be literal values, variables, or expressions
Must be provided unless a default is defined

in_parameters.sql

-- IN parameters: The default mode in SQL Server
CREATE PROCEDURE Sales.GetCustomerOrders
    @CustomerID INT,                    -- Required IN parameter
    @StartDate DATE = NULL,             -- Optional with default
    @EndDate DATE = NULL,               -- Optional with default
    @MaxResults INT = 100               -- Optional with default
AS
BEGIN
    SET NOCOUNT ON;
    
    -- Use parameters as read-only values
    SELECT TOP (@MaxResults)
        o.OrderID,
        o.OrderDate,
        o.TotalAmount,
        o.Status
    FROM Sales.Orders o
    WHERE o.CustomerID = @CustomerID
      AND (@StartDate IS NULL OR o.OrderDate >= @StartDate)
      AND (@EndDate IS NULL OR o.OrderDate <= @EndDate)
    ORDER BY o.OrderDate DESC;
END;
GO
 
-- Calling with all parameters
EXEC Sales.GetCustomerOrders 
    @CustomerID = 1001,
    @StartDate = '2024-01-01',
    @EndDate = '2024-12-31',
    @MaxResults = 50;
 
-- Calling with only required parameter (uses defaults)
EXEC Sales.GetCustomerOrders @CustomerID = 1001;
 
-- Calling with positional arguments (not recommended)
EXEC Sales.GetCustomerOrders 1001, '2024-01-01', '2024-12-31', 50;
 
-- Calling with mixed named/positional (named must come last)
EXEC Sales.GetCustomerOrders 1001, @MaxResults = 25;

OUT Parameters: Returning Data

Key characteristics:

Caller provides a variable to receive the value
Procedure sets the value (initial caller value is typically ignored)
Multiple OUT parameters allow returning multiple values
Common for status/error message patterns

out_parameters.sql

-- OUT parameters use the OUTPUT keyword
CREATE PROCEDURE Sales.CreateOrder
    @CustomerID INT,
    @ProductID INT,
    @Quantity INT,
    @OrderID INT OUTPUT,                  -- Returns generated order ID
    @TotalAmount DECIMAL(18,2) OUTPUT,    -- Returns calculated total
    @Success BIT OUTPUT,                  -- Returns success status
    @ErrorMessage NVARCHAR(500) OUTPUT    -- Returns error if any
AS
BEGIN
    SET NOCOUNT ON;
    
    DECLARE @UnitPrice DECIMAL(18,2);
    DECLARE @AvailableQty INT;
    
    BEGIN TRY
        -- Validate customer
        IF NOT EXISTS (SELECT 1 FROM Sales.Customers WHERE CustomerID = @CustomerID)
        BEGIN
            SET @Success = 0;
            SET @ErrorMessage = 'Customer not found';
            SET @OrderID = NULL;
            SET @TotalAmount = NULL;
            RETURN;
        END
        
        -- Get product details and validate stock
        SELECT @UnitPrice = UnitPrice, @AvailableQty = StockQuantity
        FROM Products
        WHERE ProductID = @ProductID;
        
        IF @AvailableQty < @Quantity
        BEGIN
            SET @Success = 0;
            SET @ErrorMessage = 'Insufficient stock. Available: ' + CAST(@AvailableQty AS NVARCHAR);
            SET @OrderID = NULL;
            SET @TotalAmount = NULL;
            RETURN;
        END
        
        -- Calculate total
        SET @TotalAmount = @UnitPrice * @Quantity;
        
        -- Create order
        BEGIN TRANSACTION;
        
        INSERT INTO Sales.Orders (CustomerID, OrderDate, TotalAmount, Status)
        VALUES (@CustomerID, GETDATE(), @TotalAmount, 'Pending');
        
        SET @OrderID = SCOPE_IDENTITY();  -- Get generated ID
        
        INSERT INTO Sales.OrderLines (OrderID, ProductID, Quantity, UnitPrice)
        VALUES (@OrderID, @ProductID, @Quantity, @UnitPrice);
        
        UPDATE Products
        SET StockQuantity = StockQuantity - @Quantity
        WHERE ProductID = @ProductID;
        
        COMMIT TRANSACTION;
        
        SET @Success = 1;
        SET @ErrorMessage = NULL;
        
    END TRY
    BEGIN CATCH
        IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION;
        SET @Success = 0;
        SET @ErrorMessage = ERROR_MESSAGE();
        SET @OrderID = NULL;
        SET @TotalAmount = NULL;
    END CATCH
END;
GO
 
-- Calling with OUTPUT parameters
DECLARE @NewOrderID INT;
DECLARE @Total DECIMAL(18,2);
DECLARE @OK BIT;
DECLARE @Err NVARCHAR(500);
 
EXEC Sales.CreateOrder
    @CustomerID = 1001,
    @ProductID = 5001,
    @Quantity = 3,
    @OrderID = @NewOrderID OUTPUT,      -- OUTPUT keyword required
    @TotalAmount = @Total OUTPUT,
    @Success = @OK OUTPUT,
    @ErrorMessage = @Err OUTPUT;
 
-- Check results
IF @OK = 1
    SELECT 'Order created' AS Status, @NewOrderID AS OrderID, @Total AS Total;
ELSE
    SELECT 'Order failed' AS Status, @Err AS Error;

Output Parameter Pattern

A common pattern uses three standard OUT parameters: Success (boolean), ErrorCode (string/int), and ErrorMessage (text). This provides consistent error handling across all procedures in a codebase.

INOUT Parameters: Bidirectional Data Flow

Key characteristics:

Combines IN and OUT behavior
Caller's variable is modified by the procedure
Useful for accumulators, counters, or in-place transformations

inout_parameters.sql

-- SQL Server uses OUTPUT for both OUT and INOUT
-- The difference is whether you pass an initial value
CREATE PROCEDURE Utilities.AccumulateTotal
    @NewAmount DECIMAL(18,2),           -- IN: Value to add
    @RunningTotal DECIMAL(18,2) OUTPUT  -- INOUT: Accumulator
AS
BEGIN
    SET NOCOUNT ON;
    
    -- Read the current value, add to it, and set back
    SET @RunningTotal = ISNULL(@RunningTotal, 0) + @NewAmount;
    
    -- The modified value is returned to the caller
END;
GO
 
-- Example: Processing a batch of items with running total
DECLARE @Total DECIMAL(18,2) = 0;  -- Initial value provided
 
EXEC Utilities.AccumulateTotal @NewAmount = 100.00, @RunningTotal = @Total OUTPUT;
-- @Total is now 100.00
 
EXEC Utilities.AccumulateTotal @NewAmount = 250.50, @RunningTotal = @Total OUTPUT;
-- @Total is now 350.50
 
EXEC Utilities.AccumulateTotal @NewAmount = 75.25, @RunningTotal = @Total OUTPUT;
-- @Total is now 425.75
 
SELECT @Total AS FinalTotal;  -- 425.75
 
-- More practical example: Counter with formatting
CREATE PROCEDURE Utilities.IncrementAndFormat
    @Value INT OUTPUT,
    @Prefix VARCHAR(10) = 'Item',
    @FormattedOutput VARCHAR(50) OUTPUT
AS
BEGIN
    SET NOCOUNT ON;
    
    -- Increment the counter (INOUT behavior)
    SET @Value = ISNULL(@Value, 0) + 1;
    
    -- Format and output
    SET @FormattedOutput = @Prefix + '-' + RIGHT('0000' + CAST(@Value AS VARCHAR), 4);
END;
GO
 
-- Usage
DECLARE @Counter INT = 0;
DECLARE @Formatted VARCHAR(50);
 
EXEC Utilities.IncrementAndFormat @Counter OUTPUT, 'ORD', @Formatted OUTPUT;
SELECT @Counter AS Counter, @Formatted AS Formatted;  -- 1, 'ORD-0001'
 
EXEC Utilities.IncrementAndFormat @Counter OUTPUT, 'ORD', @Formatted OUTPUT;
SELECT @Counter AS Counter, @Formatted AS Formatted;  -- 2, 'ORD-0002'

INOUT Complexity

Default Parameter Values

Design principles for defaults:

Use sensible defaults that work for common cases
Order parameters: required first, optional with defaults later
Consider NULL as a default for "apply no filter" scenarios
Document default values clearly

default_values.sql

-- Comprehensive example with defaults
CREATE PROCEDURE Reports.GetSalesReport
    -- Required parameters (no defaults)
    @Year INT,
    
    -- Optional parameters with defaults
    @Month INT = NULL,                    -- NULL = all months
    @Region VARCHAR(50) = NULL,           -- NULL = all regions
    @MinAmount DECIMAL(18,2) = 0,         -- Default: no minimum
    @MaxAmount DECIMAL(18,2) = NULL,      -- NULL = no maximum
    @IncludeReturns BIT = 0,              -- Default: exclude returns
    @SortBy VARCHAR(20) = 'Amount',       -- Default sort column
    @SortOrder VARCHAR(4) = 'DESC',       -- Default sort direction
    @PageSize INT = 50,                   -- Default page size
    @PageNumber INT = 1                   -- Default to first page
AS
BEGIN
    SET NOCOUNT ON;
    
    DECLARE @Offset INT = (@PageNumber - 1) * @PageSize;
    
    SELECT 
        s.SaleDate,
        s.Region,
        s.ProductName,
        s.Amount,
        s.SaleType
    FROM Sales.SalesData s
    WHERE YEAR(s.SaleDate) = @Year
      AND (@Month IS NULL OR MONTH(s.SaleDate) = @Month)
      AND (@Region IS NULL OR s.Region = @Region)
      AND s.Amount >= @MinAmount
      AND (@MaxAmount IS NULL OR s.Amount <= @MaxAmount)
      AND (@IncludeReturns = 1 OR s.SaleType <> 'Return')
    ORDER BY
        CASE WHEN @SortBy = 'Date' AND @SortOrder = 'ASC' THEN s.SaleDate END ASC,
        CASE WHEN @SortBy = 'Date' AND @SortOrder = 'DESC' THEN s.SaleDate END DESC,
        CASE WHEN @SortBy = 'Amount' AND @SortOrder = 'ASC' THEN s.Amount END ASC,
        CASE WHEN @SortBy = 'Amount' AND @SortOrder = 'DESC' THEN s.Amount END DESC
    OFFSET @Offset ROWS FETCH NEXT @PageSize ROWS ONLY;
END;
GO
 
-- Calling with only required parameter
EXEC Reports.GetSalesReport @Year = 2024;
 
-- Calling with some optional parameters
EXEC Reports.GetSalesReport 
    @Year = 2024,
    @Month = 6,
    @Region = 'West';
 
-- Calling with pagination
EXEC Reports.GetSalesReport 
    @Year = 2024,
    @PageSize = 20,
    @PageNumber = 3;
 
-- Full customization
EXEC Reports.GetSalesReport 
    @Year = 2024,
    @Month = NULL,
    @Region = 'East',
    @MinAmount = 1000,
    @MaxAmount = 50000,
    @IncludeReturns = 1,
    @SortBy = 'Date',
    @SortOrder = 'ASC',
    @PageSize = 100,
    @PageNumber = 1;

Parameter Ordering Strategy

Parameter Data Types

Choosing appropriate data types for parameters ensures type safety, prevents data loss, and enables the database to optimize execution.

Type matching principles:

Match parameter types to column types they'll be compared against
Use sufficient precision for numeric types
Consider VARCHAR length carefully—too short truncates, too long wastes memory
Be consistent across related procedures

Common Parameter Types and Recommendations
Data Category	Recommended Type(s)	Avoid	Notes
Primary keys	INT, BIGINT	VARCHAR (for numeric IDs)	Use same type as table column
Money/Currency	DECIMAL(18,2) or MONEY	FLOAT, REAL	Never use floating-point for money
Short text	VARCHAR(50-100)	TEXT, VARCHAR(MAX)	Size appropriately
Long text	VARCHAR(MAX), TEXT	CHAR	Use for variable-length content
Boolean flags	BIT (SQL Server), BOOLEAN	INT, CHAR(1)	Native boolean types
Dates	DATE, DATETIME, TIMESTAMP	VARCHAR	Use native date types
JSON data	JSON, JSONB (PostgreSQL)	TEXT	Use JSON types if available

parameter_types.sql

SQL Server

-- Proper type usage examples
CREATE PROCEDURE Order.PlaceOrder
    -- IDs: Match table column types
    @CustomerID BIGINT,                    -- Same as Customers.CustomerID
    @ProductID INT,                        -- Same as Products.ProductID
    
    -- Numeric: Use appropriate precision
    @Quantity SMALLINT,                    -- Reasonable range for quantities
    @UnitPrice DECIMAL(18,4),              -- Extra precision for price calculations
    @DiscountPercent DECIMAL(5,2),         -- 0.00 to 100.00
    
    -- Money: DECIMAL or MONEY type
    @PaymentAmount MONEY,                  -- 4 decimal places, fixed
    
    -- Text: VARCHAR with reasonable limits
    @ShippingAddress NVARCHAR(500),        -- Unicode for internationalization
    @OrderNotes NVARCHAR(MAX) = NULL,      -- Long text, optional
    
    -- Flags: BIT for booleans
    @IsGift BIT = 0,
    @RequiresSignature BIT = 0,
    
    -- Dates: Use appropriate date types
    @RequestedDeliveryDate DATE,           -- Date only
    @OrderTimestamp DATETIME2 = NULL,      -- Will default to SYSDATETIME() if NULL
    
    -- Output: Matching return types
    @OrderID BIGINT OUTPUT,
    @EstimatedTotal MONEY OUTPUT
AS
BEGIN
    SET NOCOUNT ON;
    -- Implementation...
END;
GO
 
-- Table-Valued Parameters (SQL Server)
-- Define a custom table type
CREATE TYPE Sales.OrderLineType AS TABLE (
    ProductID INT,
    Quantity SMALLINT,
    UnitPrice DECIMAL(18,4)
);
GO
 
-- Procedure accepting multiple items
CREATE PROCEDURE Sales.CreateMultiItemOrder
    @CustomerID BIGINT,
    @OrderLines Sales.OrderLineType READONLY,  -- Table-valued parameter
    @OrderID BIGINT OUTPUT
AS
BEGIN
    SET NOCOUNT ON;
    
    -- Insert order header
    INSERT INTO Sales.Orders (CustomerID, OrderDate, Status)
    VALUES (@CustomerID, SYSDATETIME(), 'New');
    
    SET @OrderID = SCOPE_IDENTITY();
    
    -- Insert all lines from table parameter
    INSERT INTO Sales.OrderLines (OrderID, ProductID, Quantity, UnitPrice)
    SELECT @OrderID, ProductID, Quantity, UnitPrice
    FROM @OrderLines;
    
    -- Update order total
    UPDATE Sales.Orders
    SET TotalAmount = (
        SELECT SUM(Quantity * UnitPrice) FROM Sales.OrderLines WHERE OrderID = @OrderID
    )
    WHERE OrderID = @OrderID;
END;
GO
 
-- Calling with table-valued parameter
DECLARE @Lines Sales.OrderLineType;
INSERT INTO @Lines VALUES (101, 2, 29.99), (205, 1, 149.99), (308, 3, 9.99);
 
DECLARE @NewOrderID BIGINT;
EXEC Sales.CreateMultiItemOrder @CustomerID = 1001, @OrderLines = @Lines, @OrderID = @NewOrderID OUTPUT;

Type Mismatch Performance Penalty

Parameter Validation

Never trust input parameters. Validate all inputs at the start of the procedure to fail fast with meaningful errors rather than cryptic database errors or data corruption.

Essential validations:

NULL checks for required parameters
Range validation for numeric values
Length/format validation for strings
Referential integrity checks (does this ID exist?)
Business rule validation (is this value allowed?)

parameter_validation.sql

SQL Server

CREATE PROCEDURE HR.UpdateEmployeeSalary
    @EmployeeID INT,
    @NewSalary DECIMAL(18,2),
    @EffectiveDate DATE,
    @Reason NVARCHAR(500),
    @ApprovedBy NVARCHAR(100),
    @Success BIT OUTPUT,
    @ErrorCode VARCHAR(20) OUTPUT,
    @ErrorMessage NVARCHAR(500) OUTPUT
AS
BEGIN
    SET NOCOUNT ON;
    
    -- Initialize outputs
    SET @Success = 0;
    SET @ErrorCode = NULL;
    SET @ErrorMessage = NULL;
    
    --======================================
    -- PARAMETER VALIDATION BLOCK
    --======================================
    
    -- Required parameter checks
    IF @EmployeeID IS NULL
    BEGIN
        SET @ErrorCode = 'NULL_EMPLOYEE';
        SET @ErrorMessage = 'EmployeeID is required';
        RETURN;
    END
    
    IF @NewSalary IS NULL
    BEGIN
        SET @ErrorCode = 'NULL_SALARY';
        SET @ErrorMessage = 'NewSalary is required';
        RETURN;
    END
    
    IF @EffectiveDate IS NULL
    BEGIN
        SET @ErrorCode = 'NULL_DATE';
        SET @ErrorMessage = 'EffectiveDate is required';
        RETURN;
    END
    
    IF @Reason IS NULL OR LEN(TRIM(@Reason)) < 10
    BEGIN
        SET @ErrorCode = 'INVALID_REASON';
        SET @ErrorMessage = 'Reason must be at least 10 characters';
        RETURN;
    END
    
    -- Range validation
    IF @NewSalary <= 0
    BEGIN
        SET @ErrorCode = 'INVALID_SALARY';
        SET @ErrorMessage = 'Salary must be positive';
        RETURN;
    END
    
    IF @NewSalary > 10000000  -- $10M cap
    BEGIN
        SET @ErrorCode = 'SALARY_EXCEEDS_MAX';
        SET @ErrorMessage = 'Salary exceeds maximum allowed ($10,000,000)';
        RETURN;
    END
    
    -- Date validation
    IF @EffectiveDate < CAST(GETDATE() AS DATE)
    BEGIN
        SET @ErrorCode = 'PAST_DATE';
        SET @ErrorMessage = 'EffectiveDate cannot be in the past';
        RETURN;
    END
    
    IF @EffectiveDate > DATEADD(YEAR, 1, GETDATE())
    BEGIN
        SET @ErrorCode = 'DATE_TOO_FAR';
        SET @ErrorMessage = 'EffectiveDate cannot be more than 1 year in future';
        RETURN;
    END
    
    -- Referential integrity checks
    DECLARE @CurrentSalary DECIMAL(18,2);
    DECLARE @EmployeeName NVARCHAR(200);
    DECLARE @EmployeeStatus VARCHAR(20);
    
    SELECT @CurrentSalary = Salary, 
           @EmployeeName = FirstName + ' ' + LastName,
           @EmployeeStatus = Status
    FROM HR.Employees
    WHERE EmployeeID = @EmployeeID;
    
    IF @CurrentSalary IS NULL
    BEGIN
        SET @ErrorCode = 'EMPLOYEE_NOT_FOUND';
        SET @ErrorMessage = 'Employee ID ' + CAST(@EmployeeID AS VARCHAR) + ' not found';
        RETURN;
    END
    
    IF @EmployeeStatus <> 'Active'
    BEGIN
        SET @ErrorCode = 'EMPLOYEE_INACTIVE';
        SET @ErrorMessage = 'Cannot update salary for inactive employee';
        RETURN;
    END
    
    -- Business rule validation
    DECLARE @MaxIncrease DECIMAL(5,2) = 0.50;  -- 50% max increase
    
    IF @NewSalary > @CurrentSalary * (1 + @MaxIncrease)
    BEGIN
        SET @ErrorCode = 'EXCESSIVE_INCREASE';
        SET @ErrorMessage = 'Salary increase exceeds 50% limit. Current: $' + 
            CAST(@CurrentSalary AS VARCHAR) + ', Proposed: $' + CAST(@NewSalary AS VARCHAR);
        RETURN;
    END
    
    --======================================
    -- MAIN LOGIC (after validation passes)
    --======================================
    
    BEGIN TRY
        BEGIN TRANSACTION;
        
        -- Record salary history
        INSERT INTO HR.SalaryHistory (EmployeeID, OldSalary, NewSalary, EffectiveDate, Reason, ApprovedBy)
        VALUES (@EmployeeID, @CurrentSalary, @NewSalary, @EffectiveDate, @Reason, @ApprovedBy);
        
        -- Update if effective immediately
        IF @EffectiveDate = CAST(GETDATE() AS DATE)
        BEGIN
            UPDATE HR.Employees
            SET Salary = @NewSalary, LastSalaryUpdate = GETDATE()
            WHERE EmployeeID = @EmployeeID;
        END
        
        COMMIT TRANSACTION;
        SET @Success = 1;
        
    END TRY
    BEGIN CATCH
        IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION;
        SET @ErrorCode = 'DB_ERROR';
        SET @ErrorMessage = ERROR_MESSAGE();
    END CATCH
END;

Validation Pattern

Summary: Mastering Procedure Parameters

We've covered stored procedure parameters comprehensively. Here are the essential takeaways:

Key Takeaways

•Three modes define data direction — IN for input, OUT for output, INOUT for bidirectional. Syntax varies by database.
•IN parameters are most common — Pass criteria, identifiers, and configuration values into procedures.
•OUT parameters return results — Use for status codes, computed values, and generated IDs. Essential pattern for error handling.
•INOUT enables bidirectional flow — Useful for accumulators and counters, but use sparingly due to added complexity.
•Default values enable flexibility — Make non-essential parameters optional. Order required before optional.
•Type matching is critical — Match parameter types to column types exactly to enable index usage and prevent implicit conversions.
•Validate all inputs — Check for NULL, validate ranges and formats, verify referential integrity, and enforce business rules before proceeding.

What's next:

Page Complete

3 / 5