SQL with Manoj

In my previous post [link] I talked about the new THROW clause introduced in SQL Server 2012 and how it is different from the existing RAISERROR function.

I just stumbled on one more reason to stop using RAISERROR function and start using the new THROW clause.

There are some SQL statements that throws more than one error message when they go wrong due to some reason.

-> When using RAISERROR function it just returns the last (single) error message and its details, but the previous error message details are not returned by this function.

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

Let’s check this with a small example where we want to take backup of a database, but the folder provided does not exist, and hence it errors out:

Example #1:
On executing the below BACKUP statement we can see we get 2 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.

Example #2:
And when you try to use error-handling by using TRY-CATCH and RAISERROR(), it fetches us only 1 error:

BEGIN TRY
	BACKUP DATABASE [AdventureWorks2012] 
	TO DISK='E:\FOLDER_NOT_EXISTS\test.bak'
END TRY
BEGIN CATCH
	DECLARE @msg VARCHAR(1000)
	SET @msg = 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.

Example #3:
But this is not the case with the new THROW clause, as I mentioned previously.
It throws all the errors that are originally thrown by the original SQL statement, like below:

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, you must consider using THROW clause instead of the RAISERROR function if you are in SQL Server 2012.
But, it still depends upon different conditions and scenarios where you would need either of both the features.