Category Archives: Exception Handling

Exception Handling Template for Stored Procedure – In Sql Server

This is the fourth article in the series of articles on Exception Handling in Sql Server. Below is the complete list of articles in this series.

Part   I: Exception Handling Basics – MUST Read Article
Part  II: TRY…CATCH (Introduced in Sql Server 2005)
Part III: RAISERROR Vs THROW (Throw: Introduced in Sql Server 2012)
Part IV: Exception Handling Template

Exception Handling Template

Based on my analysis, below is the exception handling template one should be using for proper error handling in Sql Server. Note the THROW statement works only in Sql Server 2012. You can validate it’s correctness and then use in your development work.

CREATE PROCEDURE dbo.ErrorHandlingTemplate
AS
BEGIN
	BEGIN TRY
		SET NOCOUNT ON
		SET XACT_ABORT ON

		--  Code Which Doesn't Require Transaction

		BEGIN TRANSACTION
			-- Code which Requires Transaction 
		COMMIT TRANSACTION
	END TRY
	BEGIN CATCH
		IF @@TRANCOUNT > 0 AND XACT_STATE() <> 0 
			ROLLBACK TRAN

		-- Do the Necessary Error logging if required
		-- Take Corrective Action if Required

		THROW --RETHROW the ERROR
	END CATCH
END

TRY…CATCH In Sql Server

This is the second article in the series of articles on Exception Handling in Sql Server. Below is the list of other articles in this series.

Part   I: Exception Handling Basics – MUST Read Article
Part  II: TRY…CATCH (Introduced in Sql Server 2005)
Part III: RAISERROR Vs THROW (Throw: Introduced in Sql Server 2012)
Part IV: Exception Handling Template

TRY…CATCH

TRY…CATCH is the Structured Error handling construct introduced in Sql Server 2005. It is similar to the one which we have in C#, but it doesn’t have the FINALLY block. If any error is raised by the statement in the TRY block then the control is immediately passed to the CATCH block. If none of the statement in the TRY block raises any exception then the CATCH block will not be executed.

SYNTAX:

BEGIN TRY
 -- T-Sql Statements
END TRY
BEGIN CATCH
 -- T-Sql Statements
 /*Control is passed to CATCH block only if
   there are any exceptions in the TRY block*/
END CATCH

Let us understand TRY…CATCH construct with below extensive list of examples.

To demonstrate this let us create a New Database and table as shown below:

--Create a New database for the Demo
CREATE DATABASE SqlHintsErrorHandlingDemo
GO
USE SqlHintsErrorHandlingDemo
GO
CREATE TABLE dbo.Account
(	
 AccountId INT NOT NULL PRIMARY KEY, 
 Name	 NVARCHAR (50) NOT NULL,
 Balance Money NOT NULL CHECK (Balance>=0)	
)
GO

As the Account table has Primary Key on the AccountId column, so it will raise an error if we try to duplicate the AccountId column value. And the Balance column has a CHECK constraint Balance>=0, so it will raise an exception if the value of Balance is <0.

DEMO 1:

Let us execute the below script and observe what will be the behavior of TRY..CATCH construct if none of the Statements in the TRY block raises any exception.

PRINT 'BEFORE TRY'
BEGIN TRY
 PRINT 'First Statement in the TRY block'	
 INSERT INTO dbo.Account(AccountId, Name , Balance) 
 VALUES(1, 'Account1', 10000)
 PRINT 'Last Statement in the TRY block'
END TRY
BEGIN CATCH
	PRINT 'In CATCH Block'
END CATCH
PRINT 'After END CATCH'
GO

RESULT:
TryCatch

From the above result it is clear that, if the statements enclosed within TRY block doesn’t result in any errors then the control is not passed to the CATCH block instead the execution continues with immediate statement after the END CATCH statement.

Let us empty the Account Table before proceeding with the next DEMO.

DELETE FROM dbo.Account
GO

DEMO 2:

Let us execute the below script and observe what will be the behavior of TRY..CATCH construct. Here the Second INSERT statement results in a statement Terminating Primary Key Violation error.

PRINT 'BEFORE TRY'  
BEGIN TRY
 PRINT 'First Statement in the TRY block'
 INSERT INTO dbo.Account(AccountId, Name , Balance)
 VALUES(1, 'Account1',  10000)
 INSERT INTO dbo.Account(AccountId, Name , Balance)
 VALUES(1, 'Duplicate', 10000)
 INSERT INTO dbo.Account(AccountId, Name , Balance)
 VALUES(2, 'Account2',  20000)
 PRINT 'Last Statement in the TRY block'
END TRY
BEGIN CATCH
	PRINT 'In CATCH Block'	
END CATCH
PRINT 'After END CATCH'
SELECT * FROM dbo.Account WITH(NOLOCK)
GO

RESULT:
SqlServerTRYCATCH2

From the above result it is clear that as soon as the error is encountered in the TRY block, sql server terminates the Statement which raised the error, no subsequent statements in the TRY block are executed and the control is passed to the CATCH block.

