Exception Handling | SQL with Manoj

Standardize TRY CATCH block across all Stored Procedures – MSDN TSQL forum

March 31, 2015 Leave a comment

–> Question:

I am trying to develop best try catch block to be standardized in all of the SPs. Below is what I have right now developed which is giving me proper formatted output, would like to add more details or remove unwanted items from it.

Thanks for all your help!

I am using 2008 R2 Version, would be migrating to 2012 soon.

BEGIN CATCH
  IF @@TRANCOUNT > 0
  BEGIN
     ROLLBACK TRANSACTION
  END

DECLARE @ErrorMsg varchar(2100);

SET  @ErrorMsg =  ' DBName        = ' + DB_NAME() + CHAR(10)
+ ' ErrorMessage  = ' + LTRIM(CONVERT(VARCHAR(2047), LEFT(Error_Message(),2044))) + CHAR(10) 
+ ' Procedure     = ' + OBJECT_NAME(@@PROCID) + CHAR(10) + ' ErrorNumber   = ' + 
LTRIM(CONVERT(VARCHAR(9),Error_Number()))  + CHAR(10) 
+ ' ErrorState    = ' + LTRIM(CONVERT(VARCHAR(3),Error_State())) + CHAR(10)
+ ' ErrorSeverity = ' + LTRIM(CONVERT(VARCHAR(3),Error_Severity()))  + CHAR(10)
+ ' LineNumber    = ' + LTRIM(CONVERT(VARCHAR(9),Error_Line()))  + CHAR(10)
+ ' ErrorDT       = ' + CONVERT(VARCHAR(23),GETDATE(),121) + CHAR(10) + ' ErrorBy = ' + SUSER_SNAME();

RAISERROR(@ErrorMsg,16,1);
    RETURN -1
END CATCH

–> My Answer:

The way you’ve drafted your TRY-CATCH looks good, however you can also consider few things:

1. Use THROW instead of RAISERROR as recommended by Microsoft, this will also reduce unnecessary (at times) declaration and assignment of error related variables, link.

2. With THROW you can also catch multiple errors, RAISERROR will only throw last error raised, link.

3. Make use of XACT_ABORT only when you want to rollback the entire transaction, link.

Check all these and other stuff about TRY-CATCH in my blog posts.

Ref Link.

Categories: SQL Server Questions Tags: Exception Handling, MSDN TSQL forum, TRY-CATCH

Capture multiple errors in TRY CATCH by using THROW statement

February 4, 2015 4 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

Most effective way to write SQL Statement to Catch and Reverse errors during Query execution – MSDN TSQL forum

September 11, 2014 Leave a comment

–> Question:

I am wondering what is the most effective way to deal with errors, specifically in a Stored Procedure.

I wrote something like this:

BEGIN TRY
BEGIN TRANSACTION

/*My statements goes in here*/

IF ERROR_NUMBER() = 0 -- Do I need this line?

COMMIT TRANSACTION;

END TRY

BEGIN CATCH

IF ERROR_NUMBER() > 0 --Do I need this line?

ROLLBACK TRANSACTION;

END CATCH;

It would make sense using the if Statement when attempting to log errors.

–> My Answer:

Checking ERROR_NUMBER() within TRY BEGIN-COMMIT TRANS block is not a good idea, because as soon as an error is reported the control will be immediately redirected to the CATCH block.

So, use it like this:

BEGIN TRY
BEGIN TRANSACTION

/*My statements goes in here*/

COMMIT TRANSACTION;

END TRY

BEGIN CATCH

;THROW

ROLLBACK TRANSACTION;

END CATCH;

Check my other blog posts on error/exception handling.

Ref Link.

Categories: SQL Server Questions Tags: Exception Handling, MSDN TSQL forum, TRY-CATCH

Another reason to use THROW clause instead of RAISERROR | SQL Server 2012

January 24, 2013 5 comments

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:

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

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.

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

New THROW statement in SQL Server 2012 (vs RAISERROR)

December 20, 2012 7 comments

Newly introduced THROW keyword in SQL server 2012 is an improvement over the existing RAISERROR() statement. Yes, it’s single ‘E’ in RAISERROR.

Both RAISERROR & THROW can be used in T-SQL code/script to raise and throw error within a TRY-CATCH block. Check my previous post for TRY-CATCH block, [link].

–> With RAISERROR developers had to use different ERROR_xxxx() system functions to get the error details to pass through the RAISERROR() statement, like:
– ERROR_NUMBER()
– ERROR_MESSAGE()
– ERROR_SEVERITY()
– ERROR_STATE()

let’s see an example:

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

BEGIN TRY
	SELECT 1/0 as DivideByZero
END TRY
BEGIN CATCH
	SELECT @ERR_MSG = ERROR_MESSAGE(),
		@ERR_SEV =ERROR_SEVERITY(),
		@ERR_STA = ERROR_STATE()
	SET @ERR_MSG= 'Error occurred while retrieving the data from database: ' + @ERR_MSG

	RAISERROR (@ERR_MSG, @ERR_SEV, @ERR_STA)  WITH NOWAIT
END CATCH
GO

Output:
(0 row(s) affected)
Msg 50000, Level 16, State 1, Line 15
Error occurred while retrieving the data from database: Divide by zero error encountered.

The RAISERROR() can take first argument as message_id also instead of the message. But if you want to pass the message_id then it has to be in sys.messages

–> With THROW the benefit is: it is not mandatory to pass any parameter to raise an exception.
Just using the THROW; statement will get the error details and raise it, as shown below:

-- Using THROW - 1
BEGIN TRY
	SELECT 1/0 as DivideByZero
END TRY
BEGIN CATCH
	THROW;
END CATCH
GO

Output:
(0 row(s) affected)
Msg 8134, Level 16, State 1, Line 2
Divide by zero error encountered.
As you see in the Output above, the error message thrown is the default one. But you can also add your customized message, we will see below.

IMP NOTE: THROW will show the exact line where the exception was occurred, here the line number is 2. But RAISERROR will show the line number where the RAISERROR statement was executed i.e. Line 15, but not the actual exception position.

Also passing the message_id won’t require it to be stored in sys.messages, let’s check this:

-- Using THROW - 2
DECLARE  
	@ERR_MSG AS NVARCHAR(4000)      
	,@ERR_STA AS SMALLINT      

BEGIN TRY
	SELECT 1/0 as DivideByZero
END TRY
BEGIN CATCH
	SELECT @ERR_MSG = ERROR_MESSAGE(),
		@ERR_STA = ERROR_STATE()

	SET @ERR_MSG= 'Error occurred while retrieving the data from database: ' + @ERR_MSG;

	THROW 50001, @ERR_MSG, @ERR_STA;
END CATCH
GO

Output:
(0 row(s) affected)
Msg 50001, Level 16, State 1, Line 14
Error occurred while retrieving the data from database: Divide by zero error encountered.

But if you parameterize the THROW statement as above it will not show the actual position of exception occurrence, and the behavior will be same as RAISERROR(). As with RAISERROR() you’ve to provide mandatory params, so there is no way to get the actual position of Line where the error occurred.

As per MSBOL following are the difference between RAISERROR & THROW:

RAISERROR statement	THROW statement
If a msg_id is passed to RAISERROR, the ID must be defined in sys.messages.	The error_number parameter does not have to be defined in sys.messages.
The msg_str parameter can contain printf formatting styles.	The message parameter does not accept printf style formatting.
The severity parameter specifies the severity of the exception.	There is no severity parameter. The exception severity is always set to 16.