Stored Procedures Basics

Difficulty: Intermediate
Generated on: 2025-07-10 02:27:53
Category: SQL Cheatsheet for Database Development

SQL Stored Procedures Cheatsheet (Intermediate)

1. Quick Overview

What is it? A stored procedure is a precompiled set of SQL statements stored within the database. Think of it as a function or subroutine for your database.

When to use it?

Encapsulation: Group complex logic into a single unit.
Reusability: Execute the same logic from multiple locations (applications, other procedures, triggers).
Performance: Precompilation can improve execution speed.
Security: Grant permissions to execute the procedure, not directly on the underlying tables.
Data Integrity: Enforce business rules and constraints within the database.
Reduced Network Traffic: Only the procedure name and parameters are sent across the network, not the entire SQL code.

2. Syntax

General Structure:

-- MySQL/SQL Server/PostgreSQL/Oracle (Common Structure)
CREATE PROCEDURE procedure_name (
    IN/OUT/INOUT parameter_name datatype,  -- Parameter definitions
    ...
)
BEGIN
    -- SQL statements
    -- Logic, loops, conditionals, etc.
END;

Specific Database Syntax:

MySQL:

DELIMITER //  -- Change delimiter to avoid conflict with semicolons inside the procedure

CREATE PROCEDURE get_customer_by_id (IN customer_id INT)
BEGIN
    SELECT * FROM Customers WHERE CustomerID = customer_id;
END //

DELIMITER ;  -- Reset delimiter to default

-- Calling the procedure
CALL get_customer_by_id(123);

SQL Server:

CREATE PROCEDURE get_customer_by_id
    @customer_id INT
AS
BEGIN
    SELECT * FROM Customers WHERE CustomerID = @customer_id;
END;

-- Calling the procedure
EXEC get_customer_by_id @customer_id = 123;

PostgreSQL:

CREATE OR REPLACE PROCEDURE get_customer_by_id (customer_id INT)
LANGUAGE plpgsql
AS $$
BEGIN
    SELECT * FROM Customers WHERE CustomerID = customer_id;
END;
$$;

-- Calling the procedure
CALL get_customer_by_id(123);

Oracle:

CREATE OR REPLACE PROCEDURE get_customer_by_id (customer_id IN NUMBER)
AS
BEGIN
    FOR rec IN (SELECT * FROM Customers WHERE CustomerID = customer_id)
    LOOP
        DBMS_OUTPUT.PUT_LINE('Customer Name: ' || rec.CustomerName); -- Example output
    END LOOP;
END;
/

-- Enable output (for testing in SQL Developer/SQL*Plus)
SET SERVEROUTPUT ON;

-- Calling the procedure
EXECUTE get_customer_by_id(123);

Parameter Modes:

IN: Input parameter (default in many databases). The procedure receives a value.
OUT: Output parameter. The procedure returns a value through this parameter.
INOUT: Input and output parameter. The procedure receives a value and can modify it, returning the updated value.

Example with OUT Parameter (SQL Server):

CREATE PROCEDURE get_customer_name_by_id
    @customer_id INT,
    @customer_name VARCHAR(255) OUTPUT
AS
BEGIN
    SELECT @customer_name = CustomerName FROM Customers WHERE CustomerID = @customer_id;
END;

-- Calling the procedure
DECLARE @name VARCHAR(255);
EXEC get_customer_name_by_id @customer_id = 123, @customer_name = @name OUTPUT;
SELECT @name; -- Display the returned name

3. Common Use Cases

Data Validation: Validate data before inserting or updating.

-- Example: Check if product exists before adding to order (SQL Server)
CREATE PROCEDURE add_order_item
    @order_id INT,
    @product_id INT,
    @quantity INT
AS
BEGIN
    IF EXISTS (SELECT 1 FROM Products WHERE ProductID = @product_id)
    BEGIN
        -- Add the item to the order
        INSERT INTO OrderItems (OrderID, ProductID, Quantity) VALUES (@order_id, @product_id, @quantity);
    END
    ELSE
    BEGIN
        -- Raise an error or log the issue
        RAISERROR('Product not found', 16, 1);
        RETURN;
    END
END;

Batch Processing: Perform multiple operations in a single transaction.

