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:
- The goal is to create a stored procedure that updates an employee's salary while incorporating error handling to manage unexpected issues.
- TRY...CATCH Block : Handles errors during execution.
- RAISERROR : Raises a custom error message if the employee is not found.
- ERROR_MESSAGE() : Retrieves the error message in the CATCH block.
- Error handling ensures that stored procedures can gracefully handle runtime errors without crashing the application.
- For example, in payroll systems, you might use this procedure to safely update salaries while logging errors for debugging.
1. Purpose of the Query :
2. Key Components :
3. Why Use Error Handling? :
4. Real-World Application :
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.