Generate Database Stored Procedures

This skill designs, writes, and optimizes stored procedures across SQL Server (T-SQL), PostgreSQL (PL/pgSQL), MySQL, and Oracle (PL/SQL), built on three core principles: input validation and security (always use parameterized queries to prevent SQL injection, validate parameters at the procedure's start, use appropriate data types and constraints), error handling (comprehensive handling with meaningful messages, transaction management for consistency, standardized error codes), and performance optimization (minimize network round trips, use appropriate indexing, prefer set-based operations over cursors).

It provides a full T-SQL template (sp_ProcessCustomerOrder) that validates inputs, wraps the operation in a try/catch transaction, checks customer and product existence and stock availability, creates the order and updates inventory, rolls back and logs on any failure, and returns output result codes and messages - plus an equivalent PostgreSQL PL/pgSQL function using the same validation and stock-check logic with RETURN QUERY result rows and an EXCEPTION block for unhandled errors.

Best-practice guidance covers parameter design (descriptive names with consistent prefixes, default values, OUTPUT parameters for result codes, logical grouping), further performance optimization (SET NOCOUNT ON in T-SQL, avoiding SELECT *, using EXISTS instead of COUNT(*) for existence checks, table-valued parameters for bulk operations), maintenance and documentation (header comments describing purpose and parameters, consistent naming, version-controlled change history, example calls in comments), and security (never building dynamic SQL via string concatenation, using QUOTENAME() for dynamic object names in T-SQL, role-based security, validating business rules inside the procedure). Common patterns include a table-valued-parameter bulk order processor using MERGE to insert or update in one statement, and an audit-trail pattern that logs old and new values alongside every data-modification update.

UPDATE Customers
SET 
    CustomerName = @CustomerName,
    Email = @Email,
    LastModified = GETDATE(),
    ModifiedBy = SYSTEM_USER
WHERE CustomerId = @CustomerId;

Use this skill when writing or reviewing a stored procedure that needs proper input validation, transaction-safe error handling, and performance-conscious SQL - especially for a data-modification workflow (order processing, bulk updates, audited changes) rather than a simple read query.

It is not a fit for ORM-based or application-layer data access patterns - the guidance is specific to database-native stored procedures and their parameter, transaction, and security conventions.

Inputs are the target database platform and the operation's business logic (validation rules, the tables involved, and the transaction boundary). Outputs are a complete stored procedure or function with input validation, transaction-wrapped error handling with rollback, OUTPUT result codes and messages, and, where relevant, bulk-processing or audit-trail logic.

Database developers and backend engineers writing stored procedures who need concrete, security-conscious templates for T-SQL or PL/pgSQL - parameterized input validation, transaction-safe error handling with logging, and performance patterns like set-based bulk processing - rather than assembling these conventions from scratch on each new procedure.