SQL Stored Procedures: Creating and Calling

Key Insights

Stored procedures encapsulate SQL logic on the database server, providing better performance through execution plan caching and reduced network traffic compared to sending multiple ad-hoc queries from application code.
Parameter handling varies significantly between database systems—SQL Server uses OUTPUT parameters, PostgreSQL uses INOUT and function return values, while MySQL supports both IN and OUT parameters with different calling conventions.
Always use parameterized stored procedures to prevent SQL injection, implement proper error handling with TRY-CATCH blocks, and avoid cursors in favor of set-based operations for better performance.

Introduction to Stored Procedures

Stored procedures are precompiled SQL statements stored directly in your database. They act as reusable functions that encapsulate business logic, data validation, and complex queries in a single callable unit.

The primary benefits are tangible: execution plans are cached after the first run, reducing compilation overhead. Network traffic decreases because you send a single procedure call instead of multiple round-trips with individual queries. Security improves through controlled data access—you can grant execute permissions on procedures without exposing underlying tables.

Use stored procedures for complex business logic that touches multiple tables, batch operations, or scenarios requiring transaction control. Skip them for simple CRUD operations where ORMs provide sufficient abstraction, or when you need maximum portability across database systems since syntax varies considerably.

Basic Stored Procedure Syntax

The syntax differs across database platforms, but the concept remains consistent. Here’s how to create a basic procedure in the three major systems.

SQL Server:

CREATE PROCEDURE GetAllCustomers
AS
BEGIN
    SELECT CustomerID, CustomerName, Email, CreatedDate
    FROM Customers
    ORDER BY CustomerName;
END;

MySQL:

DELIMITER //
CREATE PROCEDURE GetAllCustomers()
BEGIN
    SELECT CustomerID, CustomerName, Email, CreatedDate
    FROM Customers
    ORDER BY CustomerName;
END //
DELIMITER ;

MySQL requires the DELIMITER change because the procedure body contains semicolons. We temporarily switch to // as the statement terminator.

PostgreSQL:

CREATE OR REPLACE PROCEDURE GetAllCustomers()
LANGUAGE plpgsql
AS $$
BEGIN
    -- PostgreSQL procedures don't return result sets directly
    -- Use functions for that purpose
    RAISE NOTICE 'Use functions for SELECT queries in PostgreSQL';
END;
$$;

PostgreSQL distinguishes between procedures and functions. For returning result sets, use CREATE FUNCTION instead:

CREATE OR REPLACE FUNCTION GetAllCustomers()
RETURNS TABLE(CustomerID INT, CustomerName VARCHAR, Email VARCHAR, CreatedDate DATE)
LANGUAGE plpgsql
AS $$
BEGIN
    RETURN QUERY
    SELECT c.CustomerID, c.CustomerName, c.Email, c.CreatedDate
    FROM Customers c
    ORDER BY c.CustomerName;
END;
$$;

Working with Parameters

Parameters make stored procedures flexible and reusable. They come in three flavors: INPUT (data going in), OUTPUT (data coming out), and INOUT (both directions).

Input Parameters (SQL Server):

CREATE PROCEDURE GetCustomersByDateRange
    @StartDate DATE,
    @EndDate DATE
AS
BEGIN
    SELECT CustomerID, CustomerName, Email, CreatedDate
    FROM Customers
    WHERE CreatedDate BETWEEN @StartDate AND @EndDate
    ORDER BY CreatedDate DESC;
END;

Output Parameters (SQL Server):

CREATE PROCEDURE CreateCustomerWithCount
    @CustomerName NVARCHAR(100),
    @Email NVARCHAR(100),
    @NewCustomerID INT OUTPUT,
    @TotalCustomers INT OUTPUT
AS
BEGIN
    INSERT INTO Customers (CustomerName, Email, CreatedDate)
    VALUES (@CustomerName, @Email, GETDATE());
    
    SET @NewCustomerID = SCOPE_IDENTITY();
    
    SELECT @TotalCustomers = COUNT(*)
    FROM Customers;
END;

MySQL with INOUT:

