Starting in SQL Server 2005, the exception handling has been very similar to the exception handling in c++ and c#. SQL Server 2005 introduced the Try and Catch blocks. You can place all your T-SQL statements in the TRY block. If an error occurred in any T-SQL statements within the TRY block, then the control will jump to the nearest CATCH block. In the CATCH block you can do any extra processing (e.g., rollback any remaining transactions) and if you want to re-throw the error, then you can use RAISERROR command. But if there was no error in the TRY block, the CATCH block will be skipped.
Syntax
BEGIN TRY
{ sql_statement | statement_block }
END TRY
BEGIN CATCH
{ sql_statement | statement_block }
END CATCH
[ ; ]
Arguments
sql_statement is any Transact-SQL statement.
statement_block set of T-Sql batch statements
Example
BEGIN TRY
BEGIN TRANSACTION
INSERT INTO customers(CName,Phone,Address)
VALUES ('THAJ', 8965569823, 'West Street')
COMMIT TRANSACTION
print 'Insert succeeded.'
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0
ROLLBACK TRANSACTION;
Print 'Insert failed'
END CATCH
In the TRY block, we included an insert statement. If the insert statement executes correctly, then the message “Insert succeeded.” will be displayed. Otherwise, the “Insert failed” message will be displayed from the Catch block.
Exception Handling Functions
In the CATCH block, we can use the following functions to get the information about the current error that caused the CATCH block to run:
1. ERROR_NUMBER()
It returns the error number of the current error.
2. ERROR_MESSAGE()
It returns the message text of the current error.
3. ERROR_SEVERITY()
It returns the severity of the current error.
4. ERROR_STATE()
It returns the state number of the current error.
5. ERROR_LINE()
It returns the line number at which the current error occurred.
6.ERROR_PROCEDURE()
It returns the name of the stored procedure or trigger where the current error occurred.
Example
The following example will generate the dive-by-zero error:
BEGIN TRY
-- Generate a divide-by-zero error.
SELECT 1/0;
END TRY
BEGIN CATCH
SELECT ERROR_NUMBER() AS ErrorNumber;
END CATCH;
Result
ErrorNumber
8134
In the above example, we displayed only the error number. By use the above functions we can display other information about the error as illustrated below:
BEGIN TRY
-- Generate a divide-by-zero error.
SELECT 1/0;
END TRY
BEGIN CATCH
SELECT
ERROR_NUMBER() AS ErrorNumber,
ERROR_SEVERITY() AS ErrorSeverity,
ERROR_STATE() AS ErrorState,
ERROR_PROCEDURE() AS ErrorProcedure,
ERROR_LINE() AS ErrorLine,
ERROR_MESSAGE() AS ErrorMessage;
END CATCH;
Result
