Adding Error Handling to a Stored Procedure

Create a Stored Procedure with Error Handling

Write a SQL query to create a stored procedure that includes error handling using TRY...CATCH.

Solution:

-- Create a stored procedure with error handling.
CREATE PROCEDURE UpdateEmployeeSalary
    @EmployeeID INT, -- Input parameter for the employee ID.
    @NewSalary DECIMAL(10, 2) -- Input parameter for the new salary.
AS
BEGIN
    BEGIN TRY
        -- Attempt to update the employee's salary.
        UPDATE Employees 
        SET Salary = @NewSalary 
        WHERE EmployeeID = @EmployeeID;

        IF @@ROWCOUNT = 0
        BEGIN
            RAISERROR('Employee not found.', 16, 1); -- Raise an error if no rows are updated.
        END
    END TRY
    BEGIN CATCH
        -- Handle errors gracefully.
        PRINT 'Error occurred: ' + ERROR_MESSAGE();
    END CATCH
END;

Explanation:

1. Purpose of the Query :

1. The goal is to create a stored procedure that updates an employee's salary while incorporating error handling to manage unexpected issues.

2. Key Components :

1. TRY...CATCH Block : Handles errors during execution.
2. RAISERROR : Raises a custom error message if the employee is not found.
3. ERROR_MESSAGE() : Retrieves the error message in the CATCH block.

3. Why Use Error Handling? :

1. Error handling ensures that stored procedures can gracefully handle runtime errors without crashing the application.

4. Real-World Application :

1. For example, in payroll systems, you might use this procedure to safely update salaries while logging errors for debugging.

Additional Notes:

• Always include error handling in production-grade stored procedures.
• Use this exercise to teach how to debug and troubleshoot SQL code effectively.

For more Practice: Solve these Related Problems:

• Write a SQL query to create a stored procedure that includes error handling to manage duplicate key violations during an insert operation.
• Write a SQL query to create a stored procedure that handles foreign key constraint errors while updating a table.
• Write a SQL query to create a stored procedure that logs detailed error information into a separate error log table when an exception occurs.
• Write a SQL query to create a stored procedure that retries a failed transaction up to three times before giving up.

Have another way to solve this solution? Contribute your code (and comments) through Disqus.

Previous SQL Exercise: Execute a Stored Procedure with Output Parameters.
Next SQL Exercise: Create a Trigger for Enforcing Business Rules.