DELIMITER //
CREATE PROCEDURE UpdateCustomerStatus(
    IN customer_id INT,
    IN new_status VARCHAR(20),
    OUT rows_affected INT
)
BEGIN
    UPDATE Customers
    SET Status = new_status
    WHERE CustomerID = customer_id;
    
    SET rows_affected = ROW_COUNT();
END //
DELIMITER ;

Control Flow and Logic

Stored procedures support variables, conditional logic, and loops. This enables complex business rules without application-level code.

SQL Server Example with Conditional Logic:

CREATE PROCEDURE ManageCustomer
    @Action VARCHAR(10),
    @CustomerID INT = NULL,
    @CustomerName NVARCHAR(100) = NULL,
    @Email NVARCHAR(100) = NULL,
    @ResultMessage NVARCHAR(255) OUTPUT
AS
BEGIN
    SET NOCOUNT ON;
    
    DECLARE @RowCount INT;
    
    BEGIN TRY
        IF @Action = 'CREATE'
        BEGIN
            INSERT INTO Customers (CustomerName, Email, CreatedDate)
            VALUES (@CustomerName, @Email, GETDATE());
            
            SET @ResultMessage = 'Customer created successfully. ID: ' + 
                                CAST(SCOPE_IDENTITY() AS NVARCHAR);
        END
        ELSE IF @Action = 'UPDATE'
        BEGIN
            UPDATE Customers
            SET CustomerName = @CustomerName,
                Email = @Email
            WHERE CustomerID = @CustomerID;
            
            SET @RowCount = @@ROWCOUNT;
            
            IF @RowCount = 0
                SET @ResultMessage = 'Customer not found';
            ELSE
                SET @ResultMessage = 'Customer updated successfully';
        END
        ELSE IF @Action = 'DELETE'
        BEGIN
            DELETE FROM Customers
            WHERE CustomerID = @CustomerID;
            
            SET @RowCount = @@ROWCOUNT;
            
            IF @RowCount = 0
                SET @ResultMessage = 'Customer not found';
            ELSE
                SET @ResultMessage = 'Customer deleted successfully';
        END
        ELSE
        BEGIN
            SET @ResultMessage = 'Invalid action specified';
        END
    END TRY
    BEGIN CATCH
        SET @ResultMessage = 'Error: ' + ERROR_MESSAGE();
    END CATCH
END;

This procedure demonstrates variables (@RowCount), conditional branching, and error handling—all essential for production-grade stored procedures.

Calling Stored Procedures

Execution syntax varies by database and client library.

Direct SQL Execution:

-- SQL Server
EXEC GetCustomersByDateRange @StartDate = '2024-01-01', @EndDate = '2024-12-31';

-- With output parameters
DECLARE @NewID INT, @Total INT;
EXEC CreateCustomerWithCount 
    @CustomerName = 'Acme Corp',
    @Email = 'contact@acme.com',
    @NewCustomerID = @NewID OUTPUT,
    @TotalCustomers = @Total OUTPUT;
SELECT @NewID AS NewCustomerID, @Total AS TotalCustomers;

-- MySQL
CALL GetCustomersByDateRange('2024-01-01', '2024-12-31');

-- PostgreSQL (for functions)
SELECT * FROM GetAllCustomers();

Python with psycopg2 (PostgreSQL):

import psycopg2

conn = psycopg2.connect(
    host="localhost",
    database="mydb",
    user="user",
    password="password"
)

cursor = conn.cursor()

# Calling a function that returns a table
cursor.execute("SELECT * FROM GetAllCustomers()")
customers = cursor.fetchall()

for customer in customers:
    print(f"ID: {customer[0]}, Name: {customer[1]}")

cursor.close()
conn.close()

Python with pymysql (MySQL):

import pymysql

conn = pymysql.connect(
    host='localhost',
    user='user',
    password='password',
    database='mydb'
)

cursor = conn.cursor()

# Calling procedure with output parameter
args = (123, 'Active', 0)
cursor.callproc('UpdateCustomerStatus', args)