Let us empty the Account Table before proceeding with the next DEMO.

DELETE FROM dbo.Account
GO

DEMO 3:

Let us execute the below script and observe what will be the behavior of TRY..CATCH construct. Here the Second statement (i.e. UPDATE statement) results in a Batch Abortion CONVERSION/CAST error.

PRINT 'BEFORE TRY'  
BEGIN TRY
 PRINT 'First Statement in the TRY block'
 INSERT INTO dbo.Account(AccountId, Name , Balance)
 VALUES(1, 'Account1',  10000)

 UPDATE dbo.Account
 SET Balance = Balance + CAST('TEN THOUSAND' AS MONEY) 
 WHERE AccountId = 1

 INSERT INTO dbo.Account(AccountId, Name , Balance)
 VALUES(2, 'Account2',  20000)
 PRINT 'Last Statement in the TRY block'
END TRY
BEGIN CATCH
	PRINT 'In CATCH Block'	
END CATCH
PRINT 'After END CATCH'
SELECT * FROM dbo.Account WITH(NOLOCK)
GO

RESULT:
SqlServerTRYCATCH3

From the above result it is clear that as soon as the error is encountered in the TRY block, sql server terminates the Statement which raised the error, no subsequent statements in the TRY block are executed and the control is passed to the CATCH block.

Let us empty the Account Table before proceeding with the next DEMO.

DELETE FROM dbo.Account
GO

DEMO 4:

In this DEMO we will see how we can use the Transactions with TRY..CATCH constructs.

PRINT 'BEFORE TRY' 
BEGIN TRY
	BEGIN TRAN
	 PRINT 'First Statement in the TRY block'
	 INSERT INTO dbo.Account(AccountId, Name , Balance) VALUES(1, 'Account1',  10000)
	 UPDATE dbo.Account SET Balance = Balance + CAST('TEN THOUSAND' AS MONEY) WHERE AccountId = 1
	 INSERT INTO dbo.Account(AccountId, Name , Balance) VALUES(2, 'Account2',  20000)
	 PRINT 'Last Statement in the TRY block'
	COMMIT TRAN
END TRY
BEGIN CATCH
    PRINT 'In CATCH Block' 
	IF(@@TRANCOUNT > 0)
		ROLLBACK TRAN
END CATCH
PRINT 'After END CATCH'
SELECT * FROM dbo.Account WITH(NOLOCK)
GO

RESULT:
SqlServerTRYCATCH4

Here @@TRANCOUNT in the CATCH block identifies whether we have any active transactions, if yes we are rolling back the transaction. Because of this we don’t see any records in the Account Table.

DEMO 5:

In this demo we will see the set of ERROR FUNCTIONS which we can use in the CATCH block to get the details of the Error which resulted in transfering the control from the TRY block to the CATCH block

BEGIN TRY
 SELECT 5/0
END TRY
BEGIN CATCH
 PRINT '*************Error Detail****************'
 PRINT 'Error Number  :' + CAST(ERROR_NUMBER() AS VARCHAR)
 PRINT 'Error Severity:' + CAST(ERROR_SEVERITY() AS VARCHAR)
 PRINT 'Error State   :' + CAST(ERROR_STATE() AS VARCHAR)
 PRINT 'Error Line    :' + CAST(ERROR_LINE() AS VARCHAR)
 PRINT 'Error Message :' + ERROR_MESSAGE()
END CATCH

RESULT:
SqlServerTRYCATCH5

ERROR FUNCTIONS in Sql Server

Below is the list of ERROR FUNCTIONS which we can use in the CATCH block to get the details of the Error which resulted in transferring the control from the TRY block to the CATCH block.

  • ERROR_NUMBER() : Returns the Error Number.
  • ERROR_SEVERITY() : Returns the Severity of the Error.
  • ERROR_STATE() : Returns the State of the Error.
  • ERROR_PROCEDURE(): Returns the name of the SP/UDF in which the error occurred.
  • ERROR_LINE() : Returns the line number of the Sql statement which raised the error.
  • ERROR_MESSAGE() : Returns the error message.

These function can used only in the scope of the CATCH block. They will not return the value outside the CATCH block, below example demonstrates this:

SELECT 5/0
SELECT ERROR_NUMBER() ‘Error Number’, ERROR_MESSAGE() ‘Error Message’

RESULT:
ErrorFunctions

DEMO 6:

TRY…CATCH construct catches all the errors except the Connection Termination errors. This implies any error in the TRY block with severity level between 11-19 causes Sql Server to transfer the control to the CATCH block. One exception is the Scope Abortion error (i.e. Deferred Name resolution) which is not a connection termination error but can’t be Trapped by the TRY…CATCH construct. The below example demonstrates this:

PRINT 'BEFORE TRY'
BEGIN TRY
  PRINT 'First Statement in the TRY block'
  SELECT * FROM NONExistentTable
  PRINT 'Last Statement in the TRY block'
