SQL Server Exceptions Working

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

1. Statement-Level Exception

   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 

   

2. Batch-Level Exception

   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 

   

3. 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 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 

   

Summary

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.

Further Reading