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.


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.

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.