SQL Stored Procedures

A stored procedure is a prepared set of SQL statements that is compiled, stored, and executed directly on the database server. Instead of sending complex or repetitive SQL queries from an application over the network repeatedly, you can package business logic into a stored procedure and call it by name.

Stored procedures can accept input parameters, return output parameters or result sets, execute conditional control-flow logic (IF/ELSE, WHILE), and perform transactional operations.

Why Use Stored Procedures?

1. Performance Improvement: Database engines parse, optimize, and compile stored procedures upon creation. Subsequent executions reuse the cached execution plan, reducing query parsing and compilation overhead.
2. Network Traffic Reduction: Instead of transmitting long multi-statement SQL scripts over the network, client applications send a short invocation command (e.g., EXECUTE ProcessOrder 101).
3. Security & Access Control: Users can be granted permission to execute a stored procedure without giving them direct SELECT, INSERT, UPDATE, or DELETE permissions on the underlying base tables.
4. Code Reusability & Centralized Business Logic: Encapsulates core database operations in one central place, ensuring that applications written in different languages (e.g., Python, Java, C#) apply identical business rules.
5. Protection Against SQL Injection: Parameterized input arguments in stored procedures naturally isolate user input from SQL code structure, helping mitigate SQL injection risks.

Basic Syntax & Examples

Syntax for creating and executing stored procedures varies across database engines.

1. SQL Server (T-SQL)

-- Creating a procedure with Input and Output Parameters
CREATE PROCEDURE GetCustomerOrders
    @CustomerID INT,                  -- Input Parameter
    @TotalOrders INT OUTPUT           -- Output Parameter
AS
BEGIN
    SET NOCOUNT ON;
    
    -- Retrieve customer orders
    SELECT OrderID, OrderDate, TotalAmount
    FROM Orders
    WHERE CustomerID = @CustomerID;
    
    -- Assign value to output parameter
    SELECT @TotalOrders = COUNT(*)
    FROM Orders
    WHERE CustomerID = @CustomerID;
END;
GO

Executing the Procedure:

DECLARE @OrderCount INT;

EXEC GetCustomerOrders 
    @CustomerID = 101, 
    @TotalOrders = @OrderCount OUTPUT;

SELECT @OrderCount AS CustomerOrderCount;

2. PostgreSQL (PL/pgSQL)

In PostgreSQL 11+, stored procedures support explicit transaction control (COMMIT and ROLLBACK inside the procedure body).

CREATE OR REPLACE PROCEDURE TransferFunds(
    sender_id INT,
    receiver_id INT,
    amount DECIMAL(10, 2)
)
LANGUAGE plpgsql
AS $$
BEGIN
    -- Deduct from sender
    UPDATE Accounts 
    SET Balance = Balance - amount 
    WHERE AccountID = sender_id;

    -- Add to receiver
    UPDATE Accounts 
    SET Balance = Balance + amount 
    WHERE AccountID = receiver_id;

    -- Commit transaction explicitly
    COMMIT;
END;
$$;

Executing the Procedure:

CALL TransferFunds(1001, 1002, 250.00);

3. MySQL / MariaDB

MySQL requires changing the statement DELIMITER temporarily so that semicolons inside the procedure body do not terminate the creation statement early.

DELIMITER //

CREATE PROCEDURE AddNewProduct(
    IN p_ProductName VARCHAR(100),
    IN p_Price DECIMAL(10, 2),
    OUT p_ProductID INT
)
BEGIN
    INSERT INTO Products (ProductName, Price)
    VALUES (p_ProductName, p_Price);
    
    SET p_ProductID = LAST_INSERT_ID();
END //

DELIMITER ;

Executing the Procedure:

CALL AddNewProduct('Wireless Mouse', 29.99, @NewID);
SELECT @NewID AS InsertedProductID;

Parameter Types

Stored procedure parameters generally fall into three categories:

Modifying and Dropping Procedures

Modifying an Existing Procedure

To update the SQL code inside a stored procedure without dropping permissions:

-- SQL Server
ALTER PROCEDURE ProcedureName ...

-- PostgreSQL / MySQL
CREATE OR REPLACE PROCEDURE ProcedureName ...

Dropping a Procedure

To delete a procedure from the database schema:

DROP PROCEDURE IF EXISTS ProcedureName;

Stored Procedures vs. User-Defined Functions (UDFs)