Error Handling in SQL Server 2012

As we all know that from SQL Server 2005 and onward, we had TRY CATCH to handle the exceptions / errors in t-sql code along with @@ERROR methodology.

@@ERROR

Returns an error number if the previous statement encountered an error. If the error was one of the errors in the sys.messages catalog view, then @@ERROR contains the value from the sys.messages.message_id column for that error. You can view the text associated with an @@ERROR error number in sys.messages.

Because @@ERROR is cleared and reset on each statement executed, check it immediately following the statement being verified, or save it to a local variable that can be checked later.

Simple Example;

CREATE TABLE [dbo].[tblTest](

       [NAME] [varchar](100) NOT NULL,

 CONSTRAINT [PK_tblTest_1] PRIMARY KEY CLUSTERED

(

       [NAME] ASC

)

)

GO

INSERT INTO tblTest 

       Select 'Atif'

       Union

       Select 'Sheikh'

Update tblTest

Set Name = 'Atif'

Where Name = 'Sheikh'

if @@ERROR <> 0

       print 'Error Generated'

else

       Print 'No Error.'

TRY CATCH

We can also make use of TRY CATCH block in order to catch the error;

BEGIN TRY

       Update tblTest

       Set Name = 'Atif'

       Where Name = 'Sheikh'

END TRY

BEGIN CATCH

       print 'Error Generated.'

END CATCH

We can make use of transactions and roll back transactipon in the CATCH block. The TRY...CATCH construct also supports additional system functions (ERROR_LINE, ERROR_MESSAGE, ERROR_PROCEDURE, ERROR_SEVERITY, and ERROR_STATE) that return more error information than @@ERROR. TRY...CATCH also supports an ERROR_NUMBER function that is not limited to returning the error number in the statement immediately after the statement that generated an error.

BEGIN TRY

       Update tblTest

       Set Name = 'Atif'

       Where Name = 'Sheikh'

END TRY

BEGIN CATCH

       print 'Error Generated.'

      

       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

THROW

Now, in SQL Server 2012, we can also use THROW to throw the exception to the application. You can say it works like RAISERROR;

BEGIN TRY

       Update tblTest

       Set Name = 'Atif'

       Where Name = 'Sheikh'

END TRY

BEGIN CATCH

    print 'Error Generated.';     

       THROW;

END CATCH

You can use THROW statement like RAISERROR as well;

BEGIN TRY

       Update tblTest

       Set Name = 'Atif'

       Where Name = 'Sheikh'

END TRY

BEGIN CATCH

    print 'Error Generated.';     

       THROW 51000, 'Primary Key Violates.', 1;

END CATCH