Capture multiple errors in TRY CATCH by using THROW statement

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

February 4, 2015 Leave a comment Go to comments

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.

Categories: Differences, SQL Server 2012 Tags: Denali, Exception Handling, RAISERROR, SQL Server 2012, THROW, TRY-CATCH

Comments (3) Trackbacks (1) Leave a comment Trackback

spunelli

March 14, 2015 at 12:25 am

Reply

But… how do you insert Throw into an error logging table?
- manub22
  
  March 14, 2015 at 10:04 am
  
  Reply
  
  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.