다음을 통해 공유

Another reason to use THROW clause instead of RAISERROR

  • 아티클

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, and the message does not show the actual line where error occurred:

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, and this message show the exact line number where the error occurred:

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 and above.

But, it may depend on different conditions and scenarios where you would need either of both the features.

 

Source: from my personal blog SQLwithManoj: https://sqlwithmanoj.wordpress.com/2013/01/24/another-reason-to-use-throw-clause-instead-of-raiserror-sql-server-2012/

Comments

  • Anonymous
    March 08, 2016
    Can the throw clause output be stored into a variable.. I need the complete error to be stored into variable or temp table