Search This Blog

Monday, November 25, 2013

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



5% OFF All Lenovo ThinkPad's.

1 comment:

  1. LOSYCO ist ein kompetentes, Handling spezialisiertes Unternehmen im Bereich der Produktions-Logistik. Konzipiert und realisiert werden Lean-Produktions-Systeme aus einer Hand.

    Visit Now - http://losyco.com/landingpages/handling.html

    ReplyDelete