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: Step 2: Generate Stored Procedure PostgreSQL Function (PL/pgSQL)…

THEN\n RAISE EXCEPTION 'Invalid email format: %', p_email\n USING ERRCODE = 'P0001';\n END IF;\n RETURN TRUE;\nEND;\n$ LANGUAGE plpgsql;\n\n-- SQL Server\nCREATE PROCEDURE dbo.ValidateAndInsertUser\n @Email NVARCHAR(255),\n @Age INT\nAS\nBEGIN\n -- Validate email format\n IF @Email NOT LIKE '%_@__%.__%'\n THROW 50001, 'Invalid email format', 1;\n\n -- Validate age range\n IF @Age \u003c 0 OR @Age > 150\n THROW 50002, 'Age must be between 0 and 150', 1;\n\n INSERT INTO Users (Email, Age) VALUES (@Email, @Age);\nEND;\n```\n\n## Sensitive Data Protection\n\n### Avoid Logging Sensitive Data\n\n```sql\n-- BAD: Password in error message\nRAISE NOTICE 'Login failed for password: %', p_password;\n\n-- GOOD: Mask sensitive data\nRAISE NOTICE 'Login failed for user: %', p_username;\n```\n\n### Use Encryption for Sensitive Columns\n\n```sql\n-- PostgreSQL with pgcrypto\nCREATE FUNCTION store_secret(p_user_id INT, p_secret TEXT, p_key TEXT)\nRETURNS VOID AS $\nBEGIN\n UPDATE users\n SET encrypted_secret = pgp_sym_encrypt(p_secret, p_key)\n WHERE id = p_user_id;\nEND;\n$ LANGUAGE plpgsql SECURITY DEFINER;\n```\n\n## SECURITY DEFINER vs SECURITY INVOKER\n\n### SECURITY DEFINER (PostgreSQL)\n\nExecutes with function owner's privileges:\n\n```sql\n-- Allows controlled access to sensitive table\nCREATE FUNCTION admin_get_user_count()\nRETURNS BIGINT\nSECURITY DEFINER\nSET search_path = public, pg_temp\nAS $\n SELECT COUNT(*) FROM admin.sensitive_users;\n$ LANGUAGE sql;\n\n-- Regular users can call this but not access admin.sensitive_users directly\nGRANT EXECUTE ON FUNCTION admin_get_user_count() TO app_user;\n```\n\n### SQL SECURITY DEFINER (MySQL)\n\n```sql\nCREATE DEFINER='admin'@'localhost' PROCEDURE GetSensitiveData()\nSQL SECURITY DEFINER\nBEGIN\n SELECT * FROM sensitive_table;\nEND;\n```\n\n### EXECUTE AS (SQL Server)\n\n```sql\nCREATE PROCEDURE dbo.GetSensitiveData\nWITH EXECUTE AS 'AdminUser'\nAS\nBEGIN\n SELECT * FROM dbo.SensitiveTable;\nEND;\n```\n\n## Audit Logging\n\nTrack who does what:\n\n```sql\n-- PostgreSQL audit trigger\nCREATE TABLE audit_log (\n id SERIAL PRIMARY KEY,\n table_name VARCHAR(100),\n operation VARCHAR(10),\n old_data JSONB,\n new_data JSONB,\n changed_by VARCHAR(100) DEFAULT current_user,\n changed_at TIMESTAMP DEFAULT NOW()\n);\n\nCREATE FUNCTION audit_trigger_func()\nRETURNS TRIGGER AS $\nBEGIN\n INSERT INTO audit_log (table_name, operation, old_data, new_data)\n VALUES (\n TG_TABLE_NAME,\n TG_OP,\n CASE WHEN TG_OP = 'DELETE' THEN row_to_json(OLD) ELSE NULL END,\n CASE WHEN TG_OP IN ('INSERT', 'UPDATE') THEN row_to_json(NEW) ELSE NULL END\n );\n RETURN COALESCE(NEW, OLD);\nEND;\n$ LANGUAGE plpgsql SECURITY DEFINER;\n```\n\n## Rate Limiting\n\nPrevent abuse:\n\n```sql\n-- PostgreSQL rate limiting\nCREATE FUNCTION check_rate_limit(p_user_id INT, p_action VARCHAR)\nRETURNS BOOLEAN AS $\nDECLARE\n v_count INT;\nBEGIN\n SELECT COUNT(*) INTO v_count\n FROM rate_limit_log\n WHERE user_id = p_user_id\n AND action = p_action\n AND created_at > NOW() - INTERVAL '1 minute';\n\n IF v_count >= 100 THEN\n RAISE EXCEPTION 'Rate limit exceeded for action: %', p_action\n USING ERRCODE = 'P0429';\n END IF;\n\n INSERT INTO rate_limit_log (user_id, action) VALUES (p_user_id, p_action);\n RETURN TRUE;\nEND;\n$ LANGUAGE plpgsql;\n```\n\n## Connection Security\n\n### Use SSL/TLS\n\n```sql\n-- PostgreSQL: Check SSL status\nSELECT ssl, version FROM pg_stat_ssl WHERE pid = pg_backend_pid();\n\n-- MySQL: Require SSL for user\nCREATE USER 'secure_user'@'%' REQUIRE SSL;\n```\n\n### Limit Connection Sources\n\n```sql\n-- PostgreSQL: pg_hba.conf restricts connections\n-- MySQL: User@Host pattern\nCREATE USER 'appuser'@'192.168.1.%' IDENTIFIED BY 'password';\n```\n\n## Security Checklist\n\n| Category | Check | Status |\n|----------|-------|--------|\n| Authentication | Procedures don't bypass auth | Required |\n| Authorization | Least privilege enforced | Required |\n| Input Validation | All inputs sanitized | Required |\n| SQL Injection | No string concatenation | Required |\n| Error Handling | No sensitive data in errors | Required |\n| Logging | Audit trail enabled | Recommended |\n| Encryption | Sensitive data encrypted | Recommended |\n| Rate Limiting | Abuse prevention in place | Recommended |\n","content_type":"text/markdown; charset=utf-8","language":"markdown","size":6045,"content_sha256":"c84c81a24ae8143a722d7f333da6db494ae5794a10e499be906b4cca2cbca7e0"},{"filename":"references/mysql_stored_procedure_best_practices.md","content":"# MySQL Stored Procedure Best Practices\n\n## Basic Structure\n\n```sql\nDELIMITER //\nCREATE PROCEDURE procedure_name(\n IN param1 INT,\n OUT param2 VARCHAR(255),\n INOUT param3 DECIMAL(10,2)\n)\nBEGIN\n -- Procedure body\nEND //\nDELIMITER ;\n```\n\n## Parameter Modes\n\n| Mode | Description | Use Case |\n|------|-------------|----------|\n| IN | Input only (default) | Pass values to procedure |\n| OUT | Output only | Return values from procedure |\n| INOUT | Both input and output | Modify and return value |\n\n```sql\nDELIMITER //\nCREATE PROCEDURE calculate_order_total(\n IN p_order_id INT,\n OUT p_subtotal DECIMAL(10,2),\n OUT p_tax DECIMAL(10,2),\n OUT p_total DECIMAL(10,2)\n)\nBEGIN\n SELECT SUM(quantity * unit_price)\n INTO p_subtotal\n FROM order_items\n WHERE order_id = p_order_id;\n\n SET p_tax = p_subtotal * 0.08;\n SET p_total = p_subtotal + p_tax;\nEND //\nDELIMITER ;\n\n-- Call procedure\nCALL calculate_order_total(123, @subtotal, @tax, @total);\nSELECT @subtotal, @tax, @total;\n```\n\n## Variables and Data Types\n\n```sql\nDELIMITER //\nCREATE PROCEDURE variable_examples()\nBEGIN\n -- Variable declaration\n DECLARE v_count INT DEFAULT 0;\n DECLARE v_name VARCHAR(100);\n DECLARE v_created_at DATETIME DEFAULT NOW();\n DECLARE v_is_active BOOLEAN DEFAULT TRUE;\n\n -- Assignment\n SET v_count = v_count + 1;\n SELECT name INTO v_name FROM users WHERE id = 1;\n\n -- Multiple assignment\n SELECT COUNT(*), MAX(name)\n INTO v_count, v_name\n FROM users;\nEND //\nDELIMITER ;\n```\n\n## Control Flow\n\n### IF-THEN-ELSE\n\n```sql\nDELIMITER //\nCREATE PROCEDURE get_discount(\n IN p_customer_type VARCHAR(20),\n OUT p_discount DECIMAL(5,2)\n)\nBEGIN\n IF p_customer_type = 'premium' THEN\n SET p_discount = 0.20;\n ELSEIF p_customer_type = 'regular' THEN\n SET p_discount = 0.10;\n ELSE\n SET p_discount = 0.00;\n END IF;\nEND //\nDELIMITER ;\n```\n\n### CASE Statement\n\n```sql\nDELIMITER //\nCREATE PROCEDURE get_status_label(\n IN p_status_code INT,\n OUT p_label VARCHAR(50)\n)\nBEGIN\n SET p_label = CASE p_status_code\n WHEN 1 THEN 'Pending'\n WHEN 2 THEN 'Processing'\n WHEN 3 THEN 'Shipped'\n WHEN 4 THEN 'Delivered'\n ELSE 'Unknown'\n END;\nEND //\nDELIMITER ;\n```\n\n### Loops\n\n```sql\nDELIMITER //\nCREATE PROCEDURE process_batch()\nBEGIN\n DECLARE v_done BOOLEAN DEFAULT FALSE;\n DECLARE v_id INT;\n DECLARE cur CURSOR FOR SELECT id FROM pending_items;\n DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_done = TRUE;\n\n OPEN cur;\n\n read_loop: LOOP\n FETCH cur INTO v_id;\n IF v_done THEN\n LEAVE read_loop;\n END IF;\n\n -- Process each item\n UPDATE pending_items SET status = 'processed' WHERE id = v_id;\n END LOOP;\n\n CLOSE cur;\nEND //\nDELIMITER ;\n```\n\n## Error Handling\n\n```sql\nDELIMITER //\nCREATE PROCEDURE safe_transfer(\n IN p_from_account INT,\n IN p_to_account INT,\n IN p_amount DECIMAL(15,2)\n)\nBEGIN\n -- Declare handler for any SQL exception\n DECLARE EXIT HANDLER FOR SQLEXCEPTION\n BEGIN\n ROLLBACK;\n SELECT 'Transaction failed' AS message;\n END;\n\n -- Declare handler for specific error\n DECLARE EXIT HANDLER FOR 1062\n BEGIN\n SELECT 'Duplicate entry error' AS message;\n END;\n\n START TRANSACTION;\n\n -- Check sufficient funds\n IF (SELECT balance FROM accounts WHERE id = p_from_account) \u003c p_amount THEN\n SIGNAL SQLSTATE '45000'\n SET MESSAGE_TEXT = 'Insufficient funds';\n END IF;\n\n UPDATE accounts SET balance = balance - p_amount WHERE id = p_from_account;\n UPDATE accounts SET balance = balance + p_amount WHERE id = p_to_account;\n\n COMMIT;\n SELECT 'Transfer successful' AS message;\nEND //\nDELIMITER ;\n```\n\n## Custom Errors with SIGNAL\n\n```sql\nDELIMITER //\nCREATE PROCEDURE validate_age(IN p_age INT)\nBEGIN\n IF p_age \u003c 0 THEN\n SIGNAL SQLSTATE '45000'\n SET MESSAGE_TEXT = 'Age cannot be negative',\n MYSQL_ERRNO = 1001;\n ELSEIF p_age > 150 THEN\n SIGNAL SQLSTATE '45000'\n SET MESSAGE_TEXT = 'Age seems unrealistic',\n MYSQL_ERRNO = 1002;\n END IF;\n\n SELECT 'Age is valid' AS result;\nEND //\nDELIMITER ;\n```\n\n## Transaction Management\n\n```sql\nDELIMITER //\nCREATE PROCEDURE create_order_with_items(\n IN p_customer_id INT,\n IN p_items JSON\n)\nBEGIN\n DECLARE v_order_id INT;\n DECLARE v_item JSON;\n DECLARE v_i INT DEFAULT 0;\n DECLARE v_count INT;\n\n DECLARE EXIT HANDLER FOR SQLEXCEPTION\n BEGIN\n ROLLBACK;\n RESIGNAL;\n END;\n\n START TRANSACTION;\n\n -- Create order\n INSERT INTO orders (customer_id, created_at)\n VALUES (p_customer_id, NOW());\n SET v_order_id = LAST_INSERT_ID();\n\n -- Add items\n SET v_count = JSON_LENGTH(p_items);\n WHILE v_i \u003c v_count DO\n SET v_item = JSON_EXTRACT(p_items, CONCAT('$[', v_i, ']'));\n\n INSERT INTO order_items (order_id, product_id, quantity, unit_price)\n VALUES (\n v_order_id,\n JSON_EXTRACT(v_item, '$.product_id'),\n JSON_EXTRACT(v_item, '$.quantity'),\n JSON_EXTRACT(v_item, '$.price')\n );\n\n SET v_i = v_i + 1;\n END WHILE;\n\n COMMIT;\n SELECT v_order_id AS order_id;\nEND //\nDELIMITER ;\n```\n\n## Security Best Practices\n\n### Use SQL SECURITY\n\n```sql\n-- DEFINER: Runs with creator's privileges (default)\nCREATE DEFINER='admin'@'localhost' PROCEDURE admin_only_proc()\nSQL SECURITY DEFINER\nBEGIN\n -- Can access tables admin has access to\nEND;\n\n-- INVOKER: Runs with caller's privileges\nCREATE PROCEDURE user_proc()\nSQL SECURITY INVOKER\nBEGIN\n -- Limited to caller's permissions\nEND;\n```\n\n### Prevent SQL Injection\n\n```sql\n-- BAD: Dynamic SQL vulnerable to injection\nCREATE PROCEDURE bad_search(IN p_name VARCHAR(100))\nBEGIN\n SET @sql = CONCAT('SELECT * FROM users WHERE name = ''', p_name, '''');\n PREPARE stmt FROM @sql;\n EXECUTE stmt;\nEND;\n\n-- GOOD: Use parameterized prepared statements\nCREATE PROCEDURE good_search(IN p_name VARCHAR(100))\nBEGIN\n SET @sql = 'SELECT * FROM users WHERE name = ?';\n SET @name = p_name;\n PREPARE stmt FROM @sql;\n EXECUTE stmt USING @name;\n DEALLOCATE PREPARE stmt;\nEND;\n```\n\n## Performance Tips\n\n1. **Avoid SELECT * in procedures** - Specify columns\n2. **Use appropriate indexes** - Ensure WHERE clauses are indexed\n3. **Limit result sets** - Use LIMIT when possible\n4. **Avoid cursors for bulk operations** - Use set-based operations\n5. **Use EXPLAIN** - Analyze query plans within procedures\n\n```sql\n-- Set-based (fast)\nUPDATE products SET price = price * 1.1 WHERE category = 'electronics';\n\n-- Cursor-based (slow)\n-- Avoid unless absolutely necessary\n```\n\n## Debugging\n\n```sql\nDELIMITER //\nCREATE PROCEDURE debug_proc()\nBEGIN\n -- Create debug table if needed\n CREATE TEMPORARY TABLE IF NOT EXISTS debug_log (\n id INT AUTO_INCREMENT PRIMARY KEY,\n message TEXT,\n created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP\n );\n\n INSERT INTO debug_log (message) VALUES ('Procedure started');\n\n -- Your logic here\n\n INSERT INTO debug_log (message) VALUES ('Procedure completed');\n\n SELECT * FROM debug_log;\nEND //\nDELIMITER ;\n```\n","content_type":"text/markdown; charset=utf-8","language":"markdown","size":7209,"content_sha256":"3ab757cf41a5cd9120051d65d7714d6fcb16040b96e6fe4f1683e4d86f11b0ca"},{"filename":"references/postgresql_stored_procedure_best_practices.md","content":"# PostgreSQL Stored Procedure Best Practices\n\n## Function vs Procedure\n\nPostgreSQL 11+ supports both:\n\n- **FUNCTION**: Returns values, can be used in SELECT\n- **PROCEDURE**: No return value, supports COMMIT/ROLLBACK inside\n\n```sql\n-- Function (returns value)\nCREATE FUNCTION get_user(p_id INT) RETURNS users AS $\n SELECT * FROM users WHERE id = p_id;\n$ LANGUAGE sql;\n\n-- Procedure (no return, can commit)\nCREATE PROCEDURE archive_old_orders() AS $\nBEGIN\n INSERT INTO orders_archive SELECT * FROM orders WHERE created_at \u003c NOW() - INTERVAL '1 year';\n DELETE FROM orders WHERE created_at \u003c NOW() - INTERVAL '1 year';\n COMMIT;\nEND;\n$ LANGUAGE plpgsql;\n```\n\n## Parameter Naming\n\nUse `p_` prefix for parameters to avoid column name conflicts:\n\n```sql\n-- GOOD: Clear parameter naming\nCREATE FUNCTION update_user(p_user_id INT, p_email VARCHAR)\nRETURNS VOID AS $\nBEGIN\n UPDATE users SET email = p_email WHERE id = p_user_id;\nEND;\n$ LANGUAGE plpgsql;\n\n-- BAD: Ambiguous naming causes bugs\nCREATE FUNCTION update_user(user_id INT, email VARCHAR)\nRETURNS VOID AS $\nBEGIN\n -- BUG: 'email = email' always true due to column name conflict\n UPDATE users SET email = email WHERE id = user_id;\nEND;\n$ LANGUAGE plpgsql;\n```\n\n## Return Types\n\n### Single Row\n\n```sql\nCREATE FUNCTION get_user(p_id INT)\nRETURNS users AS $\n SELECT * FROM users WHERE id = p_id;\n$ LANGUAGE sql;\n```\n\n### Multiple Rows\n\n```sql\nCREATE FUNCTION get_active_users()\nRETURNS SETOF users AS $\n SELECT * FROM users WHERE active = true;\n$ LANGUAGE sql;\n```\n\n### Custom Columns\n\n```sql\nCREATE FUNCTION get_user_summary(p_id INT)\nRETURNS TABLE(name VARCHAR, order_count BIGINT, total_spent NUMERIC) AS $\nBEGIN\n RETURN QUERY\n SELECT u.name, COUNT(o.id), SUM(o.total)\n FROM users u\n LEFT JOIN orders o ON o.user_id = u.id\n WHERE u.id = p_id\n GROUP BY u.id;\nEND;\n$ LANGUAGE plpgsql;\n```\n\n## Error Handling\n\n```sql\nCREATE FUNCTION safe_divide(a NUMERIC, b NUMERIC)\nRETURNS NUMERIC AS $\nBEGIN\n IF b = 0 THEN\n RAISE EXCEPTION 'Division by zero'\n USING ERRCODE = '22012',\n HINT = 'Ensure divisor is not zero';\n END IF;\n RETURN a / b;\nEXCEPTION\n WHEN division_by_zero THEN\n RAISE NOTICE 'Caught division by zero';\n RETURN NULL;\n WHEN OTHERS THEN\n RAISE NOTICE 'Unexpected error: %', SQLERRM;\n RAISE;\nEND;\n$ LANGUAGE plpgsql;\n```\n\n## Security: SECURITY DEFINER\n\nExecute with function owner's privileges:\n\n```sql\n-- Runs as function owner, not caller\nCREATE FUNCTION admin_reset_password(p_user_id INT, p_new_hash VARCHAR)\nRETURNS VOID\nSECURITY DEFINER\nSET search_path = public\nAS $\nBEGIN\n UPDATE users SET password_hash = p_new_hash WHERE id = p_user_id;\nEND;\n$ LANGUAGE plpgsql;\n\n-- Grant execute to specific role\nREVOKE ALL ON FUNCTION admin_reset_password FROM PUBLIC;\nGRANT EXECUTE ON FUNCTION admin_reset_password TO app_admin;\n```\n\n## Performance Tips\n\n### Use IMMUTABLE/STABLE/VOLATILE Correctly\n\n```sql\n-- IMMUTABLE: Same input = same output, no DB access\nCREATE FUNCTION double(x INT) RETURNS INT AS $\n SELECT x * 2;\n$ LANGUAGE sql IMMUTABLE;\n\n-- STABLE: Same output within single query, reads DB\nCREATE FUNCTION get_setting(key TEXT) RETURNS TEXT AS $\n SELECT value FROM settings WHERE name = key;\n$ LANGUAGE sql STABLE;\n\n-- VOLATILE (default): May return different values, modifies DB\nCREATE FUNCTION log_access() RETURNS VOID AS $\n INSERT INTO access_log (accessed_at) VALUES (NOW());\n$ LANGUAGE sql VOLATILE;\n```\n\n### Avoid Row-by-Row Processing\n\n```sql\n-- BAD: Slow cursor loop\nCREATE FUNCTION update_all_prices_bad() RETURNS VOID AS $\nDECLARE\n rec RECORD;\nBEGIN\n FOR rec IN SELECT id, price FROM products LOOP\n UPDATE products SET price = rec.price * 1.1 WHERE id = rec.id;\n END LOOP;\nEND;\n$ LANGUAGE plpgsql;\n\n-- GOOD: Single UPDATE statement\nCREATE FUNCTION update_all_prices_good() RETURNS INT AS $\n UPDATE products SET price = price * 1.1;\n SELECT COUNT(*) FROM products;\n$ LANGUAGE sql;\n```\n\n## Debugging\n\n```sql\nCREATE FUNCTION debug_example(p_value INT) RETURNS INT AS $\nBEGIN\n RAISE DEBUG 'Input value: %', p_value;\n RAISE NOTICE 'Processing value: %', p_value;\n RAISE WARNING 'Value might be too high: %', p_value;\n\n -- Log to table for persistent debugging\n INSERT INTO debug_log (message, created_at)\n VALUES (format('debug_example called with %s', p_value), NOW());\n\n RETURN p_value * 2;\nEND;\n$ LANGUAGE plpgsql;\n```\n\n## Naming Conventions\n\n| Type | Convention | Example |\n|------|------------|---------|\n| Functions | snake_case, verb prefix | `get_user`, `calculate_total` |\n| Parameters | p_ prefix | `p_user_id`, `p_amount` |\n| Local variables | v_ prefix | `v_count`, `v_result` |\n| Cursors | cur_ prefix | `cur_orders` |\n\n## Testing\n\n```sql\n-- Create test wrapper\nCREATE FUNCTION test_get_user() RETURNS BOOLEAN AS $\nDECLARE\n v_result users;\nBEGIN\n -- Setup\n INSERT INTO users (id, name) VALUES (999, 'Test User');\n\n -- Test\n SELECT * INTO v_result FROM get_user(999);\n\n -- Assert\n IF v_result.name != 'Test User' THEN\n RAISE EXCEPTION 'Test failed: expected Test User, got %', v_result.name;\n END IF;\n\n -- Cleanup\n DELETE FROM users WHERE id = 999;\n\n RETURN TRUE;\nEND;\n$ LANGUAGE plpgsql;\n```\n","content_type":"text/markdown; charset=utf-8","language":"markdown","size":5318,"content_sha256":"dbe062a1d47cfd857d1ef374ce1fe02bdb84a60ab5a6e3a367704f0d8ee75859"},{"filename":"references/README.md","content":"# References\n\nBundled resources for stored-procedure-generator skill\n\n- [x] postgresql_stored_procedure_best_practices.md: Best practices for writing stored procedures in PostgreSQL.\n- [x] mysql_stored_procedure_best_practices.md: Best practices for writing stored procedures in MySQL.\n- [x] sqlserver_stored_procedure_best_practices.md: Best practices for writing stored procedures in SQL Server.\n- [x] database_security_guidelines.md: Guidelines for ensuring database security when using stored procedures.\n- [x] stored_procedure_optimization_techniques.md: Techniques for optimizing the performance of stored procedures.\n","content_type":"text/markdown; charset=utf-8","language":"markdown","size":624,"content_sha256":"ffffc846250424606a4f88908b8665b1211b6f6bafc9ff831f702af72e408a3c"},{"filename":"references/sqlserver_stored_procedure_best_practices.md","content":"# SQL Server Stored Procedure Best Practices\n\n## Basic Structure\n\n```sql\nCREATE PROCEDURE dbo.ProcedureName\n @Param1 INT,\n @Param2 VARCHAR(100) = NULL, -- Optional with default\n @Result INT OUTPUT -- Output parameter\nAS\nBEGIN\n SET NOCOUNT ON;\n\n -- Procedure body\n\n SET @Result = @@ROWCOUNT;\nEND;\nGO\n```\n\n## Parameter Conventions\n\n| Prefix | Use Case |\n|--------|----------|\n| @ | Required for all parameters |\n| OUTPUT | Return values to caller |\n| = value | Default value (makes param optional) |\n\n```sql\nCREATE PROCEDURE dbo.GetUserOrders\n @UserId INT,\n @StartDate DATE = NULL,\n @EndDate DATE = NULL,\n @TotalCount INT OUTPUT\nAS\nBEGIN\n SET NOCOUNT ON;\n\n -- Default date range if not provided\n SET @StartDate = ISNULL(@StartDate, DATEADD(MONTH, -1, GETDATE()));\n SET @EndDate = ISNULL(@EndDate, GETDATE());\n\n SELECT\n OrderId,\n OrderDate,\n TotalAmount\n FROM dbo.Orders\n WHERE UserId = @UserId\n AND OrderDate BETWEEN @StartDate AND @EndDate\n ORDER BY OrderDate DESC;\n\n SET @TotalCount = @@ROWCOUNT;\nEND;\nGO\n\n-- Calling with output parameter\nDECLARE @Count INT;\nEXEC dbo.GetUserOrders @UserId = 123, @TotalCount = @Count OUTPUT;\nSELECT @Count AS OrderCount;\n```\n\n## Error Handling with TRY-CATCH\n\n```sql\nCREATE PROCEDURE dbo.TransferFunds\n @FromAccountId INT,\n @ToAccountId INT,\n @Amount DECIMAL(18,2)\nAS\nBEGIN\n SET NOCOUNT ON;\n SET XACT_ABORT ON; -- Auto-rollback on error\n\n BEGIN TRY\n BEGIN TRANSACTION;\n\n -- Validate sufficient funds\n IF NOT EXISTS (\n SELECT 1 FROM dbo.Accounts\n WHERE AccountId = @FromAccountId AND Balance >= @Amount\n )\n BEGIN\n RAISERROR('Insufficient funds', 16, 1);\n END\n\n -- Perform transfer\n UPDATE dbo.Accounts\n SET Balance = Balance - @Amount\n WHERE AccountId = @FromAccountId;\n\n UPDATE dbo.Accounts\n SET Balance = Balance + @Amount\n WHERE AccountId = @ToAccountId;\n\n -- Log transaction\n INSERT INTO dbo.TransactionLog (FromAccount, ToAccount, Amount, TransactionDate)\n VALUES (@FromAccountId, @ToAccountId, @Amount, GETDATE());\n\n COMMIT TRANSACTION;\n END TRY\n BEGIN CATCH\n IF @@TRANCOUNT > 0\n ROLLBACK TRANSACTION;\n\n -- Re-throw error with details\n DECLARE @ErrorMessage NVARCHAR(4000) = ERROR_MESSAGE();\n DECLARE @ErrorSeverity INT = ERROR_SEVERITY();\n DECLARE @ErrorState INT = ERROR_STATE();\n\n RAISERROR(@ErrorMessage, @ErrorSeverity, @ErrorState);\n END CATCH\nEND;\nGO\n```\n\n## Custom Error Handling\n\n```sql\nCREATE PROCEDURE dbo.ValidateOrder\n @OrderId INT\nAS\nBEGIN\n SET NOCOUNT ON;\n\n -- Check order exists\n IF NOT EXISTS (SELECT 1 FROM dbo.Orders WHERE OrderId = @OrderId)\n BEGIN\n RAISERROR('Order %d not found', 16, 1, @OrderId);\n RETURN -1;\n END\n\n -- Check order status\n DECLARE @Status VARCHAR(20);\n SELECT @Status = Status FROM dbo.Orders WHERE OrderId = @OrderId;\n\n IF @Status = 'Cancelled'\n BEGIN\n RAISERROR('Order %d has been cancelled', 16, 1, @OrderId);\n RETURN -2;\n END\n\n -- Throw custom error (SQL 2012+)\n IF @Status = 'Shipped'\n BEGIN\n THROW 51000, 'Cannot modify shipped order', 1;\n END\n\n RETURN 0; -- Success\nEND;\nGO\n```\n\n## Table-Valued Parameters\n\n```sql\n-- Create table type\nCREATE TYPE dbo.OrderItemType AS TABLE\n(\n ProductId INT NOT NULL,\n Quantity INT NOT NULL,\n UnitPrice DECIMAL(10,2) NOT NULL\n);\nGO\n\n-- Use in procedure\nCREATE PROCEDURE dbo.CreateOrderWithItems\n @CustomerId INT,\n @Items dbo.OrderItemType READONLY\nAS\nBEGIN\n SET NOCOUNT ON;\n\n DECLARE @OrderId INT;\n\n BEGIN TRANSACTION;\n\n INSERT INTO dbo.Orders (CustomerId, OrderDate, Status)\n VALUES (@CustomerId, GETDATE(), 'Pending');\n\n SET @OrderId = SCOPE_IDENTITY();\n\n INSERT INTO dbo.OrderItems (OrderId, ProductId, Quantity, UnitPrice)\n SELECT @OrderId, ProductId, Quantity, UnitPrice\n FROM @Items;\n\n COMMIT TRANSACTION;\n\n SELECT @OrderId AS NewOrderId;\nEND;\nGO\n\n-- Calling with table parameter\nDECLARE @OrderItems dbo.OrderItemType;\nINSERT INTO @OrderItems VALUES (101, 2, 29.99), (102, 1, 49.99);\nEXEC dbo.CreateOrderWithItems @CustomerId = 1, @Items = @OrderItems;\n```\n\n## Dynamic SQL (Safe)\n\n```sql\nCREATE PROCEDURE dbo.SearchProducts\n @SearchTerm NVARCHAR(100) = NULL,\n @CategoryId INT = NULL,\n @MinPrice DECIMAL(10,2) = NULL,\n @MaxPrice DECIMAL(10,2) = NULL,\n @SortColumn NVARCHAR(50) = 'ProductName',\n @SortDirection NVARCHAR(4) = 'ASC'\nAS\nBEGIN\n SET NOCOUNT ON;\n\n DECLARE @SQL NVARCHAR(MAX);\n DECLARE @ParamDef NVARCHAR(500);\n\n -- Validate sort column (prevent injection)\n IF @SortColumn NOT IN ('ProductName', 'Price', 'CategoryId', 'CreatedDate')\n SET @SortColumn = 'ProductName';\n\n IF @SortDirection NOT IN ('ASC', 'DESC')\n SET @SortDirection = 'ASC';\n\n SET @SQL = N'\n SELECT ProductId, ProductName, Price, CategoryId\n FROM dbo.Products\n WHERE 1=1';\n\n IF @SearchTerm IS NOT NULL\n SET @SQL += N' AND ProductName LIKE @SearchTerm + ''%''';\n\n IF @CategoryId IS NOT NULL\n SET @SQL += N' AND CategoryId = @CategoryId';\n\n IF @MinPrice IS NOT NULL\n SET @SQL += N' AND Price >= @MinPrice';\n\n IF @MaxPrice IS NOT NULL\n SET @SQL += N' AND Price \u003c= @MaxPrice';\n\n SET @SQL += N' ORDER BY ' + QUOTENAME(@SortColumn) + N' ' + @SortDirection;\n\n SET @ParamDef = N'\n @SearchTerm NVARCHAR(100),\n @CategoryId INT,\n @MinPrice DECIMAL(10,2),\n @MaxPrice DECIMAL(10,2)';\n\n EXEC sp_executesql @SQL, @ParamDef,\n @SearchTerm, @CategoryId, @MinPrice, @MaxPrice;\nEND;\nGO\n```\n\n## Performance Best Practices\n\n### Use SET NOCOUNT ON\n\n```sql\nCREATE PROCEDURE dbo.FastProcedure\nAS\nBEGIN\n SET NOCOUNT ON; -- Prevents \"n rows affected\" messages\n -- Your logic\nEND;\n```\n\n### Avoid SELECT *\n\n```sql\n-- BAD\nSELECT * FROM dbo.Users WHERE UserId = @UserId;\n\n-- GOOD\nSELECT UserId, UserName, Email, CreatedDate\nFROM dbo.Users\nWHERE UserId = @UserId;\n```\n\n### Use Appropriate Indexes\n\n```sql\n-- Add index hint if needed (use sparingly)\nSELECT *\nFROM dbo.Orders WITH (INDEX(IX_Orders_CustomerId))\nWHERE CustomerId = @CustomerId;\n```\n\n### Schema-Qualify Objects\n\n```sql\n-- BAD (requires schema resolution)\nSELECT * FROM Users;\n\n-- GOOD (direct access)\nSELECT * FROM dbo.Users;\n```\n\n## Security\n\n### Principle of Least Privilege\n\n```sql\n-- Grant EXECUTE only, not direct table access\nGRANT EXECUTE ON dbo.GetUserOrders TO AppUser;\n-- User can only access data through the procedure\n```\n\n### Ownership Chaining\n\n```sql\n-- Procedure and tables in same schema = automatic access\nCREATE PROCEDURE dbo.SecureDataAccess\nWITH EXECUTE AS OWNER -- Run as procedure owner\nAS\nBEGIN\n SELECT * FROM dbo.SensitiveData; -- Works even if caller can't access table\nEND;\n```\n\n## Naming Conventions\n\n| Element | Convention | Example |\n|---------|------------|---------|\n| Procedure | PascalCase with verb | `GetUserById`, `CreateOrder` |\n| Parameters | @PascalCase | `@UserId`, `@OrderDate` |\n| Variables | @camelCase or @PascalCase | `@totalAmount`, `@RowCount` |\n| Schema | Always specify | `dbo.ProcedureName` |\n\n## Documentation\n\n```sql\nCREATE PROCEDURE dbo.ProcessDailyReport\n @ReportDate DATE,\n @IncludeDetails BIT = 0\nAS\n/*\nPurpose: Generates daily summary report\nAuthor: Jeremy Longshore\nCreated: 2025-01-15\nModified: 2025-01-18 - Added @IncludeDetails parameter\n\nParameters:\n @ReportDate - Date for the report (required)\n @IncludeDetails - Include line-item details (optional, default 0)\n\nReturns:\n Result set with daily summary\n If @IncludeDetails = 1, second result set with details\n\nExample:\n EXEC dbo.ProcessDailyReport @ReportDate = '2025-01-15', @IncludeDetails = 1;\n*/\nBEGIN\n SET NOCOUNT ON;\n -- Implementation\nEND;\nGO\n```\n","content_type":"text/markdown; charset=utf-8","language":"markdown","size":7957,"content_sha256":"abe2c5b7ca33a81a3afd86f94b24719136120f7ba4717c85c81540291aeea20c"},{"filename":"references/stored_procedure_optimization_techniques.md","content":"# Stored Procedure Optimization Techniques\n\n## Set-Based vs Row-Based Operations\n\n### Avoid Cursors When Possible\n\n```sql\n-- SLOW: Row-by-row cursor processing\nCREATE FUNCTION update_prices_slow()\nRETURNS VOID AS $\nDECLARE\n rec RECORD;\nBEGIN\n FOR rec IN SELECT id, price FROM products LOOP\n UPDATE products SET price = rec.price * 1.1 WHERE id = rec.id;\n END LOOP;\nEND;\n$ LANGUAGE plpgsql;\n\n-- FAST: Set-based operation\nCREATE FUNCTION update_prices_fast()\nRETURNS INT AS $\n UPDATE products SET price = price * 1.1;\n SELECT COUNT(*) FROM products;\n$ LANGUAGE sql;\n```\n\n**Performance difference**: Set-based is typically 10-100x faster.\n\n## Reduce Network Round Trips\n\n### Batch Operations\n\n```sql\n-- SLOW: Multiple calls\nCALL insert_order_item(1, 101, 2);\nCALL insert_order_item(1, 102, 1);\nCALL insert_order_item(1, 103, 5);\n\n-- FAST: Single call with table parameter (SQL Server)\nCREATE PROCEDURE dbo.InsertOrderItems\n @OrderId INT,\n @Items dbo.OrderItemType READONLY\nAS\nBEGIN\n INSERT INTO OrderItems (OrderId, ProductId, Quantity)\n SELECT @OrderId, ProductId, Quantity FROM @Items;\nEND;\n\n-- FAST: Single call with JSON (PostgreSQL/MySQL)\nCREATE FUNCTION insert_order_items(p_order_id INT, p_items JSONB)\nRETURNS VOID AS $\nBEGIN\n INSERT INTO order_items (order_id, product_id, quantity)\n SELECT p_order_id, (item->>'product_id')::INT, (item->>'quantity')::INT\n FROM jsonb_array_elements(p_items) AS item;\nEND;\n$ LANGUAGE plpgsql;\n```\n\n## Efficient Indexing\n\n### Use Covering Indexes\n\n```sql\n-- Index covers all columns needed by query\nCREATE INDEX idx_orders_covering ON orders (customer_id)\n INCLUDE (order_date, total_amount, status);\n\n-- Procedure benefits from index-only scan\nCREATE FUNCTION get_customer_orders(p_customer_id INT)\nRETURNS TABLE(order_date DATE, total DECIMAL, status VARCHAR) AS $\n SELECT order_date, total_amount, status\n FROM orders\n WHERE customer_id = p_customer_id;\n$ LANGUAGE sql STABLE;\n```\n\n### Index for Procedure Parameters\n\n```sql\n-- If procedure frequently filters by status + date\nCREATE INDEX idx_orders_status_date ON orders (status, order_date DESC);\n\n-- Procedure uses this index efficiently\nCREATE FUNCTION get_pending_orders(p_days INT)\nRETURNS SETOF orders AS $\n SELECT * FROM orders\n WHERE status = 'pending'\n AND order_date > CURRENT_DATE - p_days;\n$ LANGUAGE sql STABLE;\n```\n\n## Query Plan Optimization\n\n### PostgreSQL: Analyze Query Plans\n\n```sql\nCREATE FUNCTION analyze_query_example()\nRETURNS TEXT AS $\nDECLARE\n v_plan TEXT;\nBEGIN\n EXPLAIN (ANALYZE, FORMAT TEXT) INTO v_plan\n SELECT * FROM large_table WHERE indexed_col = 123;\n\n RETURN v_plan;\nEND;\n$ LANGUAGE plpgsql;\n```\n\n### SQL Server: Query Hints\n\n```sql\nCREATE PROCEDURE dbo.OptimizedSearch\n @SearchTerm NVARCHAR(100)\nAS\nBEGIN\n SELECT ProductId, ProductName\n FROM Products WITH (INDEX(IX_Products_Name))\n WHERE ProductName LIKE @SearchTerm + '%'\n OPTION (RECOMPILE); -- Fresh plan each execution\nEND;\n```\n\n## Caching and Memoization\n\n### PostgreSQL: Materialized Views\n\n```sql\n-- Create materialized view for expensive aggregations\nCREATE MATERIALIZED VIEW mv_daily_sales AS\nSELECT\n DATE(order_date) AS sale_date,\n SUM(total_amount) AS daily_total,\n COUNT(*) AS order_count\nFROM orders\nGROUP BY DATE(order_date);\n\n-- Procedure uses cached data\nCREATE FUNCTION get_daily_sales(p_date DATE)\nRETURNS TABLE(daily_total DECIMAL, order_count BIGINT) AS $\n SELECT daily_total, order_count\n FROM mv_daily_sales\n WHERE sale_date = p_date;\n$ LANGUAGE sql STABLE;\n\n-- Refresh periodically\nCREATE FUNCTION refresh_sales_cache()\nRETURNS VOID AS $\n REFRESH MATERIALIZED VIEW CONCURRENTLY mv_daily_sales;\n$ LANGUAGE sql;\n```\n\n## Volatility Declarations (PostgreSQL)\n\n```sql\n-- IMMUTABLE: Same input = same output, always\n-- Can be used in indexes, parallelized\nCREATE FUNCTION calculate_tax(amount DECIMAL)\nRETURNS DECIMAL AS $\n SELECT amount * 0.08;\n$ LANGUAGE sql IMMUTABLE;\n\n-- STABLE: Same output within single transaction\n-- Safe for index scans\nCREATE FUNCTION get_current_user_id()\nRETURNS INT AS $\n SELECT current_setting('app.user_id')::INT;\n$ LANGUAGE sql STABLE;\n\n-- VOLATILE: May return different values (default)\n-- Prevents certain optimizations\nCREATE FUNCTION log_and_return(val INT)\nRETURNS INT AS $\nBEGIN\n INSERT INTO log_table (value, logged_at) VALUES (val, NOW());\n RETURN val;\nEND;\n$ LANGUAGE plpgsql VOLATILE;\n```\n\n## Reduce Lock Contention\n\n### Use Appropriate Isolation Levels\n\n```sql\n-- PostgreSQL: Read-only procedure with reduced locking\nCREATE FUNCTION read_only_report()\nRETURNS SETOF report_type AS $\nBEGIN\n SET TRANSACTION ISOLATION LEVEL READ COMMITTED READ ONLY;\n RETURN QUERY SELECT * FROM generate_report();\nEND;\n$ LANGUAGE plpgsql;\n```\n\n### Batch Large Updates\n\n```sql\n-- Instead of updating 1M rows at once\n-- Process in batches to reduce lock time\nCREATE PROCEDURE dbo.BatchUpdate\nAS\nBEGIN\n DECLARE @BatchSize INT = 10000;\n DECLARE @Affected INT = 1;\n\n WHILE @Affected > 0\n BEGIN\n UPDATE TOP (@BatchSize) Products\n SET LastUpdated = GETDATE()\n WHERE LastUpdated \u003c DATEADD(YEAR, -1, GETDATE());\n\n SET @Affected = @@ROWCOUNT;\n\n -- Allow other transactions to proceed\n WAITFOR DELAY '00:00:00.100';\n END\nEND;\n```\n\n## Statistics and Plan Cache\n\n### Keep Statistics Updated\n\n```sql\n-- PostgreSQL\nANALYZE table_name;\n\n-- SQL Server\nUPDATE STATISTICS table_name;\n\n-- MySQL\nANALYZE TABLE table_name;\n```\n\n### Clear Plan Cache When Needed\n\n```sql\n-- SQL Server: Clear specific procedure plan\nEXEC sp_recompile 'dbo.ProcedureName';\n\n-- PostgreSQL: No procedure-specific cache, but:\nDISCARD PLANS; -- Clears all plans in session\n```\n\n## Optimization Checklist\n\n| Technique | Impact | When to Use |\n|-----------|--------|-------------|\n| Set-based operations | High | Always prefer over cursors |\n| Covering indexes | High | Frequent queries with predictable columns |\n| Batch processing | Medium | Large data modifications |\n| Volatility hints | Medium | PostgreSQL pure functions |\n| Query hints | Low | Last resort after other optimizations |\n| Plan recompile | Low | Variable parameter distribution |\n\n## Monitoring and Profiling\n\n```sql\n-- PostgreSQL: Enable timing\nSET track_functions = 'all';\nSELECT * FROM pg_stat_user_functions;\n\n-- SQL Server: Execution statistics\nSELECT\n OBJECT_NAME(object_id) AS ProcedureName,\n execution_count,\n total_worker_time / 1000 AS TotalCPU_ms,\n total_elapsed_time / 1000 AS TotalElapsed_ms,\n total_logical_reads\nFROM sys.dm_exec_procedure_stats\nORDER BY total_worker_time DESC;\n\n-- MySQL: Performance schema\nSELECT * FROM performance_schema.events_statements_summary_by_digest\nORDER BY SUM_TIMER_WAIT DESC LIMIT 10;\n```\n","content_type":"text/markdown; charset=utf-8","language":"markdown","size":6808,"content_sha256":"0c900e00eabcd93777fae8d05034be31222095da6bcbb6f248b88381d0d00f33"},{"filename":"scripts/database_connection_test.py","content":"#!/usr/bin/env python3\n\"\"\"\nDatabase Connection Test\n\nTest connectivity to PostgreSQL, MySQL, or SQL Server databases.\nVerifies credentials and permissions needed for stored procedure operations.\n\nAuthor: Jeremy Longshore \[email protected]>\nVersion: 2.0.0\nLicense: MIT\n\"\"\"\n\nimport sys\nimport argparse\nimport subprocess\nfrom dataclasses import dataclass\nfrom typing import List, Optional\n\n\n@dataclass\nclass ConnectionResult:\n \"\"\"Result of connection test.\"\"\"\n\n success: bool\n message: str\n version: Optional[str] = None\n permissions: List[str] = None\n\n\ndef check_postgresql(host: str, port: int, database: str, user: str, password: str = None) -> ConnectionResult:\n \"\"\"Test PostgreSQL connection and permissions.\"\"\"\n import os\n\n env = os.environ.copy()\n if password:\n env[\"PGPASSWORD\"] = password\n\n # Test basic connectivity\n cmd = [\"psql\", \"-h\", host, \"-p\", str(port), \"-d\", database, \"-U\", user, \"-c\", \"SELECT version();\"]\n\n try:\n result = subprocess.run(cmd, env=env, capture_output=True, text=True, timeout=10)\n if result.returncode != 0:\n return ConnectionResult(success=False, message=f\"Connection failed: {result.stderr.strip()}\")\n\n version = result.stdout.strip().split(\"\\n\")[2].strip() if result.stdout else None\n\n # Check permissions\n perm_cmd = [\n \"psql\",\n \"-h\",\n host,\n \"-p\",\n str(port),\n \"-d\",\n database,\n \"-U\",\n user,\n \"-t\",\n \"-c\",\n \"\"\"\n SELECT string_agg(privilege_type, ', ')\n FROM information_schema.role_table_grants\n WHERE grantee = current_user;\n \"\"\",\n ]\n perm_result = subprocess.run(perm_cmd, env=env, capture_output=True, text=True, timeout=10)\n permissions = perm_result.stdout.strip().split(\", \") if perm_result.returncode == 0 else []\n\n return ConnectionResult(success=True, message=\"Connection successful\", version=version, permissions=permissions)\n\n except subprocess.TimeoutExpired:\n return ConnectionResult(success=False, message=\"Connection timed out\")\n except FileNotFoundError:\n return ConnectionResult(success=False, message=\"psql not found - install PostgreSQL client\")\n\n\ndef check_mysql(host: str, port: int, database: str, user: str, password: str = None) -> ConnectionResult:\n \"\"\"Test MySQL connection and permissions.\"\"\"\n cmd = [\"mysql\", \"-h\", host, \"-P\", str(port), \"-D\", database, \"-u\", user]\n if password:\n cmd.append(f\"-p{password}\")\n cmd.extend([\"-e\", \"SELECT VERSION();\"])\n\n try:\n result = subprocess.run(cmd, capture_output=True, text=True, timeout=10)\n if result.returncode != 0:\n return ConnectionResult(success=False, message=f\"Connection failed: {result.stderr.strip()}\")\n\n version = result.stdout.strip().split(\"\\n\")[-1] if result.stdout else None\n\n # Check permissions\n perm_cmd = [\"mysql\", \"-h\", host, \"-P\", str(port), \"-D\", database, \"-u\", user]\n if password:\n perm_cmd.append(f\"-p{password}\")\n perm_cmd.extend([\"-e\", \"SHOW GRANTS;\"])\n\n perm_result = subprocess.run(perm_cmd, capture_output=True, text=True, timeout=10)\n permissions = []\n if perm_result.returncode == 0:\n for line in perm_result.stdout.split(\"\\n\"):\n if \"GRANT\" in line:\n permissions.append(line.strip())\n\n return ConnectionResult(success=True, message=\"Connection successful\", version=version, permissions=permissions)\n\n except subprocess.TimeoutExpired:\n return ConnectionResult(success=False, message=\"Connection timed out\")\n except FileNotFoundError:\n return ConnectionResult(success=False, message=\"mysql not found - install MySQL client\")\n\n\ndef check_sqlserver(host: str, port: int, database: str, user: str, password: str = None) -> ConnectionResult:\n \"\"\"Test SQL Server connection and permissions.\"\"\"\n cmd = [\"sqlcmd\", \"-S\", f\"{host},{port}\", \"-d\", database, \"-U\", user, \"-Q\", \"SELECT @@VERSION;\"]\n if password:\n cmd.extend([\"-P\", password])\n\n try:\n result = subprocess.run(cmd, capture_output=True, text=True, timeout=10)\n if result.returncode != 0:\n return ConnectionResult(success=False, message=f\"Connection failed: {result.stderr.strip()}\")\n\n version = result.stdout.strip().split(\"\\n\")[2] if result.stdout else None\n\n # Check permissions\n perm_cmd = [\n \"sqlcmd\",\n \"-S\",\n f\"{host},{port}\",\n \"-d\",\n database,\n \"-U\",\n user,\n \"-Q\",\n \"SELECT permission_name FROM fn_my_permissions(NULL, 'DATABASE');\",\n ]\n if password:\n perm_cmd.extend([\"-P\", password])\n\n perm_result = subprocess.run(perm_cmd, capture_output=True, text=True, timeout=10)\n permissions = []\n if perm_result.returncode == 0:\n for line in perm_result.stdout.split(\"\\n\"):\n if line.strip() and not line.startswith(\"-\") and \"permission_name\" not in line.lower():\n permissions.append(line.strip())\n\n return ConnectionResult(success=True, message=\"Connection successful\", version=version, permissions=permissions)\n\n except subprocess.TimeoutExpired:\n return ConnectionResult(success=False, message=\"Connection timed out\")\n except FileNotFoundError:\n return ConnectionResult(success=False, message=\"sqlcmd not found - install SQL Server tools\")\n\n\ndef main():\n parser = argparse.ArgumentParser(description=\"Test database connectivity for stored procedure deployment\")\n parser.add_argument(\n \"--db-type\", \"-t\", required=True, choices=[\"postgresql\", \"mysql\", \"sqlserver\"], help=\"Database type\"\n )\n parser.add_argument(\"--host\", \"-H\", default=\"localhost\", help=\"Database host\")\n parser.add_argument(\"--port\", \"-P\", type=int, help=\"Database port\")\n parser.add_argument(\"--database\", \"-d\", required=True, help=\"Database name\")\n parser.add_argument(\"--user\", \"-u\", required=True, help=\"Database user\")\n parser.add_argument(\"--password\", \"-p\", help=\"Database password\")\n\n args = parser.parse_args()\n\n default_ports = {\n \"postgresql\": 5432,\n \"mysql\": 3306,\n \"sqlserver\": 1433,\n }\n port = args.port or default_ports.get(args.db_type, 5432)\n\n print(f\"Testing connection to {args.db_type}://{args.host}:{port}/{args.database}\")\n print(f\"User: {args.user}\")\n print()\n\n testers = {\n \"postgresql\": check_postgresql,\n \"mysql\": check_mysql,\n \"sqlserver\": check_sqlserver,\n }\n\n result = testers[args.db_type](args.host, port, args.database, args.user, args.password)\n\n if result.success:\n print(\"Status: SUCCESS\")\n print(f\"Version: {result.version or 'Unknown'}\")\n if result.permissions:\n print(\"\\nPermissions:\")\n for perm in result.permissions[:10]: # Limit output\n print(f\" - {perm}\")\n if len(result.permissions) > 10:\n print(f\" ... and {len(result.permissions) - 10} more\")\n return 0\n else:\n print(\"Status: FAILED\")\n print(f\"Error: {result.message}\")\n return 1\n\n\nif __name__ == \"__main__\":\n sys.exit(main())\n","content_type":"text/x-python; charset=utf-8","language":"python","size":7395,"content_sha256":"38ddff8a01c45dd4e4e9ed926ef5e678e045c8909009bf660d6aa51487f298c3"},{"filename":"scripts/README.md","content":"# Scripts\n\nBundled resources for stored-procedure-generator skill\n\n- [x] database_connection_test.py: Tests the database connection using provided credentials and outputs the connection status.\n- [x] stored_procedure_syntax_validator.py: Validates the syntax of the generated stored procedure against the target database (PostgreSQL, MySQL, SQL Server).\n- [x] stored_procedure_deployer.py: Deploys the generated stored procedure to the target database.\n\n## Auto-Generated\n\nScripts generated on 2025-12-10 03:48:17\n","content_type":"text/markdown; charset=utf-8","language":"markdown","size":514,"content_sha256":"a1b3f4f77852d1d2b5c1792d0ee01fdffdfb97974e17a68f7659b4b9f0f297cf"},{"filename":"scripts/stored_procedure_deployer.py","content":"#!/usr/bin/env python3\n\"\"\"\nStored Procedure Deployer\n\nDeploy stored procedures to PostgreSQL, MySQL, or SQL Server databases.\nSupports dry-run mode, transaction wrapping, and automatic rollback scripts.\n\nAuthor: Jeremy Longshore \[email protected]>\nVersion: 2.0.0\nLicense: MIT\n\"\"\"\n\nimport sys\nimport argparse\nimport subprocess\nfrom pathlib import Path\nfrom datetime import datetime\nfrom typing import Dict, Optional\nfrom dataclasses import dataclass\n\n\n@dataclass\nclass DeploymentResult:\n \"\"\"Result of deployment operation.\"\"\"\n\n success: bool\n message: str\n procedure_name: Optional[str] = None\n rollback_sql: Optional[str] = None\n execution_time: float = 0.0\n\n\nclass PostgreSQLDeployer:\n \"\"\"Deploy stored procedures to PostgreSQL.\"\"\"\n\n def __init__(self, host: str, port: int, database: str, user: str, password: str = None):\n self.host = host\n self.port = port\n self.database = database\n self.user = user\n self.password = password\n\n def _get_psql_env(self) -> Dict[str, str]:\n \"\"\"Get environment variables for psql.\"\"\"\n import os\n\n env = os.environ.copy()\n if self.password:\n env[\"PGPASSWORD\"] = self.password\n return env\n\n def _run_psql(self, sql: str, dry_run: bool = False) -> DeploymentResult:\n \"\"\"Execute SQL via psql.\"\"\"\n if dry_run:\n return DeploymentResult(success=True, message=f\"DRY RUN: Would execute SQL ({len(sql)} chars)\")\n\n cmd = [\n \"psql\",\n \"-h\",\n self.host,\n \"-p\",\n str(self.port),\n \"-d\",\n self.database,\n \"-U\",\n self.user,\n \"-v\",\n \"ON_ERROR_STOP=1\",\n \"-c\",\n sql,\n ]\n\n start_time = datetime.now()\n try:\n result = subprocess.run(cmd, env=self._get_psql_env(), capture_output=True, text=True, timeout=60)\n elapsed = (datetime.now() - start_time).total_seconds()\n\n if result.returncode == 0:\n return DeploymentResult(\n success=True, message=result.stdout.strip() or \"Executed successfully\", execution_time=elapsed\n )\n else:\n return DeploymentResult(\n success=False, message=f\"Error: {result.stderr.strip()}\", execution_time=elapsed\n )\n except subprocess.TimeoutExpired:\n return DeploymentResult(success=False, message=\"Execution timed out\")\n except FileNotFoundError:\n return DeploymentResult(success=False, message=\"psql not found - install PostgreSQL client\")\n\n def deploy(self, sql: str, dry_run: bool = False) -> DeploymentResult:\n \"\"\"Deploy a stored procedure.\"\"\"\n # Extract procedure name for rollback\n import re\n\n name_match = re.search(\n r\"CREATE\\s+(?:OR\\s+REPLACE\\s+)?(?:FUNCTION|PROCEDURE)\\s+(\\w+(?:\\.\\w+)?)\", sql, re.IGNORECASE\n )\n proc_name = name_match.group(1) if name_match else None\n\n result = self._run_psql(sql, dry_run)\n result.procedure_name = proc_name\n\n if proc_name:\n # Detect if it's a function or procedure\n if \"FUNCTION\" in sql.upper():\n result.rollback_sql = f\"DROP FUNCTION IF EXISTS {proc_name};\"\n else:\n result.rollback_sql = f\"DROP PROCEDURE IF EXISTS {proc_name};\"\n\n return result\n\n\nclass MySQLDeployer:\n \"\"\"Deploy stored procedures to MySQL.\"\"\"\n\n def __init__(self, host: str, port: int, database: str, user: str, password: str = None):\n self.host = host\n self.port = port\n self.database = database\n self.user = user\n self.password = password\n\n def _run_mysql(self, sql: str, dry_run: bool = False) -> DeploymentResult:\n \"\"\"Execute SQL via mysql client.\"\"\"\n if dry_run:\n return DeploymentResult(success=True, message=f\"DRY RUN: Would execute SQL ({len(sql)} chars)\")\n\n cmd = [\n \"mysql\",\n \"-h\",\n self.host,\n \"-P\",\n str(self.port),\n \"-D\",\n self.database,\n \"-u\",\n self.user,\n ]\n if self.password:\n cmd.append(f\"-p{self.password}\")\n\n start_time = datetime.now()\n try:\n result = subprocess.run(cmd, input=sql, capture_output=True, text=True, timeout=60)\n elapsed = (datetime.now() - start_time).total_seconds()\n\n if result.returncode == 0:\n return DeploymentResult(\n success=True, message=result.stdout.strip() or \"Executed successfully\", execution_time=elapsed\n )\n else:\n return DeploymentResult(\n success=False, message=f\"Error: {result.stderr.strip()}\", execution_time=elapsed\n )\n except subprocess.TimeoutExpired:\n return DeploymentResult(success=False, message=\"Execution timed out\")\n except FileNotFoundError:\n return DeploymentResult(success=False, message=\"mysql not found - install MySQL client\")\n\n def deploy(self, sql: str, dry_run: bool = False) -> DeploymentResult:\n \"\"\"Deploy a stored procedure.\"\"\"\n import re\n\n name_match = re.search(r\"CREATE\\s+(?:DEFINER\\s*=\\s*[^\\s]+\\s+)?PROCEDURE\\s+(\\w+(?:\\.\\w+)?)\", sql, re.IGNORECASE)\n proc_name = name_match.group(1) if name_match else None\n\n result = self._run_mysql(sql, dry_run)\n result.procedure_name = proc_name\n\n if proc_name:\n result.rollback_sql = f\"DROP PROCEDURE IF EXISTS {proc_name};\"\n\n return result\n\n\nclass SQLServerDeployer:\n \"\"\"Deploy stored procedures to SQL Server.\"\"\"\n\n def __init__(self, host: str, port: int, database: str, user: str, password: str = None):\n self.host = host\n self.port = port\n self.database = database\n self.user = user\n self.password = password\n\n def _run_sqlcmd(self, sql: str, dry_run: bool = False) -> DeploymentResult:\n \"\"\"Execute SQL via sqlcmd.\"\"\"\n if dry_run:\n return DeploymentResult(success=True, message=f\"DRY RUN: Would execute SQL ({len(sql)} chars)\")\n\n cmd = [\"sqlcmd\", \"-S\", f\"{self.host},{self.port}\", \"-d\", self.database, \"-U\", self.user, \"-Q\", sql]\n if self.password:\n cmd.extend([\"-P\", self.password])\n\n start_time = datetime.now()\n try:\n result = subprocess.run(cmd, capture_output=True, text=True, timeout=60)\n elapsed = (datetime.now() - start_time).total_seconds()\n\n if result.returncode == 0:\n return DeploymentResult(\n success=True, message=result.stdout.strip() or \"Executed successfully\", execution_time=elapsed\n )\n else:\n return DeploymentResult(\n success=False, message=f\"Error: {result.stderr.strip()}\", execution_time=elapsed\n )\n except subprocess.TimeoutExpired:\n return DeploymentResult(success=False, message=\"Execution timed out\")\n except FileNotFoundError:\n return DeploymentResult(success=False, message=\"sqlcmd not found - install SQL Server tools\")\n\n def deploy(self, sql: str, dry_run: bool = False) -> DeploymentResult:\n \"\"\"Deploy a stored procedure.\"\"\"\n import re\n\n name_match = re.search(\n r\"CREATE\\s+(?:OR\\s+ALTER\\s+)?PROC(?:EDURE)?\\s+(\\[?\\w+\\]?(?:\\.\\[?\\w+\\]?)?)\", sql, re.IGNORECASE\n )\n proc_name = name_match.group(1) if name_match else None\n\n result = self._run_sqlcmd(sql, dry_run)\n result.procedure_name = proc_name\n\n if proc_name:\n result.rollback_sql = f\"DROP PROCEDURE IF EXISTS {proc_name};\"\n\n return result\n\n\ndef get_deployer(db_type: str, host: str, port: int, database: str, user: str, password: str = None):\n \"\"\"Factory to get appropriate deployer.\"\"\"\n deployers = {\n \"postgresql\": PostgreSQLDeployer,\n \"mysql\": MySQLDeployer,\n \"sqlserver\": SQLServerDeployer,\n }\n\n default_ports = {\n \"postgresql\": 5432,\n \"mysql\": 3306,\n \"sqlserver\": 1433,\n }\n\n if db_type not in deployers:\n raise ValueError(f\"Unknown database type: {db_type}\")\n\n actual_port = port or default_ports.get(db_type, 5432)\n return deployers[db_type](host, actual_port, database, user, password)\n\n\ndef main():\n parser = argparse.ArgumentParser(description=\"Deploy stored procedures to PostgreSQL, MySQL, or SQL Server\")\n parser.add_argument(\"file\", help=\"SQL file to deploy\")\n parser.add_argument(\n \"--db-type\", \"-t\", required=True, choices=[\"postgresql\", \"mysql\", \"sqlserver\"], help=\"Database type\"\n )\n parser.add_argument(\"--host\", \"-H\", default=\"localhost\", help=\"Database host\")\n parser.add_argument(\"--port\", \"-P\", type=int, help=\"Database port\")\n parser.add_argument(\"--database\", \"-d\", required=True, help=\"Database name\")\n parser.add_argument(\"--user\", \"-u\", required=True, help=\"Database user\")\n parser.add_argument(\"--password\", \"-p\", help=\"Database password\")\n parser.add_argument(\"--dry-run\", action=\"store_true\", help=\"Show what would be done\")\n parser.add_argument(\"--save-rollback\", help=\"Save rollback script to file\")\n\n args = parser.parse_args()\n\n file_path = Path(args.file)\n if not file_path.exists():\n print(f\"Error: File not found: {file_path}\")\n return 1\n\n sql = file_path.read_text()\n\n print(f\"Deploying: {file_path}\")\n print(f\"Database: {args.db_type}://{args.host}/{args.database}\")\n if args.dry_run:\n print(\"MODE: DRY RUN\")\n print()\n\n try:\n deployer = get_deployer(args.db_type, args.host, args.port, args.database, args.user, args.password)\n\n result = deployer.deploy(sql, args.dry_run)\n\n if result.procedure_name:\n print(f\"Procedure: {result.procedure_name}\")\n\n if result.success:\n print(\"Status: SUCCESS\")\n print(f\"Message: {result.message}\")\n if result.execution_time:\n print(f\"Time: {result.execution_time:.2f}s\")\n\n if result.rollback_sql:\n print(f\"\\nRollback SQL: {result.rollback_sql}\")\n if args.save_rollback:\n Path(args.save_rollback).write_text(result.rollback_sql)\n print(f\"Saved to: {args.save_rollback}\")\n\n return 0\n else:\n print(\"Status: FAILED\")\n print(f\"Error: {result.message}\")\n return 1\n\n except Exception as e:\n print(f\"Error: {e}\")\n return 1\n\n\nif __name__ == \"__main__\":\n sys.exit(main())\n","content_type":"text/x-python; charset=utf-8","language":"python","size":10766,"content_sha256":"7d884f23ada069703f44c2562ad049dd02a4a17c8c8f4256b784268b1f385e7d"},{"filename":"scripts/stored_procedure_syntax_validator.py","content":"#!/usr/bin/env python3\n\"\"\"\nStored Procedure Syntax Validator\n\nValidates SQL stored procedure syntax for PostgreSQL, MySQL, and SQL Server.\nPerforms static analysis without requiring database connection.\n\nAuthor: Jeremy Longshore \[email protected]>\nVersion: 2.0.0\nLicense: MIT\n\"\"\"\n\nimport re\nimport sys\nimport argparse\nfrom pathlib import Path\nfrom dataclasses import dataclass\nfrom typing import List, Optional\n\n\n@dataclass\nclass ValidationResult:\n \"\"\"Result of syntax validation.\"\"\"\n\n valid: bool\n errors: List[str]\n warnings: List[str]\n db_type: str\n procedure_name: Optional[str] = None\n\n\nclass PostgreSQLValidator:\n \"\"\"Validate PostgreSQL/PL/pgSQL stored procedure syntax.\"\"\"\n\n REQUIRED_PATTERNS = [\n (r\"CREATE\\s+(OR\\s+REPLACE\\s+)?(FUNCTION|PROCEDURE)\", \"Missing CREATE FUNCTION/PROCEDURE\"),\n (r\"LANGUAGE\\s+(plpgsql|sql|plpython\\w*)\", \"Missing LANGUAGE clause\"),\n ]\n\n FUNCTION_PATTERNS = [\n (r\"RETURNS\\s+(\\w+|SETOF\\s+\\w+|TABLE\\s*\\()\", \"Function missing RETURNS clause\"),\n ]\n\n WARNING_PATTERNS = [\n (r\"\\bSELECT\\s+\\*\\b\", \"Avoid SELECT * - specify columns explicitly\"),\n (r\"SECURITY\\s+DEFINER(?!\\s+SET\\s+search_path)\", \"SECURITY DEFINER without SET search_path is risky\"),\n (r'\\bEXECUTE\\s+[\\'\"]', \"Dynamic SQL detected - ensure proper parameterization\"),\n ]\n\n def validate(self, sql: str) -> ValidationResult:\n errors = []\n warnings = []\n proc_name = None\n\n # Extract procedure name\n name_match = re.search(\n r\"CREATE\\s+(?:OR\\s+REPLACE\\s+)?(?:FUNCTION|PROCEDURE)\\s+(\\w+(?:\\.\\w+)?)\", sql, re.IGNORECASE\n )\n if name_match:\n proc_name = name_match.group(1)\n\n # Check required patterns\n for pattern, error_msg in self.REQUIRED_PATTERNS:\n if not re.search(pattern, sql, re.IGNORECASE):\n errors.append(error_msg)\n\n # Check if function has RETURNS\n if re.search(r\"CREATE\\s+(?:OR\\s+REPLACE\\s+)?FUNCTION\", sql, re.IGNORECASE):\n for pattern, error_msg in self.FUNCTION_PATTERNS:\n if not re.search(pattern, sql, re.IGNORECASE):\n errors.append(error_msg)\n\n # Check warnings\n for pattern, warning_msg in self.WARNING_PATTERNS:\n if re.search(pattern, sql, re.IGNORECASE):\n warnings.append(warning_msg)\n\n # Check balanced $ delimiters\n dollar_count = sql.count(\"$\")\n if dollar_count % 2 != 0:\n errors.append(\"Unbalanced $ delimiters\")\n\n # Check for BEGIN/END balance in plpgsql\n if \"plpgsql\" in sql.lower():\n begin_count = len(re.findall(r\"\\bBEGIN\\b\", sql, re.IGNORECASE))\n end_count = len(re.findall(r\"\\bEND\\b\", sql, re.IGNORECASE))\n # Account for EXCEPTION blocks which don't need separate END\n if begin_count > end_count:\n errors.append(f\"Unbalanced BEGIN/END blocks: {begin_count} BEGIN vs {end_count} END\")\n\n return ValidationResult(\n valid=len(errors) == 0, errors=errors, warnings=warnings, db_type=\"postgresql\", procedure_name=proc_name\n )\n\n\nclass MySQLValidator:\n \"\"\"Validate MySQL stored procedure syntax.\"\"\"\n\n REQUIRED_PATTERNS = [\n (r\"CREATE\\s+(?:DEFINER\\s*=\\s*[^\\s]+\\s+)?PROCEDURE\", \"Missing CREATE PROCEDURE\"),\n (r\"\\bBEGIN\\b\", \"Missing BEGIN keyword\"),\n (r\"\\bEND\\b\", \"Missing END keyword\"),\n ]\n\n WARNING_PATTERNS = [\n (r\"\\bSELECT\\s+\\*\\b\", \"Avoid SELECT * - specify columns explicitly\"),\n (r\"CONCAT\\s*\\([^)]*\\+[^)]*\\)\", \"Potential SQL injection - use prepared statements\"),\n (r\"@\\w+\\s*:=\", \"User variables persist across calls - initialize explicitly\"),\n ]\n\n def validate(self, sql: str) -> ValidationResult:\n errors = []\n warnings = []\n proc_name = None\n\n # Extract procedure name\n name_match = re.search(r\"CREATE\\s+(?:DEFINER\\s*=\\s*[^\\s]+\\s+)?PROCEDURE\\s+(\\w+(?:\\.\\w+)?)\", sql, re.IGNORECASE)\n if name_match:\n proc_name = name_match.group(1)\n\n # Check DELIMITER usage\n if \"DELIMITER\" not in sql.upper() and sql.count(\";\") > 1:\n warnings.append(\"Multiple semicolons without DELIMITER - may cause issues\")\n\n # Check required patterns\n for pattern, error_msg in self.REQUIRED_PATTERNS:\n if not re.search(pattern, sql, re.IGNORECASE):\n errors.append(error_msg)\n\n # Check warnings\n for pattern, warning_msg in self.WARNING_PATTERNS:\n if re.search(pattern, sql, re.IGNORECASE):\n warnings.append(warning_msg)\n\n # Check BEGIN/END balance\n begin_count = len(re.findall(r\"\\bBEGIN\\b\", sql, re.IGNORECASE))\n end_count = len(re.findall(r\"\\bEND\\b\", sql, re.IGNORECASE))\n if begin_count != end_count:\n errors.append(f\"Unbalanced BEGIN/END blocks: {begin_count} BEGIN vs {end_count} END\")\n\n # Check parameter modes\n params_match = re.search(r\"PROCEDURE\\s+\\w+\\s*\\(([^)]+)\\)\", sql, re.IGNORECASE | re.DOTALL)\n if params_match:\n params = params_match.group(1)\n if params.strip() and not re.search(r\"\\b(IN|OUT|INOUT)\\b\", params, re.IGNORECASE):\n warnings.append(\"Parameters should specify IN/OUT/INOUT mode\")\n\n return ValidationResult(\n valid=len(errors) == 0, errors=errors, warnings=warnings, db_type=\"mysql\", procedure_name=proc_name\n )\n\n\nclass SQLServerValidator:\n \"\"\"Validate SQL Server T-SQL stored procedure syntax.\"\"\"\n\n REQUIRED_PATTERNS = [\n (r\"CREATE\\s+(OR\\s+ALTER\\s+)?PROC(EDURE)?\", \"Missing CREATE PROCEDURE\"),\n (r\"\\bAS\\b\", \"Missing AS keyword\"),\n (r\"\\bBEGIN\\b\", \"Missing BEGIN keyword\"),\n (r\"\\bEND\\b\", \"Missing END keyword\"),\n ]\n\n WARNING_PATTERNS = [\n (r\"\\bSELECT\\s+\\*\\b\", \"Avoid SELECT * - specify columns explicitly\"),\n (r'EXEC\\s*\\(\\s*[\\'\"]', \"Dynamic SQL detected - use sp_executesql with parameters\"),\n (r\"(?\u003c!SET\\s)NOCOUNT\", \"Consider SET NOCOUNT ON for better performance\"),\n ]\n\n def validate(self, sql: str) -> ValidationResult:\n errors = []\n warnings = []\n proc_name = None\n\n # Extract procedure name\n name_match = re.search(\n r\"CREATE\\s+(?:OR\\s+ALTER\\s+)?PROC(?:EDURE)?\\s+(\\[?\\w+\\]?(?:\\.\\[?\\w+\\]?)?)\", sql, re.IGNORECASE\n )\n if name_match:\n proc_name = name_match.group(1)\n\n # Check required patterns\n for pattern, error_msg in self.REQUIRED_PATTERNS:\n if not re.search(pattern, sql, re.IGNORECASE):\n errors.append(error_msg)\n\n # Check warnings\n for pattern, warning_msg in self.WARNING_PATTERNS:\n if re.search(pattern, sql, re.IGNORECASE):\n warnings.append(warning_msg)\n\n # Check for SET NOCOUNT ON\n if not re.search(r\"SET\\s+NOCOUNT\\s+ON\", sql, re.IGNORECASE):\n warnings.append(\"Missing SET NOCOUNT ON - recommended for performance\")\n\n # Check BEGIN/END balance\n begin_count = len(re.findall(r\"\\bBEGIN\\b\", sql, re.IGNORECASE))\n end_count = len(re.findall(r\"\\bEND\\b\", sql, re.IGNORECASE))\n if begin_count != end_count:\n errors.append(f\"Unbalanced BEGIN/END blocks: {begin_count} BEGIN vs {end_count} END\")\n\n # Check for schema qualification\n if proc_name and \".\" not in proc_name:\n warnings.append(\"Procedure should be schema-qualified (e.g., dbo.ProcedureName)\")\n\n # Check for GO statement\n if not re.search(r\"\\bGO\\b\", sql, re.IGNORECASE):\n warnings.append(\"Missing GO batch terminator\")\n\n return ValidationResult(\n valid=len(errors) == 0, errors=errors, warnings=warnings, db_type=\"sqlserver\", procedure_name=proc_name\n )\n\n\ndef detect_db_type(sql: str) -> str:\n \"\"\"Auto-detect database type from SQL syntax.\"\"\"\n sql_upper = sql.upper()\n\n # PostgreSQL indicators\n if \"LANGUAGE PLPGSQL\" in sql_upper or \"$\" in sql or \"RETURNS SETOF\" in sql_upper:\n return \"postgresql\"\n\n # MySQL indicators\n if \"DELIMITER\" in sql_upper or \"DEFINER=\" in sql_upper or \"SIGNAL SQLSTATE\" in sql_upper:\n return \"mysql\"\n\n # SQL Server indicators\n if \"@\" in sql and (\"AS BEGIN\" in sql_upper or \"SET NOCOUNT\" in sql_upper):\n return \"sqlserver\"\n\n return \"unknown\"\n\n\ndef validate_file(file_path: Path, db_type: str = None) -> ValidationResult:\n \"\"\"Validate a SQL file.\"\"\"\n if not file_path.exists():\n return ValidationResult(valid=False, errors=[f\"File not found: {file_path}\"], warnings=[], db_type=\"unknown\")\n\n sql = file_path.read_text()\n\n # Auto-detect if not specified\n if not db_type:\n db_type = detect_db_type(sql)\n\n validators = {\n \"postgresql\": PostgreSQLValidator(),\n \"mysql\": MySQLValidator(),\n \"sqlserver\": SQLServerValidator(),\n }\n\n if db_type not in validators:\n return ValidationResult(\n valid=False,\n errors=[f\"Unknown database type: {db_type}. Use: postgresql, mysql, sqlserver\"],\n warnings=[],\n db_type=db_type,\n )\n\n return validators[db_type].validate(sql)\n\n\ndef main():\n parser = argparse.ArgumentParser(\n description=\"Validate stored procedure syntax for PostgreSQL, MySQL, or SQL Server\"\n )\n parser.add_argument(\"file\", help=\"SQL file to validate\")\n parser.add_argument(\n \"--db-type\",\n \"-t\",\n choices=[\"postgresql\", \"mysql\", \"sqlserver\", \"auto\"],\n default=\"auto\",\n help=\"Database type (default: auto-detect)\",\n )\n parser.add_argument(\"--verbose\", \"-v\", action=\"store_true\", help=\"Verbose output\")\n\n args = parser.parse_args()\n\n file_path = Path(args.file)\n db_type = None if args.db_type == \"auto\" else args.db_type\n\n print(f\"Validating: {file_path}\")\n\n result = validate_file(file_path, db_type)\n\n print(f\"Database type: {result.db_type}\")\n if result.procedure_name:\n print(f\"Procedure: {result.procedure_name}\")\n print()\n\n if result.errors:\n print(\"ERRORS:\")\n for error in result.errors:\n print(f\" - {error}\")\n print()\n\n if result.warnings:\n print(\"WARNINGS:\")\n for warning in result.warnings:\n print(f\" - {warning}\")\n print()\n\n if result.valid:\n print(\"Result: VALID\")\n return 0\n else:\n print(\"Result: INVALID\")\n return 1\n\n\nif __name__ == \"__main__\":\n sys.exit(main())\n","content_type":"text/x-python; charset=utf-8","language":"python","size":10572,"content_sha256":"94e644f3331207e6f85e9620b32072589e218dda1da346876c9ea29c6ec8c35b"}],"content_json":{"type":"doc","content":[{"type":"heading","attrs":{"level":1},"content":[{"text":"Stored Procedure Generator","type":"text"}]},{"type":"paragraph","content":[{"text":"Generate production-ready stored procedures for PostgreSQL, MySQL, and SQL Server with proper error handling, transaction management, and security best practices.","type":"text"}]},{"type":"heading","attrs":{"level":2},"content":[{"text":"Prerequisites","type":"text"}]},{"type":"bullet_list","content":[{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Database connection credentials (host, port, database, user, password)","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Appropriate permissions: CREATE PROCEDURE, CREATE FUNCTION, EXECUTE","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Target database type identified (PostgreSQL, MySQL, or SQL Server)","type":"text"}]}]}]},{"type":"heading","attrs":{"level":2},"content":[{"text":"Instructions","type":"text"}]},{"type":"heading","attrs":{"level":3},"content":[{"text":"Step 1: Identify Database Type and Requirements","type":"text"}]},{"type":"paragraph","content":[{"text":"Determine the target database and procedure requirements:","type":"text"}]},{"type":"code_block","attrs":{"wrap":false,"language":"sql"},"content":[{"text":"-- PostgreSQL: Check version and extensions\nSELECT version();\n\\dx\n\n-- MySQL: Check version and settings\nSELECT VERSION();\nSHOW VARIABLES LIKE 'sql_mode';\n\n-- SQL Server: Check version and edition\nSELECT @@VERSION;","type":"text"}]},{"type":"heading","attrs":{"level":3},"content":[{"text":"Step 2: Generate Stored Procedure","type":"text"}]},{"type":"paragraph","content":[{"text":"PostgreSQL Function (PL/pgSQL):","type":"text","marks":[{"type":"strong"}]}]},{"type":"code_block","attrs":{"wrap":false,"language":"sql"},"content":[{"text":"CREATE OR REPLACE FUNCTION get_user_by_id(p_user_id INTEGER)\nRETURNS TABLE(id INTEGER, username VARCHAR, email VARCHAR, created_at TIMESTAMP)\nLANGUAGE plpgsql\nAS $\nBEGIN\n RETURN QUERY\n SELECT u.id, u.username, u.email, u.created_at\n FROM users u\n WHERE u.id = p_user_id;\n\n IF NOT FOUND THEN\n RAISE EXCEPTION 'User with ID % not found', p_user_id\n USING ERRCODE = 'P0002';\n END IF;\nEND;\n$;","type":"text"}]},{"type":"paragraph","content":[{"text":"MySQL Stored Procedure:","type":"text","marks":[{"type":"strong"}]}]},{"type":"code_block","attrs":{"wrap":false,"language":"sql"},"content":[{"text":"DELIMITER //\nCREATE PROCEDURE GetUserById(IN p_user_id INT)\nBEGIN\n DECLARE user_exists INT DEFAULT 0;\n\n SELECT COUNT(*) INTO user_exists FROM users WHERE id = p_user_id;\n\n IF user_exists = 0 THEN\n SIGNAL SQLSTATE '45000' # 45000 = configured value\n SET MESSAGE_TEXT = 'User not found';\n END IF;\n\n SELECT id, username, email, created_at\n FROM users\n WHERE id = p_user_id;\nEND //\nDELIMITER ;","type":"text"}]},{"type":"paragraph","content":[{"text":"SQL Server Stored Procedure (T-SQL):","type":"text","marks":[{"type":"strong"}]}]},{"type":"code_block","attrs":{"wrap":false,"language":"sql"},"content":[{"text":"CREATE PROCEDURE dbo.GetUserById\n @UserId INT\nAS\nBEGIN\n SET NOCOUNT ON;\n\n IF NOT EXISTS (SELECT 1 FROM dbo.Users WHERE Id = @UserId)\n BEGIN\n RAISERROR('User with ID %d not found', 16, 1, @UserId);\n RETURN;\n END\n\n SELECT Id, Username, Email, CreatedAt\n FROM dbo.Users\n WHERE Id = @UserId;\nEND;\nGO","type":"text"}]},{"type":"heading","attrs":{"level":3},"content":[{"text":"Step 3: Add Transaction Management","type":"text"}]},{"type":"paragraph","content":[{"text":"PostgreSQL with Transaction:","type":"text","marks":[{"type":"strong"}]}]},{"type":"code_block","attrs":{"wrap":false,"language":"sql"},"content":[{"text":"CREATE OR REPLACE FUNCTION transfer_funds(\n p_from_account INTEGER,\n p_to_account INTEGER,\n p_amount NUMERIC(15,2)\n)\nRETURNS BOOLEAN\nLANGUAGE plpgsql\nAS $\nBEGIN\n -- Debit source account\n UPDATE accounts SET balance = balance - p_amount\n WHERE id = p_from_account AND balance >= p_amount;\n\n IF NOT FOUND THEN\n RAISE EXCEPTION 'Insufficient funds or invalid source account';\n END IF;\n\n -- Credit destination account\n UPDATE accounts SET balance = balance + p_amount\n WHERE id = p_to_account;\n\n IF NOT FOUND THEN\n RAISE EXCEPTION 'Invalid destination account';\n END IF;\n\n RETURN TRUE;\nEXCEPTION\n WHEN OTHERS THEN\n RAISE;\nEND;\n$;","type":"text"}]},{"type":"paragraph","content":[{"text":"MySQL with Transaction:","type":"text","marks":[{"type":"strong"}]}]},{"type":"code_block","attrs":{"wrap":false,"language":"sql"},"content":[{"text":"DELIMITER //\nCREATE PROCEDURE TransferFunds(\n IN p_from_account INT,\n IN p_to_account INT,\n IN p_amount DECIMAL(15,2)\n)\nBEGIN\n DECLARE EXIT HANDLER FOR SQLEXCEPTION\n BEGIN\n ROLLBACK;\n RESIGNAL;\n END;\n\n START TRANSACTION;\n\n UPDATE accounts SET balance = balance - p_amount\n WHERE id = p_from_account AND balance >= p_amount;\n\n IF ROW_COUNT() = 0 THEN\n SIGNAL SQLSTATE '45000' # 45000 = configured value\n SET MESSAGE_TEXT = 'Insufficient funds';\n END IF;\n\n UPDATE accounts SET balance = balance + p_amount\n WHERE id = p_to_account;\n\n COMMIT;\nEND //\nDELIMITER ;","type":"text"}]},{"type":"heading","attrs":{"level":3},"content":[{"text":"Step 4: Validate Syntax","type":"text"}]},{"type":"paragraph","content":[{"text":"Use the validation script to check procedure syntax:","type":"text"}]},{"type":"code_block","attrs":{"wrap":false,"language":"bash"},"content":[{"text":"# Validate PostgreSQL procedure\npython3 ${CLAUDE_SKILL_DIR}/scripts/stored_procedure_syntax_validator.py \\\n --db-type postgresql \\\n --file procedure.sql\n\n# Validate MySQL procedure\npython3 ${CLAUDE_SKILL_DIR}/scripts/stored_procedure_syntax_validator.py \\\n --db-type mysql \\\n --file procedure.sql","type":"text"}]},{"type":"heading","attrs":{"level":3},"content":[{"text":"Step 5: Deploy to Database","type":"text"}]},{"type":"code_block","attrs":{"wrap":false,"language":"bash"},"content":[{"text":"# Deploy to PostgreSQL\npython3 ${CLAUDE_SKILL_DIR}/scripts/stored_procedure_deployer.py \\\n --db-type postgresql \\\n --host localhost \\\n --database mydb \\\n --file procedure.sql\n\n# Deploy to MySQL\npython3 ${CLAUDE_SKILL_DIR}/scripts/stored_procedure_deployer.py \\\n --db-type mysql \\\n --host localhost \\\n --database mydb \\\n --file procedure.sql","type":"text"}]},{"type":"heading","attrs":{"level":2},"content":[{"text":"Output","type":"text"}]},{"type":"bullet_list","content":[{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"SQL procedure file with proper syntax for target database","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Validation report confirming syntax correctness","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Deployment confirmation with execution results","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Rollback script for procedure removal","type":"text"}]}]}]},{"type":"heading","attrs":{"level":2},"content":[{"text":"Error Handling","type":"text"}]},{"type":"table","attrs":{"layout":null},"content":[{"type":"tr","content":[{"type":"th","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"Error","type":"text"}]}]},{"type":"th","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"Cause","type":"text"}]}]},{"type":"th","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"Solution","type":"text"}]}]}]},{"type":"tr","content":[{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"permission denied","type":"text","marks":[{"type":"code_inline"}]}]}]},{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"Missing CREATE PROCEDURE privilege","type":"text"}]}]},{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"GRANT CREATE PROCEDURE ON database TO user;","type":"text","marks":[{"type":"code_inline"}]}]}]}]},{"type":"tr","content":[{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"syntax error","type":"text","marks":[{"type":"code_inline"}]}]}]},{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"Invalid SQL for database type","type":"text"}]}]},{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"Use database-specific syntax validator","type":"text"}]}]}]},{"type":"tr","content":[{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"function already exists","type":"text","marks":[{"type":"code_inline"}]}]}]},{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"Procedure exists without OR REPLACE","type":"text"}]}]},{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"Add ","type":"text"},{"text":"OR REPLACE","type":"text","marks":[{"type":"code_inline"}]},{"text":" or ","type":"text"},{"text":"DROP","type":"text","marks":[{"type":"code_inline"}]},{"text":" first","type":"text"}]}]}]},{"type":"tr","content":[{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"undefined column","type":"text","marks":[{"type":"code_inline"}]}]}]},{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"Referenced column doesn't exist","type":"text"}]}]},{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"Verify table schema before deployment","type":"text"}]}]}]},{"type":"tr","content":[{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"transaction aborted","type":"text","marks":[{"type":"code_inline"}]}]}]},{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"Error during transaction","type":"text"}]}]},{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"Check EXCEPTION handler and ROLLBACK logic","type":"text"}]}]}]}]},{"type":"heading","attrs":{"level":2},"content":[{"text":"Examples","type":"text"}]},{"type":"paragraph","content":[{"text":"Generate CRUD procedures for a table:","type":"text","marks":[{"type":"strong"}]}]},{"type":"code_block","attrs":{"wrap":false,"language":""},"content":[{"text":"User: Generate CRUD stored procedures for the 'products' table in PostgreSQL\n\nClaude: I'll create four procedures for the products table:\n1. create_product - Insert new product\n2. get_product - Retrieve by ID\n3. update_product - Update existing product\n4. delete_product - Soft delete product","type":"text"}]},{"type":"paragraph","content":[{"text":"Create audit trigger:","type":"text","marks":[{"type":"strong"}]}]},{"type":"code_block","attrs":{"wrap":false,"language":""},"content":[{"text":"User: Create a trigger to log all changes to the orders table\n\nClaude: I'll create an audit trigger that:\n1. Creates an orders_audit table if not exists\n2. Captures INSERT, UPDATE, DELETE operations\n3. Records old/new values, user, and timestamp","type":"text"}]},{"type":"heading","attrs":{"level":2},"content":[{"text":"Resources","type":"text"}]},{"type":"bullet_list","content":[{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"${CLAUDE_SKILL_DIR}/references/postgresql_stored_procedure_best_practices.md","type":"text","marks":[{"type":"code_inline"}]}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"${CLAUDE_SKILL_DIR}/references/mysql_stored_procedure_best_practices.md","type":"text","marks":[{"type":"code_inline"}]}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"${CLAUDE_SKILL_DIR}/references/sqlserver_stored_procedure_best_practices.md","type":"text","marks":[{"type":"code_inline"}]}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"${CLAUDE_SKILL_DIR}/references/database_security_guidelines.md","type":"text","marks":[{"type":"code_inline"}]}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"${CLAUDE_SKILL_DIR}/references/stored_procedure_optimization_techniques.md","type":"text","marks":[{"type":"code_inline"}]}]}]}]},{"type":"heading","attrs":{"level":2},"content":[{"text":"Overview","type":"text"}]},{"type":"heading","attrs":{"level":2},"content":[{"text":"Use when you need to generate, validate, or deploy stored procedures for PostgreSQL, MySQL, or SQL Server.","type":"text"}]}]},"metadata":{"date":"2026-06-05","name":"generating-stored-procedures","tags":["database","deployment","postgresql","mysql"],"author":"@skillopedia","source":{"stars":2275,"repo_name":"claude-code-plugins-plus-skills","origin_url":"https://github.com/jeremylongshore/claude-code-plugins-plus-skills/blob/HEAD/plugins/database/stored-procedure-generator/skills/generating-stored-procedures/SKILL.md","repo_owner":"jeremylongshore","body_sha256":"55f83a3f4c63397b4fb48c7d08d5ac1794d91ca8268608e75364161404e92904","cluster_key":"b30c05fb46d88e82fab21eba02ec1c0dc331cdb32bfdee1c2a86dc7ed1517c63","clean_bundle":{"format":"clean-skill-bundle-v1","source":"jeremylongshore/claude-code-plugins-plus-skills/plugins/database/stored-procedure-generator/skills/generating-stored-procedures/SKILL.md","attachments":[{"id":"9c222e0b-da0f-505a-8b74-80862111c7f0","key":"uploads/10433ee7-ad12-4ae0-b34e-97553e46c6c8/9c222e0b-da0f-505a-8b74-80862111c7f0/attachment.md","path":"assets/README.md","size":383,"sha256":"9a2c625223a66f8233d4980b7fcbaa5be1af78affc12d644704045742cef7a34","contentType":"text/markdown; charset=utf-8"},{"id":"ab60d8e3-c2e8-5ec0-a2a1-4daaedcd79f8","key":"uploads/10433ee7-ad12-4ae0-b34e-97553e46c6c8/ab60d8e3-c2e8-5ec0-a2a1-4daaedcd79f8/attachment.yaml","path":"config/settings.yaml","size":1765,"sha256":"c812ffba54e93a8b726b3fc5b2373e338ab19db665db5527a6a7564694fdacd1","contentType":"application/yaml; charset=utf-8"},{"id":"5c9d5f46-2593-5924-8575-dc2560def2a4","key":"uploads/10433ee7-ad12-4ae0-b34e-97553e46c6c8/5c9d5f46-2593-5924-8575-dc2560def2a4/attachment.md","path":"references/README.md","size":624,"sha256":"ffffc846250424606a4f88908b8665b1211b6f6bafc9ff831f702af72e408a3c","contentType":"text/markdown; charset=utf-8"},{"id":"fab3b494-47c0-5b57-8172-de34fc82854a","key":"uploads/10433ee7-ad12-4ae0-b34e-97553e46c6c8/fab3b494-47c0-5b57-8172-de34fc82854a/attachment.md","path":"references/database_security_guidelines.md","size":6045,"sha256":"c84c81a24ae8143a722d7f333da6db494ae5794a10e499be906b4cca2cbca7e0","contentType":"text/markdown; charset=utf-8"},{"id":"0abf9afc-aba5-5510-9ca9-af746847becb","key":"uploads/10433ee7-ad12-4ae0-b34e-97553e46c6c8/0abf9afc-aba5-5510-9ca9-af746847becb/attachment.md","path":"references/mysql_stored_procedure_best_practices.md","size":7209,"sha256":"3ab757cf41a5cd9120051d65d7714d6fcb16040b96e6fe4f1683e4d86f11b0ca","contentType":"text/markdown; charset=utf-8"},{"id":"ede759a9-0bbf-5d0a-b469-28bfd0f6e48f","key":"uploads/10433ee7-ad12-4ae0-b34e-97553e46c6c8/ede759a9-0bbf-5d0a-b469-28bfd0f6e48f/attachment.md","path":"references/postgresql_stored_procedure_best_practices.md","size":5318,"sha256":"dbe062a1d47cfd857d1ef374ce1fe02bdb84a60ab5a6e3a367704f0d8ee75859","contentType":"text/markdown; charset=utf-8"},{"id":"ac16f745-b231-5cdc-ad64-d02bd7c49d49","key":"uploads/10433ee7-ad12-4ae0-b34e-97553e46c6c8/ac16f745-b231-5cdc-ad64-d02bd7c49d49/attachment.md","path":"references/sqlserver_stored_procedure_best_practices.md","size":7957,"sha256":"abe2c5b7ca33a81a3afd86f94b24719136120f7ba4717c85c81540291aeea20c","contentType":"text/markdown; charset=utf-8"},{"id":"81bee484-da75-5f9f-b764-15dbe0698bad","key":"uploads/10433ee7-ad12-4ae0-b34e-97553e46c6c8/81bee484-da75-5f9f-b764-15dbe0698bad/attachment.md","path":"references/stored_procedure_optimization_techniques.md","size":6808,"sha256":"0c900e00eabcd93777fae8d05034be31222095da6bcbb6f248b88381d0d00f33","contentType":"text/markdown; charset=utf-8"},{"id":"82d4fe41-06b4-51e6-a6a5-29428a14b1fc","key":"uploads/10433ee7-ad12-4ae0-b34e-97553e46c6c8/82d4fe41-06b4-51e6-a6a5-29428a14b1fc/attachment.md","path":"scripts/README.md","size":514,"sha256":"a1b3f4f77852d1d2b5c1792d0ee01fdffdfb97974e17a68f7659b4b9f0f297cf","contentType":"text/markdown; charset=utf-8"},{"id":"3349a4d0-85fb-5fc3-ab92-6271925d69b7","key":"uploads/10433ee7-ad12-4ae0-b34e-97553e46c6c8/3349a4d0-85fb-5fc3-ab92-6271925d69b7/attachment.py","path":"scripts/database_connection_test.py","size":7395,"sha256":"38ddff8a01c45dd4e4e9ed926ef5e678e045c8909009bf660d6aa51487f298c3","contentType":"text/x-python; charset=utf-8"},{"id":"1fa0f8c0-15c9-5910-8dff-cfbc0d05a2ea","key":"uploads/10433ee7-ad12-4ae0-b34e-97553e46c6c8/1fa0f8c0-15c9-5910-8dff-cfbc0d05a2ea/attachment.py","path":"scripts/stored_procedure_deployer.py","size":10766,"sha256":"7d884f23ada069703f44c2562ad049dd02a4a17c8c8f4256b784268b1f385e7d","contentType":"text/x-python; charset=utf-8"},{"id":"a4291d8b-ad5b-5617-a1f4-fb0e38db3323","key":"uploads/10433ee7-ad12-4ae0-b34e-97553e46c6c8/a4291d8b-ad5b-5617-a1f4-fb0e38db3323/attachment.py","path":"scripts/stored_procedure_syntax_validator.py","size":10572,"sha256":"94e644f3331207e6f85e9620b32072589e218dda1da346876c9ea29c6ec8c35b","contentType":"text/x-python; charset=utf-8"}],"bundle_sha256":"4cec97eb6c05e76206696711e5f38461e14d154f02feddc917d2ecf3eea68e36","attachment_count":12,"text_attachments":12,"attachment_storage":"skillopedia-attachments-v1","binary_attachments":0,"excluded_attachments":[]},"cluster_size":1,"skill_md_path":"plugins/database/stored-procedure-generator/skills/generating-stored-procedures/SKILL.md","import_metadata":{"date":"2026-06-05","author":"@skillopedia","version":"v1","category":"devops-infrastructure","category_label":"DevOps"},"exact_dupes_collapsed_into_this":0},"license":"MIT","version":"v1","category":"devops-infrastructure","import_tag":"clean-skills-v1","description":"Use when you need to generate, validate, or deploy stored procedures for PostgreSQL, MySQL, or SQL Server.\nCreates database functions, triggers, and procedures with proper error handling and transaction management.\nTrigger with phrases like \"generate stored procedure\", \"create database function\", \"write SQL procedure\",\n\"add trigger to table\", or \"create CRUD procedures\".\n","allowed-tools":"Read, Write, Edit, Grep, Glob, Bash(psql:*), Bash(mysql:*), Bash(sqlcmd:*), Bash(python3:*)","compatibility":"Designed for Claude Code, also compatible with Codex and OpenClaw"}},"renderedAt":1782980515340}

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: Step 2: Generate Stored Procedure PostgreSQL Function (PL/pgSQL)…