Error handling with “THROW” command in SQL Server 2012

Error handling with “THROW” command in SQL Server 2012

error

Error handling is one of the essential skill developer should have. It was very difficult to handle error efficiently till SQL Server 2000. After SQL Server 2000, we have get TRY…CATCH in SQL Server along with RAISERROR in SQL Server 2005. RAISERROR is even improved in form of “THROW” in SQL Server 2012.

Let us see how “THROW” command works for error handling efficiently in SQL Server 2012 because without know the error/bug in application, you can’t solve it. It is not possible/feasible, sometime, in big production environment to replicate same issue in development database, at the sametime, we can’t execute some command/query on live environment if it is affecting client’s data so efficient error handling is required so that you can log proper error along with its message, error number and other important things.

Let us create sample temporary table in AdventureWorks2012 database, if you don’t have Adventureworks2012 database with you, you can use your own database as I will be having temp table for this demonstration.

IF OBJECT_ID('tempdb..#TestingTHROWCommand') IS NOT NULL
DROP TABLE #TestingTHROWCommand

CREATE TABLE #TestingTHROWCommand
(
ID INT IDENTITY(1,1)
,Name VARCHAR(50)
,OvertimeAmount INT
)

INSERT INTO #TestingTHROWCommand
SELECT 'Ritesh Shah',15 UNION ALL
SELECT 'Teerth Shah',0 UNION ALL
SELECT 'Rajan Jain',9
GO

Now let us try to execute one UPDATE statement where we will have one division operator which divide on “OvertimeAmount” field. We have value “0” with row number 2 so obviously we will be facing an error.

BEGIN TRY
BEGIN TRANSACTION
UPDATE #TestingTHROWCommand
SET OvertimeAmount=95/OvertimeAmount
COMMIT TRANSACTION
END TRY
BEGIN CATCH
SELECT ERROR_NUMBER(),ERROR_MESSAGE(),ERROR_LINE()
ROLLBACK TRANSACTION
END CATCH
GO

As soon as we will execute above code, we will be greeted with an error because on row # 2, we have 0 which will try to divide 95 and 0 can’t be used to divide anything. Here is the error message we will get:

(0 row(s) affected)
8134   Divide by zero error encountered. 3

Look at the error number, message and error line number given above. Now, we will try to handle error with different way as follow:

BEGIN TRY
BEGIN TRANSACTION
UPDATE #TestingTHROWCommand
SET OvertimeAmount=95/OvertimeAmount
COMMIT TRANSACTION
END TRY
BEGIN CATCH
DECLARE @ErrorMsg NVARCHAR(MAX) = ERROR_MESSAGE()
ROLLBACK TRANSACTION
RAISERROR(@ErrorMsg,16,1)
END CATCH
GO

We have same UPDATE statement so obviously we will be getting error message again but this time, it will come by “RAISERROR” command. Here is the output of RAISERROR.

(0 row(s) affected)
Msg 50000, Level 16, State 1, Line 10
Divide by zero error encountered.

Look at the error number and error line. It seems wrong because of “RAISERROR”. Let us now try to handle the error with “THROW” command.

BEGIN TRY
BEGIN TRANSACTION
UPDATE #TestingTHROWCommand
SET OvertimeAmount=95/OvertimeAmount
COMMIT TRANSACTION
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION;
THROW
END CATCH
GO

we will again receive proper error message, number and line with “THROW” command. Have a look:

(0 row(s) affected)
Msg 8134, Level 16, State 1, Line 3
Divide by zero error encountered.

I have recently seen many of the developer who are using SQL Server 2012, still uses “RAISERROR” as against “THROW”. I would highly recommend start using “THROW” command.

If you like this article, do like “Extreme-Advice” page in Facebook.

Reference: Ritesh Shah

http://Extreme-Advice.com

http://www.sqlhub.com

Note: Microsoft Books online is a default reference of all articles.

4 thoughts on “Error handling with “THROW” command in SQL Server 2012

  1. Pingback: List of Errors and severity level in SQL Server with catalog view sysmessages

  2. Pingback: Create custom error message by sys.sp_addmessage in SQL Server 2012

  3. Pingback: SET option XACT_ABORT and SQL Server Transaction

Comments are closed.