-- Example: Update multiple customer addresses (MySQL)
CREATE PROCEDURE update_customer_addresses()
BEGIN
    START TRANSACTION;
    UPDATE Customers SET Address = 'New Address 1' WHERE CustomerID = 1;
    UPDATE Customers SET Address = 'New Address 2' WHERE CustomerID = 2;
    COMMIT;
END;

Reporting: Generate complex reports. (Often better handled by dedicated reporting tools, but useful for simpler reports).

-- Example: Generate a summary report of sales by product (SQL Server)
CREATE PROCEDURE get_sales_by_product
AS
BEGIN
    SELECT
        p.ProductName,
        SUM(oi.Quantity * p.Price) AS TotalSales
    FROM
        OrderItems oi
    JOIN
        Products p ON oi.ProductID = p.ProductID
    GROUP BY
        p.ProductName
    ORDER BY
        TotalSales DESC;
END;

Auditing: Log changes to data.

-- Example: Log updates to the Customers table (PostgreSQL)
CREATE OR REPLACE PROCEDURE log_customer_update(
    old_customer_id INT,
    old_customer_name VARCHAR(255),
    new_customer_id INT,
    new_customer_name VARCHAR(255)
)
LANGUAGE plpgsql
AS $$
BEGIN
    INSERT INTO CustomerAudit (CustomerID, OldName, NewName, UpdatedAt)
    VALUES (old_customer_id, old_customer_name, new_customer_name, NOW());
END;
$$;

-- Example trigger to call the procedure (PostgreSQL)
CREATE OR REPLACE FUNCTION customer_update_trigger()
RETURNS TRIGGER AS $$
BEGIN
    PERFORM log_customer_update(OLD.CustomerID, OLD.CustomerName, NEW.CustomerID, NEW.CustomerName);
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER customer_update_trigger
AFTER UPDATE ON Customers
FOR EACH ROW
EXECUTE FUNCTION customer_update_trigger();

4. Best Practices

Naming Conventions: Use a consistent naming convention (e.g., sp_ prefix in SQL Server, meaningful names).

Error Handling: Implement robust error handling using TRY...CATCH blocks (SQL Server), BEGIN...EXCEPTION blocks (PostgreSQL/Oracle), or similar mechanisms.

-- Example: Error handling in SQL Server
CREATE PROCEDURE update_product_price
    @product_id INT,
    @new_price DECIMAL(10, 2)
AS
BEGIN
    BEGIN TRY
        UPDATE Products SET Price = @new_price WHERE ProductID = @product_id;
    END TRY
    BEGIN CATCH
        -- Log the error
        INSERT INTO ErrorLog (ErrorMessage, ErrorTime) VALUES (ERROR_MESSAGE(), GETDATE());
        -- Re-raise the error (optional)
        THROW; -- SQL Server 2012+
        -- Or, for older versions:
        -- RAISERROR('Error updating product price', 16, 1);
    END CATCH
END;

Transaction Management: Use transactions to ensure atomicity and consistency.

-- Example: Transaction management in MySQL
CREATE PROCEDURE transfer_funds
    (IN from_account INT, IN to_account INT, IN amount DECIMAL(10, 2))
BEGIN
    START TRANSACTION;
    UPDATE Accounts SET Balance = Balance - amount WHERE AccountID = from_account;
    UPDATE Accounts SET Balance = Balance + amount WHERE AccountID = to_account;

    -- Check if the transfer was successful (e.g., sufficient funds)
    IF (SELECT Balance FROM Accounts WHERE AccountID = from_account) < 0 THEN
        ROLLBACK;
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Insufficient funds';
    ELSE
        COMMIT;
    END IF;
END;

Security:
- Grant EXECUTE permissions only to authorized users.
- Use parameterized queries to prevent SQL injection. (Stored procedures inherently provide this protection if parameters are used correctly).
- Avoid dynamic SQL if possible. If necessary, sanitize inputs carefully.
Performance:
- Indexes: Ensure appropriate indexes are in place for tables used in the procedure.
- Avoid Cursors: Cursors are generally slow. Use set-based operations whenever possible.
- Optimize Queries: Use the EXPLAIN statement (MySQL/PostgreSQL) or the execution plan viewer (SQL Server/Oracle) to analyze query performance.
- Procedure Cache: Stored procedures are typically cached, so subsequent executions are faster. However, excessive recompilation can degrade performance.
- Keep it Simple: Break down complex procedures into smaller, more manageable units.
Code Formatting: Use consistent indentation and formatting for readability.
Comments: Add comments to explain the purpose of the procedure and complex logic.