END TRY
BEGIN CATCH
  PRINT 'In the CATCH block'	
END CATCH
PRINT 'After END CATCH'
GO

RESULT:
DeferredNameResolution

Will cover few more examples of TRY…CATCH construct while discussing on the RAISERROR and THROW Statement.

Exception Handling in Sql Server

This is the first article in the series of articles on Exception Handling in Sql Server. Below is the complete list of articles in this series.

Part   I: Exception Handling Basics
Part  II: TRY…CATCH (Introduced in Sql Server 2005)
Part III: RAISERROR Vs THROW (Throw: Introduced in Sql Server 2012)
Part IV: Exception Handling Template

Exception Handling Basics

If we are not clear about the basics of Exception handling in Sql Server, then it is the most complex/confusing task, it will become nightmarish job to identify the actual error and the root cause of the unexpected results. In this blog post I will try to make sure that all the concepts are cleared properly and the one who goes through it should feel the sense of something they have learnt new and feel themselves an expert in this area. And at the end of the blog post will present the ideal exception handling template which one should be using for proper error handling in Sql Server.

Last week on 11th January, 2014, I have presented a session on this topic at Microsoft Office in the Sql Bangalore User Group meeting which is attend by hundreds of enthusiastic Sql Server working professionals. Received very good feedback and few messages posted in the Facebook SQLBangalore user group were “Thanks Basavaraj Biradar! Your session was divine!” By Community Member Adarsh Prasad, “Thanks Basavaraj for your excellent session” By Community Member Selva Raj. Enough Self Praise, enough expectation is set, let’s cut short the long story short and move onto the deep dive of Exception handling basics

In this topic will cover the following concepts with extensive list of examples

  • Error Message
  • Error Actions

Error Message

Let’s start with a simple statement like below which results in an exception as I am trying to access a non-existing table.

--------------Try To Access Non-Existing Table ---------------
 SELECT * FROM dbo.NonExistingTable
 GO

Result of the above query:
Msg 208, Level 16, State 1, Line 2
Invalid object name ‘dbo.NonExistingTable’.

By looking at the above error message, we can see that the error message consists of following 5 parts:

Msg 208 – Error Number
Level 16 – Severity of the Error
State 1 – State of the Error
Line 2 – Line Number of the statement which generated the Error
Invalid object name ‘dbo.NonExistingTable’. – Actual Error Message

Now Let’s wrap the above statement which generated the error into a stored procedure as below

---Create the Stored Procedure 
CREATE PROCEDURE dbo.ErrorMessageDemo
AS 
BEGIN
	SELECT * FROM dbo.NonExistingTable
END
GO
--Execute the Stored Procedure
EXEC dbo.ErrorMessageDemo
GO

Result of executing the above stored procedure is:
Msg 208, Level 16, State 1, Procedure ErrorMessageDemo, Line 4
Invalid object name ‘dbo.NonExistingTable’.

If we compare this error message with the previous error message, then this message contains one extra part “Procedure ErrorMessageDemo specifying the name of the stored procedure in which the exception occurred.

Parts of ErrorMessage

The below image explains in detail each of the six parts of the error message which we have identified just above:

ErrorMessageParts

In case the image is not clear below is the detail which I have tried to present in it:

ERROR NUMBER/ Message Id:

Any error number which is <= 50000 is a System Defined Messages and the ones which are > 50000 are User Defined Messages. SYS.Messages catalog view can be used to retrieve both System and User Defined Messages. We can add a user defined message using sp_addmessage and we can remove it using the system stored procedure sp_dropmessage.

ERROR SEVERITY: Error Severity can be between 0-25.

0-10:  Informational or a warning
11-16: Programming Errors
17-25: Resource / Hardware / OS/ Sql Server Internal Errors
20-25: Terminates the Connection
19-25: Only User with SysAdmin rights can raise error’s with this severity

ERROR STATE: Same Error can be raised for several different conditions in the code. Each specific condition that raises the error assigns a unique state code. Also the SQL Support Team uses it to find the location in the source code where that error is being raised.

ERROR PROCEDURE: Name of the Stored Procedure or the Function in which the Error Occurred. It Will be blank if it is a Normal Batch of Statement.

ERROR LINE: Line Number of the Statement within SP/ UDF/ Batch which triggered the error. It will be 0 If SP/UDF Invoke Causes the Error.

ERROR MESSAGE: Error description detailing out the reason for the error

Error Actions

Now let us see how Sql Server Reacts to different errors. To demonstrate this let us create a New Database and table as shown below:

--Create a New database for the Demo
CREATE DATABASE SqlHintsErrorHandlingDemo
GO
USE SqlHintsErrorHandlingDemo
GO
CREATE TABLE dbo.Account
(	
 AccountId INT NOT NULL PRIMARY KEY, 
 Name	 NVARCHAR (50) NOT NULL,
 Balance Money NOT NULL CHECK (Balance>=0)	
)
GO

