Hey guys now the days of tedious error handing are gone atleast with new SQL Server 2005. My colleague Sugandh sent this nice link to me which show how we can implement Try….. Catch in our Transact SQL code, or better to say in our database queries and stored procedures.

TRY…CATCH (Transact-SQL) 

“Implements error handling for Transact-SQL that is similar to the exception handling in the Microsoft Visual C# and Microsoft Visual C++ languages. A group of Transact-SQL statements can be enclosed in a TRY block. If an error occurs in the TRY block, control is passed to another group of statements that is enclosed in a CATCH block.”

 

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

Any group of Transact-SQL statements in a batch or enclosed in a BEGIN…END block.

Examples

A. Using TRY…CATCH

The following example shows a SELECT statement that will generate a divide-by-zero error. The error causes execution to jump to the associated CATCH block.

USE AdventureWorks;
GO

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;
GO

Links:


TRY…CATCH (Transact-SQL) 

Using TRY…CATCH in Transact-SQL 


No more @@ERROR required………

Happy Coding

Advertisements