SQL Server Error Handling Examples

Implement TRY...CATCH with Transaction Rollback

Write a stored procedure that inserts data into two tables within a transaction. Use TRY...CATCH to rollback the transaction if any error occurs.

Expected output: The procedure commits if no errors occur; otherwise, it rolls back and returns an error message.

Hint: Use BEGIN TRANSACTION, COMMIT, and ROLLBACK inside TRY and CATCH blocks.

Raise Custom Errors Using THROW

Create a script that checks if a user input value is negative. If so, use THROW to raise an error with a custom message.

Expected output: An error is raised when the input is negative; otherwise, the script continues.

Hint: Use an IF statement and THROW with parameters for the error message.

TRY...CATCH is used to handle runtime errors gracefully by catching exceptions and allowing custom error handling logic.

THROW is simpler and recommended in SQL Server 2012+, used to raise or re-raise errors, while RAISERROR is older and more complex.

Yes, you can rollback transactions in the CATCH block to maintain data integrity after errors.