As the Account table has Primary Key on the AccountId column, so it will raise an error if we try to duplicate the AccountId column value. And the Balance column has a CHECK constraint Balance>=0, so it will raise an exception if the value of Balance is <0.

Let us first check whether we are able to insert valid Account into the Account table.

INSERT INTO dbo.Account(AccountId, Name , Balance) 
VALUES(1, 'Account1', 10000)

Result: We are able to successfully insert a record in the Account table

SuccessfulInsertion

Now try to insert one more account whose AccountId is same as the one which we have just inserted above.

INSERT INTO dbo.Account(AccountId, Name , Balance) 
VALUES(1, 'Duplicate', 10000)

Result: It fails with below error message, because we are trying to insert a duplicate value for the the Primary Key column AccountId.

Msg 2627, Level 14, State 1, Line 1
Violation of PRIMARY KEY constraint ‘PK__Account__349DA5A67ED5FC72’. Cannot insert duplicate key in object ‘dbo.Account’. The duplicate key value is (1).

The statement has been terminated.

Let me empty the Account Table by using the below statement:

DELETE FROM dbo.Account

DEMO 1: Now let us see what will be the result if we execute the below batch of Statements:

INSERT INTO dbo.Account(AccountId, Name , Balance) 
VALUES(1, 'Account1', 10000)
INSERT INTO dbo.Account(AccountId, Name , Balance) 
VALUES(1, 'Duplicate', 10000)
INSERT INTO dbo.Account(AccountId, Name , Balance) 
VALUES(2, 'Account2', 20000)
GO

Result: The First and Third Insert statements in the batch are succeeded even though the Second Insert statement fails

Sql Server Error Handling Demo1

From the above example result it is clear that even though the Second insert statement is raising a primary key voilation error, Sql server continued the execution of the next statement and it has successfully inserted the Account with AccountId 2 by the third Insert statement.

If Sql Server terminates the statement which raised the error but continues to execute the next statements in the Batch. Then such a behavior by a Sql Server in response to an error is called Statement Termination.

Let me clear the Account Table by using the below statement before proceeding with the Next DEMO:

DELETE FROM dbo.Account

DEMO 2: Now let us see what will be the result if we execute the below batch of Statements. The only difference between this batch of statement and the DEMO 1 is the first line i.e. SET XACT_ABORT ON:

SET XACT_ABORT ON
INSERT INTO dbo.Account(AccountId, Name , Balance) 
VALUES(1, 'Account1',  10000)
INSERT INTO dbo.Account(AccountId, Name , Balance) 
VALUES(1, 'Duplicate', 10000)
INSERT INTO dbo.Account(AccountId, Name , Balance) 
VALUES(2, 'Account2',  20000)
GO

RESULT: Only the first Insert succeeded
Sql Server Error Handling Demo2

From the above example result it is clear that failure in the Second insert statement due to primary key violation caused Sql Server to terminate the execution of the Subsequent statements in the batch.

If Sql Server terminates the statement which raised the error and the subsequent statements in the batch then such behavior is termed as Batch Abortion.

The Only difference in the DEMO 2 script from DEMO 1 is the additional first statement SET XACT_ABORT ON. So from the result it is clear that the SET XACT_ABORT ON statement is causing Sql Server to do the Batch Abortion for a Statement Termination Error. It means SET XACT_ABORT ON converts the Statement Terminating errors to the Batch Abortion errors.

Let me clear the Account Table and also reset the Transaction Abort setting by using the below statement before proceeding with the Next DEMO :

DELETE FROM dbo.Account 
SET XACT_ABORT OFF
GO

DEMO 3: Now let us see what will be the result if we execute the below batch of Statements. The only difference between this batch of statement and the DEMO 1 is that the INSERT statements are executed in a Transaction:

BEGIN TRAN
 INSERT INTO dbo.Account(AccountId, Name , Balance) 
 VALUES(1, 'Account1',  10000)
 INSERT INTO dbo.Account(AccountId, Name , Balance) 
 VALUES(1, 'Duplicate', 10000)
 INSERT INTO dbo.Account(AccountId, Name , Balance) 
 VALUES(2, 'Account2',  20000)
COMMIT TRAN
GO

RESULT: Same as the DEMO 1, that is only the statement which raised the error is terminated but continues with the next statement in the batch. Here First and Third Inserts are Successful even though the Second statement raised the error.
Sql Server Error Handling Demo3

Let me clear the Account Table by using the below statement before proceeding with the Next DEMO :

DELETE FROM dbo.Account
GO

DEMO 4: Now let us see what will be the result if we execute the below batch of Statements. The only difference between this batch of statement and the DEMO 2 is that the INSERT statement’s are executed within a Transaction

SET XACT_ABORT ON
BEGIN TRAN
 INSERT INTO dbo.Account(AccountId, Name , Balance)
 VALUES(1, 'Account1',  10000)
 INSERT INTO dbo.Account(AccountId, Name , Balance)
 VALUES(1, 'Duplicate', 10000)
 INSERT INTO dbo.Account(AccountId, Name , Balance)
 VALUES(2, 'Account2',  20000)