# Fetch output parameter
cursor.execute("SELECT @_UpdateCustomerStatus_2")
rows_affected = cursor.fetchone()[0]
print(f"Rows affected: {rows_affected}")

conn.commit()
cursor.close()
conn.close()

C# with ADO.NET (SQL Server):

using System.Data.SqlClient;

string connectionString = "Server=localhost;Database=mydb;Trusted_Connection=true;";

using (SqlConnection conn = new SqlConnection(connectionString))
{
    using (SqlCommand cmd = new SqlCommand("CreateCustomerWithCount", conn))
    {
        cmd.CommandType = CommandType.StoredProcedure;
        
        cmd.Parameters.AddWithValue("@CustomerName", "Acme Corp");
        cmd.Parameters.AddWithValue("@Email", "contact@acme.com");
        
        SqlParameter newIdParam = new SqlParameter("@NewCustomerID", SqlDbType.Int);
        newIdParam.Direction = ParameterDirection.Output;
        cmd.Parameters.Add(newIdParam);
        
        SqlParameter totalParam = new SqlParameter("@TotalCustomers", SqlDbType.Int);
        totalParam.Direction = ParameterDirection.Output;
        cmd.Parameters.Add(totalParam);
        
        conn.Open();
        cmd.ExecuteNonQuery();
        
        int newId = (int)newIdParam.Value;
        int total = (int)totalParam.Value;
        
        Console.WriteLine($"New ID: {newId}, Total: {total}");
    }
}

Best Practices and Common Pitfalls

Security: Never concatenate user input into dynamic SQL within procedures. Always use parameters. Even inside stored procedures, dynamic SQL with concatenation is vulnerable to SQL injection.

Naming Conventions: Use consistent prefixes like usp_ (user stored procedure) or organize by function: Customer_Create, Customer_Update. Avoid generic names like sp_ which SQL Server reserves for system procedures.

Transaction Handling: Wrap multi-statement operations in explicit transactions. Always include error handling that rolls back on failure:

CREATE PROCEDURE TransferFunds
    @FromAccount INT,
    @ToAccount INT,
    @Amount DECIMAL(10,2)
AS
BEGIN
    BEGIN TRANSACTION;
    BEGIN TRY
        UPDATE Accounts SET Balance = Balance - @Amount WHERE AccountID = @FromAccount;
        UPDATE Accounts SET Balance = Balance + @Amount WHERE AccountID = @ToAccount;
        COMMIT TRANSACTION;
    END TRY
    BEGIN CATCH
        ROLLBACK TRANSACTION;
        THROW;
    END CATCH
END;

Performance: Avoid cursors. They process row-by-row and kill performance. Use set-based operations instead. Keep procedures focused—one procedure should do one thing well.

Advanced Features

Returning Result Sets with Temporary Tables:

CREATE PROCEDURE GetCustomerOrderSummary
    @Year INT
AS
BEGIN
    CREATE TABLE #OrderSummary (
        CustomerID INT,
        CustomerName NVARCHAR(100),
        TotalOrders INT,
        TotalAmount DECIMAL(10,2)
    );
    
    INSERT INTO #OrderSummary
    SELECT 
        c.CustomerID,
        c.CustomerName,
        COUNT(o.OrderID) AS TotalOrders,
        SUM(o.OrderAmount) AS TotalAmount
    FROM Customers c
    LEFT JOIN Orders o ON c.CustomerID = o.CustomerID
    WHERE YEAR(o.OrderDate) = @Year OR o.OrderDate IS NULL
    GROUP BY c.CustomerID, c.CustomerName;
    
    SELECT * FROM #OrderSummary
    ORDER BY TotalAmount DESC;
    
    DROP TABLE #OrderSummary;
END;

Temporary tables are useful for complex multi-step transformations, but remember they consume tempdb resources. For simple queries, CTEs or derived tables perform better.

Stored procedures remain a powerful tool for encapsulating database logic. Use them judiciously where they provide clear benefits in performance, security, or code organization. Avoid overuse that creates tight coupling between your application and database implementation details.