Transaction with TRY...CATCH
Learn how TRY...CATCH can be used with SQL Server Transactions to handle errors and safely commit or roll back database changes.
What is TRY...CATCH?
TRY...CATCH is used in SQL Server to handle errors that occur while executing SQL statements.
The statements that may generate an error are placed inside the TRY block. If an error occurs, SQL Server transfers control to the CATCH block.
Basic TRY...CATCH Syntax
BEGIN TRY
-- SQL statements
END TRY
BEGIN CATCH
-- Error handling
END CATCH;
Transaction with TRY...CATCH
A transaction can be combined with TRY...CATCH so that successful operations are committed and failed operations are rolled back.
BEGIN TRY
BEGIN TRANSACTION;
-- SQL statements
COMMIT TRANSACTION;
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION;
END CATCH;
Simple Example
BEGIN TRY
BEGIN TRANSACTION;
INSERT INTO Students
(
StudentId,
StudentName,
City,
Age
)
VALUES
(
501,
'Rahul',
'Pune',
22
);
COMMIT TRANSACTION;
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION;
END CATCH;
If the INSERT operation succeeds, the transaction is committed. If an error occurs, the CATCH block executes and the transaction is rolled back.
Handling the Error Message
SQL Server provides functions that can be used inside the CATCH block to retrieve information about an error.
BEGIN TRY
BEGIN TRANSACTION;
-- SQL statements
COMMIT TRANSACTION;
END TRY
BEGIN CATCH
IF @TRANCOUNT > 0
ROLLBACK TRANSACTION;
SELECT
ERROR_NUMBER() AS ErrorNumber,
ERROR_MESSAGE() AS ErrorMessage,
ERROR_LINE() AS ErrorLine;
END CATCH;
Important Error Functions
| Function | Purpose |
|---|---|
ERROR_NUMBER()
|
Returns the error number. |
ERROR_MESSAGE()
|
Returns the error message. |
ERROR_LINE()
|
Returns the line number where the error occurred. |
ERROR_PROCEDURE()
|
Returns the name of the stored procedure where the error occurred, when applicable. |
ERROR_SEVERITY()
|
Returns the severity level of the error. |
ERROR_STATE()
|
Returns the state number of the error. |
Why Check @TRANCOUNT?
@TRANCOUNT returns the number of active transactions for the current session.
Before executing ROLLBACK TRANSACTION in the CATCH block, it is useful to check whether a transaction is currently active.
IF @TRANCOUNT > 0
ROLLBACK TRANSACTION;
Complete Transaction Example
BEGIN TRY
BEGIN TRANSACTION;
INSERT INTO Students
(
StudentId,
StudentName,
City,
Age
)
VALUES
(
502,
'Priya',
'Mumbai',
21
);
UPDATE Students
SET Age = Age + 1
WHERE StudentId = 502;
COMMIT TRANSACTION;
PRINT 'Transaction completed successfully.';
END TRY
BEGIN CATCH
IF @TRANCOUNT > 0
ROLLBACK TRANSACTION;
SELECT
ERROR_NUMBER() AS ErrorNumber,
ERROR_MESSAGE() AS ErrorMessage,
ERROR_LINE() AS ErrorLine;
END CATCH;
Transaction with TRY...CATCH Flow
↓
Step 2: Begin Transaction.
↓
Step 3: Execute SQL statements.
↓
Step 4: Check result.
↓
If successful: COMMIT Transaction.
If error occurs: Execute CATCH block.
↓
ROLLBACK Transaction.
COMMIT and ROLLBACK with TRY...CATCH
| Situation | Action |
|---|---|
| All operations succeed. | COMMIT TRANSACTION |
| An error occurs. | ROLLBACK TRANSACTION |
| Need error details. | Use ERROR_* functions. |
Important Points
- TRY contains the statements that may generate an error.
- CATCH handles errors generated inside the TRY block.
- BEGIN TRANSACTION starts the transaction.
- COMMIT TRANSACTION saves successful changes.
- ROLLBACK TRANSACTION undoes uncommitted changes.
- @TRANCOUNT can be checked before rolling back.
- ERROR_MESSAGE() and other ERROR_* functions can provide information about the error.
Summary
Using TRY...CATCH with transactions provides a structured way to handle database errors.
When all operations are successful, the transaction is committed. If an error occurs, the CATCH block can roll back the transaction and return useful error information.