How do I use the "Write Advanced SQL Stored Procedures" prompt?

Copy the prompt template, fill in the variables (DATABASE_SYSTEM, PROCEDURE_NAME, BUSINESS_LOGIC, INPUT_PARAMETERS, EXPECTED_OUTPUT), and paste it into ChatGPT, Claude, or Gemini.

What AI tools work with this data prompt?

This prompt works with chatgpt, claude, gemini. Simply copy and paste the template into any of these AI assistants.

Tips for getting the best results?

Always specify the exact database system as syntax varies significantly between platforms Include realistic business scenarios in your requirements to get more practical error handling Request both positive and negative test cases to ensure comprehensive validation Ask for performance considerations if the procedure will handle large datasets Specify security requirements upfront, especially for procedures that modify sensitive data

data advanced

Write Advanced SQL Stored Procedures

Generate production-ready SQL stored procedures with error handling, optimization, and documentation. Perfect for database developers.

Works with: chatgptclaudegemini

Prompt Template

You are an expert database developer. Create a comprehensive SQL stored procedure based on the following requirements: **Database System**: [DATABASE_SYSTEM] **Procedure Name**: [PROCEDURE_NAME] **Business Logic**: [BUSINESS_LOGIC] **Input Parameters**: [INPUT_PARAMETERS] **Expected Output**: [EXPECTED_OUTPUT] Your stored procedure must include: 1. **Complete procedure definition** with proper CREATE PROCEDURE syntax 2. **Input validation** for all parameters with appropriate error messages 3. **Comprehensive error handling** using TRY-CATCH blocks or equivalent 4. **Transaction management** where appropriate (BEGIN/COMMIT/ROLLBACK) 5. **Performance optimization** including proper indexing hints if needed 6. **Detailed comments** explaining complex logic and business rules 7. **Return codes or result sets** as specified 8. **Security considerations** to prevent SQL injection 9. **Logging mechanisms** for audit trails where applicable Additionally, provide: - **Usage examples** showing how to execute the procedure - **Test scenarios** including edge cases - **Performance considerations** and potential bottlenecks - **Maintenance notes** for future modifications Ensure the code follows best practices for the specified database system and is production-ready with proper formatting and documentation.

Variables to Customize

[DATABASE_SYSTEM]

The database management system (SQL Server, PostgreSQL, MySQL, Oracle, etc.)

Example: SQL Server

[PROCEDURE_NAME]

The name for the stored procedure

Example: sp_ProcessCustomerOrder

[BUSINESS_LOGIC]

Detailed description of what the procedure should accomplish

Example: Process a customer order by validating inventory, calculating totals with tax and shipping, updating stock levels, and creating order records

[INPUT_PARAMETERS]

List of input parameters with their data types

Example: @CustomerID INT, @ProductIDs VARCHAR(MAX), @Quantities VARCHAR(MAX), @ShippingAddress NVARCHAR(500)

[EXPECTED_OUTPUT]

What the procedure should return or output

Example: Return OrderID if successful, or error code with message if failed. Also return order total and estimated delivery date.

Example Output

```sql CREATE PROCEDURE sp_ProcessCustomerOrder @CustomerID INT, @ProductIDs VARCHAR(MAX), @Quantities VARCHAR(MAX), @ShippingAddress NVARCHAR(500), @OrderID INT OUTPUT, @OrderTotal DECIMAL(10,2) OUTPUT, @EstimatedDelivery DATE OUTPUT AS BEGIN SET NOCOUNT ON; DECLARE @ErrorMessage NVARCHAR(4000); DECLARE @ErrorSeverity INT; DECLARE @ErrorState INT; BEGIN TRY BEGIN TRANSACTION; -- Input validation IF @CustomerID IS NULL OR @CustomerID <= 0 BEGIN RAISERROR('Invalid Customer ID provided', 16, 1); RETURN -1; END -- Verify customer exists and is active IF NOT EXISTS (SELECT 1 FROM Customers WHERE CustomerID = @CustomerID AND IsActive = 1) BEGIN RAISERROR('Customer not found or inactive', 16, 1); RETURN -2; END -- Create order header INSERT INTO Orders (CustomerID, OrderDate, ShippingAddress, OrderStatus) VALUES (@CustomerID, GETDATE(), @ShippingAddress, 'Processing'); SET @OrderID = SCOPE_IDENTITY(); -- Process order items (simplified logic) -- ... additional business logic here ... SET @OrderTotal = 0; -- Calculate actual total SET @EstimatedDelivery = DATEADD(DAY, 5, GETDATE()); COMMIT TRANSACTION; RETURN 0; -- Success END TRY BEGIN CATCH IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION; SELECT @ErrorMessage = ERROR_MESSAGE(), @ErrorSeverity = ERROR_SEVERITY(), @ErrorState = ERROR_STATE(); RAISERROR(@ErrorMessage, @ErrorSeverity, @ErrorState); RETURN -99; -- General error END CATCH END ``` **Usage Example:** ```sql DECLARE @OrderID INT, @Total DECIMAL(10,2), @DeliveryDate DATE; EXEC sp_ProcessCustomerOrder 1001, '15,23,7', '2,1,3', '123 Main St', @OrderID OUT, @Total OUT, @DeliveryDate OUT; ```

Pro Tips for Best Results

Always specify the exact database system as syntax varies significantly between platforms
Include realistic business scenarios in your requirements to get more practical error handling
Request both positive and negative test cases to ensure comprehensive validation
Ask for performance considerations if the procedure will handle large datasets
Specify security requirements upfront, especially for procedures that modify sensitive data