COMMIT TRAN
GO

RESULT: No records inserted
Sql Server Error Handling Demo4

From the above example result it is clear that SET XACT_ABORT ON setting not only converts the Statement Termination Errors to the Batch Abortion Errors and also ROLLS BACK any active transactions started prior to the BATCH Abortion errors.

Let me clear the Account Table and also reset the Transaction Abort setting by using the below statement before proceeding with the Next DEMO :

DELETE FROM dbo.Account 
SET XACT_ABORT OFF
GO

DEMO 5: As a part of this DEMO we will verify what happens if a CONVERSION Error occurs within a batch of statement.

CONVERSION ERROR: Trying to convert the string ‘TEN THOUSAND’ to MONEY Type will result in an error. Let us see this with an example:

SELECT CAST('TEN THOUSAND' AS MONEY)

RESULT:
Msg 235, Level 16, State 0, Line 1
Cannot convert a char value to money. The char value has incorrect syntax.

Now let us see what happens if we come across such a CONVERSION error within a batch of statement like the below one:

INSERT INTO dbo.Account(AccountId, Name , Balance) 
VALUES(1, 'Account1', 10000)

UPDATE dbo.Account 
SET Balance = Balance + CAST('TEN THOUSAND' AS MONEY) 
WHERE AccountId = 1

INSERT INTO dbo.Account(AccountId, Name , Balance) 
VALUES(2, 'Account2',  20000)
GO

RESULT: Only the First INSERT is successful
Sql Server Error Handling Demo5

From the above result it is clear that CONVERSION errors cause the BATCH abortion, i.e Sql Server terminates the statement which raised the error and the subsequent statements in the batch. Where as PRIMARY KEY violation was resulting in a Statement Termination as explained in the DEMO 1.

Let me clear the Account Table by using the below statement before proceeding with the Next DEMO :

DELETE FROM dbo.Account
GO

DEMO 6: Now let us see what will be the result if we execute the below batch of Statements. The only difference between this batch of statement and the previous DEMO 5 is that the Batch statement’s are executed within a Transaction

BEGIN TRAN 
 INSERT INTO dbo.Account(AccountId, Name , Balance) 
 VALUES(1, 'Account1', 10000)

 UPDATE dbo.Account 
 SET Balance = Balance + CAST('TEN THOUSAND' AS MONEY) 
 WHERE AccountId = 1

 INSERT INTO dbo.Account(AccountId, Name , Balance) 
 VALUES(2, 'Account2',  20000)
COMMIT TRAN 
GO

RESULT: No records inserted
Sql Server Error Handling Demo6

From the above example result it is clear that CONVERSION errors results in a BATCH Abortion and BATCH Abortion errors ROLLS BACK any active transactions started prior to the BATCH Abortion error.

Let me clear the Account Table and also reset the Transaction Abort setting by using the below statement before proceeding with the Next DEMO :

DELETE FROM dbo.Account 
SET XACT_ABORT OFF
GO

Enough examples, let me summarize the Sql Server Error Actions. Following are the four different ways Sql Server responds(i.e. Error Actions) in response to the errors:

  • Statement Termination
  • Scope Abortion
  • Batch Abortion
  • Connection Termination

Many of these error actions I have explained in the above DEMOs using multiple examples. To explain these error actions further let us take a scenario as shown in the below image, in this scenario from client system an Execution request for the MainSP is submitted and the MainSP internally calls to sub sp’s SubSP1 and SubSP2 one after another:

SqlServerErrorActions1

Statement Termination :

If Sql Server terminates the statement which raised the error but continues to execute the next statements in the Batch. Then such a behavior by a Sql Server in response to an error is called Statement Termination. As shown in the below image the Statement-1 in SubSP1 is causing an error, in response to this Sql Server terminates only the statement that raised the error i.e. Statement-1 but continues executing subsequent statements in the SubSP1 and MainSP calls the subsequent SP SubSp2.

SqlServerErrorActions2

Scope Abortion :

If Sql Server terminates the statement which raised the error and the subsequent statements in the same scope, but continues to execute all the Statements outside the scope of the statement which raised the error. As shown in the below image the Statement-1 in SubSP1 is causing an error, in response to this Sql Server terminates not only the statement that raised the error i.e. Statement-1, but also terminates all the subsequent statements in the SubSP1, but continues executing further all the statements/Sub Sp’s (For Example SubSP2) in the MainSP.

SqlServerErrorActions3

Let us see this behavior with stored procedures similar to the one explained in the above image. Let us execute the below script to create the three stored procedures for this demo:

-------------Scope Abortion Demo-------------
-------Create SubSP1---------
CREATE PROCEDURE dbo.SubSP1
AS
BEGIN
	PRINT 'Begining of SubSP1'
	--Try to access Non-Existent Table
	SELECT * FROM NonExistentTable
	PRINT 'End of SubSP1'
