Design a SQL Stored Procedure with Error Handling and Optimization

Write a SQL stored procedure named `[PROCEDURE_NAME]` for a [DATABASE_ENGINE] database (e.g., SQL Server, PostgreSQL, MySQL) that performs the following business operation: [BUSINESS_OPERATION_DESCRIPTION]. The procedure operates on the following tables: - [TABLE_1_NAME]: [TABLE_1_DESCRIPTION] (key columns: [TABLE_1_COLUMNS]) - [TABLE_2_NAME]: [TABLE_2_DESCRIPTION] (key columns: [TABLE_2_COLUMNS]) - [TABLE_3_NAME] (optional): [TABLE_3_DESCRIPTION] (key columns: [TABLE_3_COLUMNS]) Detailed Requirements: 1. **Input Parameters**: - [PARAM_1_NAME] [PARAM_1_DATATYPE] — [PARAM_1_DESCRIPTION] - [PARAM_2_NAME] [PARAM_2_DATATYPE] — [PARAM_2_DESCRIPTION] - [PARAM_3_NAME] [PARAM_3_DATATYPE] (optional, default [DEFAULT_VALUE]) — [PARAM_3_DESCRIPTION] Include input validation for all parameters at the beginning of the procedure. Return meaningful error messages for invalid inputs. 2. **Output**: The procedure should return [OUTPUT_DESCRIPTION] (e.g., a result set, output parameters, or a status code with a message). Define output parameters: [OUTPUT_PARAM_NAME] [OUTPUT_PARAM_TYPE]. 3. **Business Logic**: Implement the following steps in order: - Step 1: [STEP_1_DESCRIPTION] - Step 2: [STEP_2_DESCRIPTION] - Step 3: [STEP_3_DESCRIPTION] Include conditional logic where [CONDITION_DESCRIPTION]. 4. **Transaction Management**: Wrap all data-modifying operations in an explicit transaction. Implement proper COMMIT on success and ROLLBACK on any failure. Handle nested transaction scenarios if applicable. 5. **Error Handling**: Use TRY...CATCH (SQL Server), BEGIN...EXCEPTION (PostgreSQL), or DECLARE HANDLER (MySQL) blocks. Log errors to an `[ERROR_LOG_TABLE]` table with columns: error_id, procedure_name, error_message, error_severity, error_timestamp, input_parameters_json. Re-raise critical errors after logging. 6. **Performance Optimization**: - Add index recommendations as comments for queries that filter or join on specific columns - Use SET NOCOUNT ON (SQL Server) or equivalent to reduce network overhead - Avoid cursors; use set-based operations wherever possible - Include query execution hints or temp table strategies if the data volume exceeds [EXPECTED_ROW_COUNT] rows 7. **Security**: Apply the principle of least privilege. Include GRANT/REVOKE statements for [ROLE_NAME]. Prevent SQL injection by using parameterized logic only. 8. **Documentation**: Add a header comment block with: procedure name, author placeholder, creation date placeholder, description, parameter descriptions, change log template, and a sample EXEC/CALL statement. Provide the complete CREATE PROCEDURE script, followed by the error log table DDL, recommended indexes, permission grants, and 3 sample execution calls demonstrating normal use, edge case, and error scenario.