5. Examples

Sample Data:

-- Create Customers Table
CREATE TABLE Customers (
    CustomerID INT PRIMARY KEY,
    CustomerName VARCHAR(255),
    City VARCHAR(255)
);

-- Insert Sample Data
INSERT INTO Customers (CustomerID, CustomerName, City) VALUES
(1, 'John Doe', 'New York'),
(2, 'Jane Smith', 'London'),
(3, 'David Lee', 'Paris');

-- Create Products Table
CREATE TABLE Products (
    ProductID INT PRIMARY KEY,
    ProductName VARCHAR(255),
    Price DECIMAL(10, 2)
);

-- Insert Sample Data
INSERT INTO Products (ProductID, ProductName, Price) VALUES
(101, 'Laptop', 1200.00),
(102, 'Mouse', 25.00),
(103, 'Keyboard', 75.00);

-- Create Orders Table
CREATE TABLE Orders (
    OrderID INT PRIMARY KEY,
    CustomerID INT,
    OrderDate DATE,
    FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID)
);

-- Insert Sample Data
INSERT INTO Orders (OrderID, CustomerID, OrderDate) VALUES
(1001, 1, '2023-10-26'),
(1002, 2, '2023-10-26');

-- Create OrderItems Table
CREATE TABLE OrderItems (
    OrderItemID INT PRIMARY KEY,
    OrderID INT,
    ProductID INT,
    Quantity INT,
    FOREIGN KEY (OrderID) REFERENCES Orders(OrderID),
    FOREIGN KEY (ProductID) REFERENCES Products(ProductID)
);

-- Insert Sample Data
INSERT INTO OrderItems (OrderItemID, OrderID, ProductID, Quantity) VALUES
(2001, 1001, 101, 1),
(2002, 1001, 102, 2),
(2003, 1002, 103, 1);

Example 1: Get Customer Orders (SQL Server):

CREATE PROCEDURE get_customer_orders
    @customer_id INT
AS
BEGIN
    SELECT
        o.OrderID,
        o.OrderDate,
        p.ProductName,
        oi.Quantity,
        p.Price * oi.Quantity AS ItemTotal
    FROM
        Orders o
    JOIN
        OrderItems oi ON o.OrderID = oi.OrderID
    JOIN
        Products p ON oi.ProductID = p.ProductID
    WHERE
        o.CustomerID = @customer_id;
END;

-- Calling the procedure
EXEC get_customer_orders @customer_id = 1;

Example Output:

OrderID	OrderDate	ProductName	Quantity	ItemTotal
1001	2023-10-26	Laptop	1	1200.00
1001	2023-10-26	Mouse	2	50.00

Example 2: Update Product Price with Validation (PostgreSQL):

CREATE OR REPLACE PROCEDURE update_product_price(
    product_id INT,
    new_price DECIMAL(10, 2)
)
LANGUAGE plpgsql
AS $$
BEGIN
    IF new_price < 0 THEN
        RAISE EXCEPTION 'Price cannot be negative';
    END IF;

    UPDATE Products SET Price = new_price WHERE ProductID = product_id;
END;
$$;

-- Calling the procedure
CALL update_product_price(102, 30.00);

-- Example call that will raise an exception:
-- CALL update_product_price(102, -10.00);

Example 3: Get Total Order Value (MySQL) with OUT Parameter:

CREATE PROCEDURE get_total_order_value (
    IN order_id INT,
    OUT total_value DECIMAL(10, 2)
)
BEGIN
    SELECT SUM(p.Price * oi.Quantity) INTO total_value
    FROM OrderItems oi
    JOIN Products p ON oi.ProductID = p.ProductID
    WHERE oi.OrderID = order_id;
END;

-- Calling the procedure
SET @total = 0;
CALL get_total_order_value(1001, @total);
SELECT @total;

Example 4: Using a Cursor (Avoid if possible, but shown for completeness - SQL Server):

-- Example of using a cursor to update a table
CREATE PROCEDURE update_all_product_prices
    @percentage_increase DECIMAL(5, 2)