END
GO
-------Create SubSP2---------
CREATE PROCEDURE dbo.SubSP2
AS
BEGIN
	PRINT 'Inside SubSP2'
END

GO
-------Create MainSP---------
CREATE PROCEDURE dbo.MainSP
AS
BEGIN
	PRINT 'Begining of MainSP'
	EXEC dbo.SubSP1
	EXEC dbo.SubSP2	
	PRINT 'End of MainSP'
END
GO

Once the above stored procedures are created, let us execute the MainSP by the below statement and verify the result:

EXEC dbo.MainSP
GO

RESULT:
SqlServerErrorAction8

From the above SP execution results it is clear that the Access for a non existent table NonExistentTable from SubSP1 is not only terminating the statement which try’s to access this NonExistentTable table, but also the Subsequent statements in the SubSP1’s scope. But Sql Server continues with the execution of the subsequent statements which are present in the in the MainSP which has called this SubSP1 and also the SubSP2 is called from the MainSP.

Let us drop all the Stored Procedures created in this demo by using the below script:

DROP PROCEDURE dbo.SubSP2
DROP PROCEDURE dbo.SubSP1
DROP PROCEDURE dbo.MainSP
GO

Batch Abortion :

If Sql Server terminates the statement which raised the error and the subsequent statements in the batch then such behavior is termed as Batch Abortion. As shown in the below image the Statement-1 in SubSP1 is causing an error, in response to this Sql Server terminates not only the statement that raised the error i.e. Statement-1, but also terminates all the subsequent statements in the SubSP1 and it will not execute the further statements/Sub Sp’s (For Example SubSP2) in the MainSP. Batch Abortion Errors ROLLS BACK any active transactions started prior to the statement which causes BATCH Abortion error.

BatchAbortion

We have already seen multiple Batch Abortion examples in the above DEMOs. Here let us see this behavior with stored procedures similar to the one explained in the above image. Let us execute the below script to create the three stored procedures for this demo:

------------Batch Abortion Demo --------------
-------Create SubSP1---------
CREATE PROCEDURE dbo.SubSP1
AS
BEGIN
	PRINT 'Begining of SubSP1'
	PRINT CAST('TEN THOUSAND' AS MONEY)
	PRINT 'End of SubSP1'
END
GO
-------Create SubSP2---------
CREATE PROCEDURE dbo.SubSP2
AS
BEGIN
	PRINT 'Inside SubSP2'
END
GO
-------Create MainSP---------
CREATE PROCEDURE dbo.MainSP
AS
BEGIN
	PRINT 'Begining of MainSP '
	EXEC dbo.SubSP1
	EXEC dbo.SubSP2	
	PRINT 'End of MainSP '
END
GO

Once the above stored procedures are created, let us execute the MainSP by the below statement and verify the result:

EXEC dbo.MainSP
GO

RESULT:
SqlServerErrorAction7

From the above SP execution results it is clear that the CONVERSION/CAST statement in the SubSP1 is causing the Batch Abortion.It is not only terminating the statement which raised the error but all the subsequent statement in the SubSP1 and the further statement in the MainSP which has called this SubSP1 and also the SubSP2 is not called from the MainSP post this error.

Let us drop all the Stored Procedures created in this demo by using the below script:

DROP PROCEDURE dbo.SubSP2
DROP PROCEDURE dbo.SubSP1
DROP PROCEDURE dbo.MainSP
GO

Connection Termination :

Errors with severity level 20-25 causes the Connection Termination. Only User with SysAdmin rights can raise error’s with these severity levels. As shown in the below image the Statement-1 in SubSP1 is causing an error with severity 20-25, in response to this Sql Server terminates not only the statement that raised the error i.e. Statement-1, but also terminates all the subsequent statements in the SubSP1 and it will not execute the further statements/Sub Sp’s (For Example SubSP2) in the MainSP. And finally terminates the connection. Note if there are any active Transactions which are started prior to the statement which caused the Connection Termination error, then Sql Server Takes care of Rolling Back all such transactions.

SqlServerErrorActions5

If we use RaiseError with WITH LOG option to raise an exception with severity level >=20 will result in a connection termination. Let us execute the below statement and observe the result:

RAISERROR('Connection Termination Error Demo', 20,1) WITH LOG
GO

RESULT: Connection is Terminated
SqlServerErrorAction6

Below query gives the list of Error’s that cause the Connection Termination.

SELECT * FROM sys.messages 
WHERE severity >= 20 and language_id =1033

Clean-UP:
Let us drop the database which we have created for this demo

--Drop the Database SqlHintsErrorHandlingDemo
USE TempDB
GO
DROP DATABASE SqlHintsErrorHandlingDemo

Let us know your feedback on this post, hope you have learnt something new. Please correct me if there are any mistakes in this post, so that I can correct it and share with the community.

Differences Between RAISERROR and THROW in Sql Server

