Generating Stored Procedures

Stored Procedure Generator

Generate production-ready stored procedures for PostgreSQL, MySQL, and SQL Server with proper error handling, transaction management, and security best practices.

Prerequisites

• Database connection credentials (host, port, database, user, password)
• Appropriate permissions: CREATE PROCEDURE, CREATE FUNCTION, EXECUTE
• Target database type identified (PostgreSQL, MySQL, or SQL Server)

Instructions

1. Identify Database Type and Requirements

Determine the target database and procedure requirements:

-- PostgreSQL: Check version and extensions
SELECT version();
\dx

-- MySQL: Check version and settings
SELECT VERSION();
SHOW VARIABLES LIKE 'sql_mode';

-- SQL Server: Check version and edition
SELECT @@VERSION;

2. Generate Stored Procedure

PostgreSQL Function (PL/pgSQL):

CREATE OR REPLACE FUNCTION get_user_by_id(p_user_id INTEGER)
RETURNS TABLE(id INTEGER, username VARCHAR, email VARCHAR, created_at TIMESTAMP)
LANGUAGE plpgsql
AS $$
BEGIN
    RETURN QUERY
    SELECT u.id, u.username, u.email, u.created_at
    FROM users u
    WHERE u.id = p_user_id;

    IF NOT FOUND THEN
        RAISE EXCEPTION 'User with ID % not found', p_user_id
            USING ERRCODE = 'P0002';
    END IF;
END;
$$;

MySQL Stored Procedure:

DELIMITER //
CREATE PROCEDURE GetUserById(IN p_user_id INT)
BEGIN
    DECLARE user_exists INT DEFAULT 0;

    SELECT COUNT(*) INTO user_exists FROM users WHERE id = p_user_id;

    IF user_exists = 0 THEN
        SIGNAL SQLSTATE '45000'
            SET MESSAGE_TEXT = 'User not found';
    END IF;

    SELECT id, username, email, created_at
    FROM users
    WHERE id = p_user_id;
END //
DELIMITER ;

SQL Server Stored Procedure (T-SQL):

CREATE PROCEDURE dbo.GetUserById
    @UserId INT
AS
BEGIN
    SET NOCOUNT ON;

    IF NOT EXISTS (SELECT 1 FROM dbo.Users WHERE Id = @UserId)
    BEGIN
        RAISERROR('User with ID %d not found', 16, 1, @UserId);
        RETURN;
    END

    SELECT Id, Username, Email, CreatedAt
    FROM dbo.Users
    WHERE Id = @UserId;
END;
GO

3. Add Transaction Management

PostgreSQL with Transaction:

CREATE OR REPLACE FUNCTION transfer_funds(
    p_from_account INTEGER,
    p_to_account INTEGER,
    p_amount NUMERIC(15,2)
)
RETURNS BOOLEAN
LANGUAGE plpgsql
AS $$
BEGIN
    -- Debit source account
    UPDATE accounts SET balance = balance - p_amount
    WHERE id = p_from_account AND balance >= p_amount;

    IF NOT FOUND THEN
        RAISE EXCEPTION 'Insufficient funds or invalid source account';
    END IF;

    -- Credit destination account
    UPDATE accounts SET balance = balance + p_amount
    WHERE id = p_to_account;

    IF NOT FOUND THEN
        RAISE EXCEPTION 'Invalid destination account';
    END IF;

    RETURN TRUE;
EXCEPTION
    WHEN OTHERS THEN
        RAISE;
END;
$$;

MySQL with Transaction:

DELIMITER //
CREATE PROCEDURE TransferFunds(
    IN p_from_account INT,
    IN p_to_account INT,
    IN p_amount DECIMAL(15,2)
)
BEGIN
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        RESIGNAL;
    END;

    START TRANSACTION;

    UPDATE accounts SET balance = balance - p_amount
    WHERE id = p_from_account AND balance >= p_amount;

    IF ROW_COUNT() = 0 THEN
        SIGNAL SQLSTATE '45000'
            SET MESSAGE_TEXT = 'Insufficient funds';
    END IF;

    UPDATE accounts SET balance = balance + p_amount
    WHERE id = p_to_account;

    COMMIT;
END //
DELIMITER ;

4. Validate Syntax

Use the validation script to check procedure syntax:

# Validate PostgreSQL procedure
python3 {baseDir}/scripts/stored_procedure_syntax_validator.py \
    --db-type postgresql \
    --file procedure.sql

# Validate MySQL procedure
python3 {baseDir}/scripts/stored_procedure_syntax_validator.py \
    --db-type mysql \
    --file procedure.sql

5. Deploy to Database

# Deploy to PostgreSQL
python3 {baseDir}/scripts/stored_procedure_deployer.py \
    --db-type postgresql \
    --host localhost \
    --database mydb \
    --file procedure.sql

# Deploy to MySQL
python3 {baseDir}/scripts/stored_procedure_deployer.py \
    --db-type mysql \
    --host localhost \
    --database mydb \
    --file procedure.sql

Output

• SQL procedure file with proper syntax for target database
• Validation report confirming syntax correctness
• Deployment confirmation with execution results
• Rollback script for procedure removal

Error Handling

Examples

Generate CRUD procedures for a table:

User: Generate CRUD stored procedures for the 'products' table in PostgreSQL

Claude: I'll create four procedures for the products table:
1. create_product - Insert new product
2. get_product - Retrieve by ID
3. update_product - Update existing product
4. delete_product - Soft delete product

Create audit trigger:

User: Create a trigger to log all changes to the orders table

Claude: I'll create an audit trigger that:
1. Creates an orders_audit table if not exists
2. Captures INSERT, UPDATE, DELETE operations
3. Records old/new values, user, and timestamp

Resources

• {baseDir}/references/postgresql_stored_procedure_best_practices.md
• {baseDir}/references/mysql_stored_procedure_best_practices.md
• {baseDir}/references/sqlserver_stored_procedure_best_practices.md
• {baseDir}/references/database_security_guidelines.md
• {baseDir}/references/stored_procedure_optimization_techniques.md