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.
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.
- Weekly Trends and Language Statistics
- Weekly Trends and Language Statistics