DATETIME SELECT SELECT INTO DATE PAD STRING DYNAMIC SQL CURSOR MONEY FORMAT PERCENT STORED PROCEDURE SQL SERVER AGENT JOB OPTIMIZATION WHILE LOOP OVER PARTITION BY UPDATE
SITE SEARCH SQLUSA.com HEADLINES NEWS
SQL E/BOOKS   SQL 2014 PROGRAMMING   DOWNLOADS
SCRIPTS SQL 2005 SQL 2008 ARTICLES
SQL JOBS TWITTER FORMAT VIDEOS

 

Catching All Errors Not Caught by TRY...CATCH

By Kalman Toth, M.Phil. Physics, M.Phil. Comp. Science, MCDBA, MCITP

June 4 , 2008

The TRY…CATCH error control method has been introduced in SQL Server 2005 following similar exception handling construct in C++ and C# languages.  A TRY…CATCH construct catches all execution errors with severity greater than 10 that will not result in the termination of the database connection. As such, it is very useful in verification of the database bound information. Error handling tasks can be handled in stored procedures which required client logic formerly.

But not every error is caught, even though SQL database developers may prefer that feature very much.  Wrong object name errors are not caught, the control flow breaks altogether. Even Query Editor parser is not catching these errors. Syntax errors are also outside the domain of errors for TRY…CATCH.  Syntax errors, on the other hand, are caught by Management Studio Query Editor parser.

There is an interesting technique for catching all errors: nest the TRY-CATCH stored procedure inside another TRY-CATCH stored procedure. The outer sproc will catch the uncaught errors by the inner sproc.

The following examples with results demonstrate what happens for different exception scenarios.

RAISERROR with severity 11-19 will jump to CATCH: Using RAISERROR

use tempdb;

 

-- Identity insert attempt flows to CATCH

begin try

    insert into AdventureWorks2008.Production.Product (ProductID)

      select 800

      print 'Try passed OK'

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

 

/* CATCH

ErrorNumber ErrorSeverity     ErrorState  ErrorProcedure    ErrorLine   ErrorMessage

544   16    1     NULL  2     Cannot insert explicit value for identity column in table 'Product' when IDENTITY_INSERT is set to OFF.

*/

 

-- The following examples don't flow to CATCH

-- In some cases the ERROR is a compile error, other cases execution error

begin try

      select HighestTicket=dateadd(UnitPrice)

      from AdventureWorks2008.Sales.SalesOrderDetail

      print 'Try passed OK'

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

/* NO CATCH ERROR

Msg 174, Level 15, State 1, Line 2

The dateadd function requires 3 argument(s).

*/

 

begin try

      select HighestTicket=maxi(UnitPrice)

      from AdventureWorks2008.Sales.SalesOrderDetail

      print 'Try passed OK'

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

/* NO CATCH ERROR

Msg 195, Level 15, State 10, Line 2

'maxi' is not a recognized built-in function name.

*/

 

begin try

      select HighestTicket=max(UnitPrice)

      from AdventureWorks2008.Sales.SalesOrderDetailOMEGA

      print 'Try passed OK'

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

/* NO CATCH ERROR Message

Msg 208, Level 16, State 1, Line 2

Invalid object name 'AdventureWorks2008.Sales.SalesOrderDetailOMEGA'.

*/

 

begin try

      insert AdventureWorks2008.Production.Product (Name)

      select values 'Rocky Mountain Bike'

      print 'UPDATE succeeded'

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

 

/*

NO CATCH ERROR Message

Msg 156, Level 15, State 1, Line 3

Incorrect syntax near the keyword 'values'.

*/

------------

 

Following is an interesting technique for catching all errors: nest the TRY-CATCH stored procedure ( sprocHighestTicketOrder) inside another TRY-CATCH stored procedure (sprocMAINHighestTicketOrder). The outer sproc will catch the uncaught errors by the inner sproc. We can test for where the error occured by looking at the ERROR_PROCEDURE() function (ErrorProcedure) return.

-- MSSQL script passes parse, however, it throws an execution error

-- T-SQL TRY - CATCH not catching

BEGIN TRY

  DECLARE @HighestTicket money

  SELECT @HighestTicket = MAX(UnitPrice)

  FROM   AdventureWorks2008.Sales.SalesOrderDetailx

  

  PRINT 'Try passed OK'

END TRY

 

BEGIN CATCH

  SELECT  WhereAmI = 'scriptHighestTicketOrder',

         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

/* Msg 208, Level 16, State 1, Line 5

Invalid object name 'AdventureWorks2008.Sales.SalesOrderDetailx'.

*/

-- T-SQL erroneous stored procedure compiles OK

-- Internal TRY-CATCH will not catch

CREATE PROC sprocHighestTicketOrder @HighestTicket money OUTPUT

AS

BEGIN

 

BEGIN TRY

  SELECT @HighestTicket = MAX(UnitPrice)

  FROM   AdventureWorks2008.Sales.SalesOrderDetailx

  

  PRINT 'Try passed OK'

END TRY

 

BEGIN CATCH

  SELECT  WhereAmI = 'sprocHighestTicketOrder',

         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

END

GO

 

-- T-SQL stored procedure without error

-- Outer sproc will catch error not caught in inner sproc

CREATE PROC sprocMAINHighestTicketOrder

AS

BEGIN

 

BEGIN TRY

  DECLARE @HighestTicket money

  EXEC sprocHighestTicketOrder @HighestTicket OUTPUT

  PRINT @HighestTicket

  

  PRINT 'Try passed OK'

END TRY

 

BEGIN CATCH

  SELECT WhereAmI = 'sprocMAINHighestTicketOrder',

   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

END

GO

 

EXEC  sprocMAINHighestTicketOrder

GO

/* Results

 

WhereAmI    ErrorNumber ErrorSeverity     ErrorState  ErrorProcedure    ErrorLine      ErrorMessage

sprocMAINHighestTicketOrder   208   16    1     sprocHighestTicketOrder 6     Invalid object name 'AdventureWorks2008.Sales.SalesOrderDetailx'.

*/

------------

Related articles:

Defensive Database Programming with SQL Server : TRY...CATCH Gotchas (part 2) - TRY...CATCH blocks cannot catch all errors

SQL Server Transactions and Error Handling

 

Exam Prep 70-461
Exam 70-461
DATETIME SELECT SELECT INTO DATE PAD STRING DYNAMIC SQL CURSOR MONEY FORMAT PERCENT STORED PROCEDURE SQL SERVER AGENT JOB OPTIMIZATION WHILE LOOP OVER PARTITION BY UPDATE