generating-stored-procedures

name	generating-stored-procedures
description	Use when you need to generate, validate, or deploy stored procedures for PostgreSQL, MySQL, or SQL Server. Creates database functions, triggers, and procedures with proper error handling and transaction management. Trigger with phrases like "generate stored procedure", "create database function", "write SQL procedure", "add trigger to table", or "create CRUD procedures".
allowed-tools	Read, Write, Edit, Grep, Glob, Bash(psql:*), Bash(mysql:*), Bash(sqlcmd:*), Bash(python3:*)
version	2.0.0
author	Jeremy Longshore <[e-mail]>
license	MIT
compatible-with	claude-code, codex, openclaw
tags	["database","deployment","postgresql","mysql"]

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:

-- 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;

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';
     IF;
;
$$;