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
Step 1: Identify Database Type and Requirements
Determine the target database and procedure requirements:
SELECT version();
\dx
SELECT VERSION();
SHOW VARIABLES LIKE 'sql_mode';
SELECT @@VERSION;
Step 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' # 45000 = configured value
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
Step 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
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;
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' # 45000 = configured value
SET MESSAGE_TEXT = 'Insufficient funds';
END IF;
UPDATE accounts SET balance = balance + p_amount
WHERE id = p_to_account;
COMMIT;
END //
DELIMITER ;
Step 4: Validate Syntax
Use the validation script to check procedure syntax:
python3 ${CLAUDE_SKILL_DIR}/scripts/stored_procedure_syntax_validator.py \
--db-type postgresql \
--file procedure.sql
python3 ${CLAUDE_SKILL_DIR}/scripts/stored_procedure_syntax_validator.py \
--db-type mysql \
--file procedure.sql
Step 5: Deploy to Database
python3 ${CLAUDE_SKILL_DIR}/scripts/stored_procedure_deployer.py \
--db-type postgresql \
--host localhost \
--database mydb \
--file procedure.sql
python3 ${CLAUDE_SKILL_DIR}/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
| Error | Cause | Solution |
|---|
permission denied | Missing CREATE PROCEDURE privilege | GRANT CREATE PROCEDURE ON database TO user; |
syntax error | Invalid SQL for database type | Use database-specific syntax validator |
function already exists | Procedure exists without OR REPLACE | Add OR REPLACE or DROP first |
undefined column | Referenced column doesn't exist | Verify table schema before deployment |
transaction aborted | Error during transaction | Check EXCEPTION handler and ROLLBACK logic |
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
${CLAUDE_SKILL_DIR}/references/postgresql_stored_procedure_best_practices.md${CLAUDE_SKILL_DIR}/references/mysql_stored_procedure_best_practices.md${CLAUDE_SKILL_DIR}/references/sqlserver_stored_procedure_best_practices.md${CLAUDE_SKILL_DIR}/references/database_security_guidelines.md${CLAUDE_SKILL_DIR}/references/stored_procedure_optimization_techniques.md
Overview
Use when you need to generate, validate, or deploy stored procedures for PostgreSQL, MySQL, or SQL Server.
