w3resource

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.

Go to:


PREV : Execute a Stored Procedure with Output Parameters.
NEXT : Create a Trigger for Enforcing Business Rules.



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

What is the difficulty level of this exercise?

Test your Programming skills with w3resource's quiz.



Follow us on Facebook and Twitter for latest update.