SQL Server Exceptions Working

Print 2 min read

31 Aug 2022

Advanced

137K Views

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

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 
```
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 
```

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.