Please enable Javascript to correctly display the contents on Dot Net Tricks!

SQL Server Exceptions Working

  Author : Shailendra Chauhan
Updated On : 24 Sep 2016
Total Views : 117,572   
Support : SQL Server 2005,2008,2012
 

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.

YOU MIGHT LIKE
Free Interview Books
 
COMMENTS (0)
14 DEC
ASP.NET MVC with AngularJS Development (online)

MON-FRI 07:30 AM- 09:00 AM IST

Know More
5 DEC
AngularJS Development (online)

Mon - Fri     6:30 AM-7:30 AM IST

3 DEC
AngularJS Development (offline)

SAT,SUN     11:00 AM-12:30 PM IST

3 DEC
MEAN Stack Development (offline)

Sat, Sun     (09:30 AM-11:00 AM IST)

26 NOV
ASP.NET MVC with AngularJS Development (offline)

(SAT,SUN)     03:30 PM-05:00 PM IST

24 NOV
ASP.NET MVC with AngularJS Development (online)

MON-FRI     09:30 PM-11:00 PM IST

12 NOV
ASP.NET MVC with AngularJS Development (offline)

SAT,SUN     08:00 AM-09:30 AM

3 NOV
ASP.NET MVC with AngularJS Development (online)

MON-FRI     07:30 AM-09:00 AM IST

25 OCT
.NET Development (offline)

Mon-Fri     9:00 AM-11:00 AM IST

BROWSE BY CATEGORY
 
RECENT ARTICLES
SUBSCRIBE TO LATEST NEWS
 
LIKE US ON FACEBOOK
 

Professional Speaks

+