AS
BEGIN
    DECLARE @product_id INT, @current_price DECIMAL(10, 2);

    -- Declare a cursor
    DECLARE product_cursor CURSOR FOR
    SELECT ProductID, Price FROM Products;

    -- Open the cursor
    OPEN product_cursor;

    -- Fetch the first row
    FETCH NEXT FROM product_cursor INTO @product_id, @current_price;

    -- Loop through the cursor
    WHILE @@FETCH_STATUS = 0
    BEGIN
        -- Calculate the new price
        DECLARE @new_price DECIMAL(10, 2) = @current_price * (1 + (@percentage_increase / 100));

        -- Update the product price
        UPDATE Products SET Price = @new_price WHERE ProductID = @product_id;

        -- Fetch the next row
        FETCH NEXT FROM product_cursor INTO @product_id, @current_price;
    END;

    -- Close the cursor
    CLOSE product_cursor;

    -- Deallocate the cursor
    DEALLOCATE product_cursor;
END;

-- Call the procedure
EXEC update_all_product_prices @percentage_increase = 10; -- Increase all prices by 10%

Important Note on Cursors: The above cursor example is for demonstration purposes only. In most cases, you can achieve the same result using a set-based operation, which is significantly faster. For example, the same update could be achieved with:

UPDATE Products
SET Price = Price * (1 + (@percentage_increase / 100));

6. Common Pitfalls

SQL Injection: Failing to use parameterized queries can lead to SQL injection vulnerabilities. Always use parameters!
Performance Issues: Inefficient queries, lack of indexes, and overuse of cursors can lead to poor performance. Profile your procedures and optimize accordingly.
Deadlocks: Procedures that access multiple tables in a different order can cause deadlocks. Order table access consistently.
Incorrect Parameter Types: Mismatched parameter types can lead to unexpected results or errors. Verify the data types of input and output parameters.
Lack of Error Handling: Failing to handle errors can lead to unexpected application behavior. Implement robust error handling.
Excessive Complexity: Overly complex procedures can be difficult to maintain and debug. Break down complex logic into smaller, more manageable units.
Not considering NULL values: Ensure your logic correctly handles NULL values that might be passed as parameters or retrieved from the database. Use IS NULL and IS NOT NULL appropriately.

7. Database Variations

Feature	MySQL	PostgreSQL	SQL Server	Oracle
Delimiter	`DELIMITER // ... DELIMITER ;`	`$$ ... $$` (or other delimiters)	`GO` (batch separator, not part of proc)	`/` (at the end of the procedure definition)
Parameter Modes	`IN`, `OUT`, `INOUT`	`IN`, `OUT`, `INOUT`	`INPUT` (default), `OUTPUT`	`IN` (default), `OUT`, `IN OUT`
Error Handling	`DECLARE EXIT HANDLER FOR SQLEXCEPTION`	`BEGIN ... EXCEPTION ... END`	`BEGIN TRY ... END TRY BEGIN CATCH ... END CATCH`	`BEGIN ... EXCEPTION ... END`
Transaction Control	`START TRANSACTION`, `COMMIT`, `ROLLBACK`	`BEGIN`, `COMMIT`, `ROLLBACK`	`BEGIN TRANSACTION`, `COMMIT`, `ROLLBACK`	`COMMIT`, `ROLLBACK`
Dynamic SQL	`PREPARE`, `EXECUTE`, `DEALLOCATE PREPARE`	`EXECUTE` (after preparing the statement)	`sp_executesql`	`EXECUTE IMMEDIATE`
Output Printing	`SELECT` (for results), `USER_VARIABLE`	`RAISE NOTICE` (for debugging)	`PRINT` (for debugging)	`DBMS_OUTPUT.PUT_LINE`
Procedure Definition	`CREATE PROCEDURE`	`CREATE OR REPLACE PROCEDURE`	`CREATE PROCEDURE`	`CREATE OR REPLACE PROCEDURE`
Comments	`--`, `/* ... */`	`--`, `/* ... */`	`--`, `/* ... */`	`--`, `/* ... */`

This cheatsheet provides a solid foundation for working with stored procedures. Remember to adapt the examples and best practices to your specific database system and application requirements. Always prioritize security and performance when designing and implementing stored procedures.