Both RAISERROR and THROW statements are used to raise an error in Sql Server. The journey of RAISERROR started from Sql Server 7.0, where as the journey of THROW statement has just began with Sql Server 2012. obviously, Microsoft suggesting us to start using THROW statement instead of RAISERROR. THROW statement seems to be simple and easy to use than RAISERROR.

This is the third article in the series of articles on Exception Handling in Sql Server. Below is the complete list of articles in this series.

Part   I: Exception Handling Basics – MUST Read Article
Part  II: TRY…CATCH (Introduced in Sql Server 2005)
Part III: RAISERROR Vs THROW (Throw: Introduced in Sql Server 2012)
Part IV: Exception Handling Template

Raiserror Vs Throw

Below table lists-out 10 major difference between RAISERROR and THROW with examples:

RAISERROR THROW
Version of the Sql Server in which it is introduced?
Introduced in SQL SERVER 7.0. And as per BOL, Microsoft is suggesting to start using THROW statement instead of RAISERROR in New Applications.

RAISERROR can’t be used in the Sql Server 2014’s Natively compiled Stored Procedures.

Introduced in SQL SERVER 2012. THROW statement seems to be simple and easy to use than RAISERROR.

THROW statement can be used in the Sql Server 2014’s Natively Compiled Stored Procedure.

SYNTAX
RAISERROR 
 ( { error_number | message 
        | @local_variable }
    { ,severity ,state }
    [ ,argument [ ,...n ] ] )
    [ WITH option [ ,...n ] ]
THROW 
 [ { error_number 
     | @local_variable },
   { message | @local_variable },
   { state | @local_variable } ] 
[ ; ]
Can re-throw the original exception that invoked the CATCH block?
NO. It always generates new exception and results in the loss of the original exception details. Below example demonstrates this:

BEGIN TRY
  DECLARE @result INT
--Generate divide-by-zero error
  SET @result = 55/0
END TRY
BEGIN CATCH
--Get the details of the error
--that invoked the CATCH block
 DECLARE 
   @ErMessage NVARCHAR(2048),
   @ErSeverity INT,
   @ErState INT

 SELECT
   @ErMessage = ERROR_MESSAGE(),
   @ErSeverity = ERROR_SEVERITY(),
   @ErState = ERROR_STATE()

 RAISERROR (@ErMessage,
             @ErSeverity,
             @ErState )
END CATCH

RESULT:
Msg 50000, Level 16, State 1, Line 19
Divide by zero error encountered.

NOTE:The actual line number of the code which generated Divided By Zero error here is 4, but the exception message returned by RAISERROR is showiung it as 19. Also the error number corresponding to divide by zero error is 8134 in the SYS.Messages table, but the one returned by RAISERROR is 50000.

YES. To Re-THROW the original exception caught in the TRY Block, we can just specify the THROW statement without any parameters in the CATCH block. Below example demonstrates this:

BEGIN TRY
  DECLARE @result INT
--Generate divide-by-zero error
  SET @result = 55/0
END TRY
BEGIN CATCH
    THROW
END CATCH

RESULT:
Msg 8134, Level 16, State 1, Line 4
Divide by zero error encountered.

With above example it is clear that THROW statement is very simple for RE-THROWING the exception. And also it returns correct error number and line number.

Causes the statement batch to be ended?
Example 1: In the below Batch of statements the PRINT statement after RAISERROR statement will be executed.

BEGIN
 PRINT 'BEFORE RAISERROR'
 RAISERROR('RAISERROR TEST',16,1)
 PRINT 'AFTER RAISERROR'
END

RESULT:

BEFORE RAISERROR
Msg 50000, Level 16, State 1, Line 3
RAISERROR TEST

AFTER RAISERROR

Example 2: In the below example all the statement’s after RAISERROR statement are executed.

BEGIN TRY
 DECLARE @RESULT INT = 55/0	
END TRY
BEGIN CATCH
 PRINT 'BEFORE RAISERROR';

--Get the details of the error
--that invoked the CATCH block
 DECLARE 
  @ErMessage NVARCHAR(2048),
  @ErSeverity INT,
  @ErState INT

 SELECT
  @ErMessage = ERROR_MESSAGE(),
  @ErSeverity = ERROR_SEVERITY(),
  @ErState = ERROR_STATE()

 RAISERROR (@ErMessage,
             @ErSeverity,
             @ErState )

 PRINT 'AFTER RAISERROR'
END CATCH
 PRINT 'AFTER CATCH'

RESULT:
BEFORE RAISERROR
Msg 50000, Level 16, State 1, Line 19
Divide by zero error encountered.

AFTER RAISERROR
AFTER CATCH

Example 1: In the below Batch of statements the PRINT statement after THROW statement will not executed.

BEGIN
	PRINT 'BEFORE THROW';
	THROW 50000,'THROW TEST',1
	PRINT 'AFTER THROW'
END

RESULT:

BEFORE THROW
Msg 50000, Level 16, State 1, Line 3
THROW TEST

Example 2: In the below example no PRINT statement’s after THROW statement are executed.

BEGIN TRY
  DECLARE @RESULT INT = 55/0	
END TRY
BEGIN CATCH
  PRINT 'BEFORE THROW';
  THROW;
  PRINT 'AFTER THROW'
END CATCH
  PRINT 'AFTER CATCH'

RESULT:
BEFORE THROW
Msg 8134, Level 16, State 1, Line 2
Divide by zero error encountered.

CAN SET SEVERITY LEVEL?
YES. The severity parameter specifies the severity of the exception. NO. There is no severity parameter. The exception severity is always set to 16. (unless re-throwing in a CATCH block)
Requires preceding statement to end with semicolon (;) statement terminator?
NO. YES. The statement before the THROW statement must be followed by the semicolon (;) statement terminator.
CAN RAISE SYSTEM ERROR MESSAGE?
The SYS.MESSAGES Table will have both system-defined and user-defined messages. Message IDs less than 50000 are system messages.
YES. With RAISERROR we can raise the System Exception.
Example:
RAISERROR (40655,16,1)RESULT:
Msg 40655, Level 16, State 1, Line 1
Database ‘master’ cannot be restored.
NO. With THROW we can’t raise the System Exception. But when it used in CATCH BLOCK it can Re-THROW the system exception.Example: Trying to raise system exception (i.e. exception with ErrorNumber less than 50000).

THROW 40655, ‘Database master cannot be restored.’, 1

RESULT:
Msg 35100, Level 16, State 10, Line 1
Error number 40655 in the THROW statement is outside the valid range. Specify an error number in the valid range of 50000 to 2147483647

CAN RAISE user-defined message with message_id greater than 50000 which is not defined in SYS.MESSAGES table?
NO. If a msg_id is passed to RAISERROR, the ID must be defined in sys.messages.Example:

RAISERROR (60000, 16, 1)

RESULT:
Msg 18054, Level 16, State 1, Line 1
Error 60000, severity 16, state 1 was raised, but no message with that error number was found in sys.messages. If error is larger than 50000, make sure the user-defined message is added using sp_addmessage.

Now add the Message to SYS.MESSAGES Table by using the below statement:

EXEC sys.sp_addmessage 60000, 16, ‘Test User Defined Message’

Now try to Raise the Error:
RAISERROR (60000, 16, 1)

RESULT:
Msg 60000, Level 16, State 1, Line 1
Test User Defined Message

YES. The error_number parameter does not have to be defined in sys.messages.Example:
THROW 60000, ‘Test User Defined Message’, 1RESULT:
Msg 60000, Level 16, State 1, Line 1
Test User Defined Message
Allows substitution parameters in the message parameter?
By using the below statement add a sample test message with parameteres to the SYS.Messages Table:
EXEC sp_addmessage 70000,16,‘Message with Parameter 1: %d and Parameter 2:%s’
YES.The msg_str parameter can contain printf formatting styles.Example 1:

RAISERROR (70000, 16, 1, 505,‘Basavaraj’ )

RESULT:
Msg 70000, Level 16, State 1, Line 1
Message with Parameter 1: 505 and Parameter 2:Basavaraj

NO.The message parameter does not accept printf style formatting.Example 1:

THROW 70000, ‘Message with Parameter 1: %d and Parameter 2:%s’, 1, 505,’Basavaraj’

RESULT:
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near ‘,’.

Alternative Way of doing this is:

DECLARE @ErrorMsg NVARCHAR(2048) = FORMATMESSAGE(70000, 505, ‘Basavaraj’ );
THROW 70000, @ErrorMsg, 1

Example 2: Message manipulation is not allowed in the THROW statement

Below statement will fail

THROW 58000,‘String1’ + ‘ String2’,1

RESULT:
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near ‘+’.

We can solve such problems, we can prepare the message prior to the THROW statement and then pass it to throw statement as a variable. Below example illustrates this.

DECLARE @message NVARCHAR(2048)
SET @message = ‘String1’ + ‘ String2’;
THROW 58000, @message, 1

RESULT:
Msg 58000, Level 16, State 1, Line 3
String1 String2

RAISERROR WITH NOWAIT statement can also be used to flushes all the buffered PRINT/SELECT Statement Messages within a batch.

[ALSO READ] You may like to read below other popular articles on differences

1. Varchar vs NVarchar
2. Varchar vs Varchar(MAX)
3. Char vs Varchar
4. Text vs Varchar(Max)
5. Union vs Union All
6. DateTime vs DateTime2
7. SET QUOTED_IDENTIFIER ON vs SET QUOTED_IDENTIFIER OFF
8. Stored Procedure vs User Defined Function
9. Primary Key vs Unique Key
10. RAISERROR vs THROW
11. Temporary Table vs Table Variable
12. Len() vs Datalength()
13. Sequence vs Identity
14. DATEDIFF vs DATEDIFF_BIG