SQL Server has an exception model to handle exceptions and errors that occurs in T-SQL statements. Exception handling in Sql Server is like as exception handling in other programming language. To understand exception handling, first we need to know how many types of exception we have in Sql Server.
Types of Exceptions
This type of exception aborts only the current running statement within a batch of T-SQL statements. The rest of the T-SQL statements will execute successfully if they have no exceptions. Let us see the below example.
--Batch SELECT POWER(4, 28) PRINT 'This statement will execute' GO
This type of exception aborts only the batch in which exception occurs. The rest of the batches will execute successfully if they have no exceptions. The statement in which exception occurs will be aborted and the remaining T-SQL statements within the batch will also stopped.
--First Batch DECLARE @var DECIMAL; set @var= CONVERT(DECIMAL, 'xyz') PRINT @var PRINT 'This statement will not execute' GO --Second Batch DECLARE @var DECIMAL; set @var= CONVERT(DECIMAL, '12.35') PRINT @var PRINT 'This statement will execute' GO
Parsing and Scope-Resolution Exception
This types of exception occurs during the parsing and during the scope-resolution phase of compilation. This exception appears to behave just like batch-level exceptions. However, this has a little different behavior.
If the exception occurs in the same scope of the batch, it behaves just like a batch-level exception.If the exception occurs in a lower level of scope of the batch, it behaves just like statement-level exception.
--Parsing Error SELECTEmpID,Name FROM Employee PRINT 'This statement will execute' GO
--For Successfully execution we need to executed select statement as dynamic SQL using the EXEC function EXEC('SELECTEmpID,Name FROM Employee') PRINT 'This statement will execute' GO
Scope Resolution Exception
--First Create a procedure CREATE PROCEDURE usp_print AS BEGIN Select * from tbl END GO
--Now execute above created procedure in batch EXEC usp_print PRINT 'This statement will execute' GO --Since the stored procedure creates a new scope. Hence rest statement will be executed
In this article I try to explain how types of Exception in Sql Server with example. I hope after reading this article your will be aware of exceptions in Sql Server. I would like to have feedback from my blog readers. Please post your feedback, question, or comments about this article.