Advertisements
Home > Differences, SQL Server 2012 > Capture multiple errors in TRY CATCH by using THROW statement

Capture multiple errors in TRY CATCH by using THROW statement


This post relates to my earlier post [link] where I mentioned on benefit of using THROW clause with same SQL examples.

The THROW clause was introduced in SQL Server 2012 and may be replacing the RAISERROR function in near future.

Normally the SQL statements returns single error, but some SQL statements returns more than one error message when they go wrong due to some reason or exception.
 

–> On executing the below BACKUP statement in SSMS we can see we get two errors:

BACKUP DATABASE [AdventureWorks2012] 
TO DISK='E:\FOLDER_NOT_EXISTS\test.bak'

The above code throws 2 errors with Error-Message IDs 3201 & 3013, as shown below:

error messages:
Msg 3201, Level 16, State 1, Line 2
Cannot open backup device ‘E:\FOLDER_NOT_EXISTS\test.bak’. Operating system error 3(The system cannot find the path specified.).
Msg 3013, Level 16, State 1, Line 2
BACKUP DATABASE is terminating abnormally.

 

-> But when we want to track these errors by using RAISERROR function it just returns the last (single) error message and its details, and the previous error message details are not returned by this function.

BEGIN TRY
	BACKUP DATABASE [AdventureWorks2012] 
	TO DISK='E:\FOLDER_NOT_EXISTS\test.bak'
END TRY
BEGIN CATCH
	DECLARE @msg VARCHAR(1000) = ERROR_MESSAGE()
	RAISERROR(@msg,16,0)
END CATCH

Here, only 1 error message will be returned:

error messages:
Msg 50000, Level 16, State 0, Line 7
BACKUP DATABASE is terminating abnormally.

 

–> With the new THROW clause you won’t see any issue of omitting the previous errors, as it returns all error details thrown by the SQL Statement itself.

BEGIN TRY
	BACKUP DATABASE [AdventureWorks2012] 
	TO DISK='E:\FOLDER_NOT_EXISTS\test.bak'
END TRY
BEGIN CATCH
	THROW;
END CATCH

The above statement throws both the error details as we saw in the first example:
error messages:
Msg 3201, Level 16, State 1, Line 2
Cannot open backup device ‘E:\FOLDER_NOT_EXISTS\test.bak’. Operating system error 3(The system cannot find the path specified.).
Msg 3013, Level 16, State 1, Line 2
BACKUP DATABASE is terminating abnormally.

 

Thus, if you are on SQL Server 2012 and above you must consider using THROW clause instead of the RAISERROR function.

Advertisements
  1. spunelli
    March 14, 2015 at 12:25 am

    But… how do you insert Throw into an error logging table?

    • March 14, 2015 at 10:04 am

      in CATCH block just before THROW statement you can insert error details into an error logging table, like:

      BEGIN CATCH

      DECLARE
      @ERR_MSG AS NVARCHAR(4000)
      ,@ERR_SEV AS SMALLINT
      ,@ERR_STA AS SMALLINT

      SELECT @ERR_MSG = ERROR_MESSAGE(),
      @ERR_SEV =ERROR_SEVERITY(),
      @ERR_STA = ERROR_STATE()

      INSERT INTO ErrorLog (error_msg, error_sev, error_state, insertedOn)
      VALUES (@ERR_MSG, @ERR_SEV, @ERR_STA, GETDATE())

      ;THROW

      END CATCH

      • LeonardLuen
        September 4, 2015 at 8:24 pm

        that will only log:

        Msg 3013, Level 16, State 1, Line 2
        BACKUP DATABASE is terminating abnormally.

  1. October 23, 2015 at 7:01 pm

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: