Holi Sale. Get upto 40% OFF on Job-oriented Training! Offer Ending in
D
H
M
S
Get Now
Browse Tutorials
SQL Server Exceptions Working

SQL Server Exceptions Working

04 Mar 2024
Advanced
138K Views
2 min read

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.

Take our free sqlserver skill challenge to evaluate your skill

In less than 5 minutes, with our skill challenge, you can identify your knowledge gaps and strengths in a given skill.

GET CHALLENGE

Share Article
About Author
Shailendra Chauhan (Microsoft MVP, Founder & CEO at Scholarhat by DotNetTricks)

Shailendra Chauhan is the Founder and CEO at ScholarHat by DotNetTricks which is a brand when it comes to e-Learning. He provides training and consultation over an array of technologies like Cloud, .NET, Angular, React, Node, Microservices, Containers and Mobile Apps development. He has been awarded Microsoft MVP 8th time in a row (2016-2023). He has changed many lives with his writings and unique training programs. He has a number of most sought-after books to his name which has helped job aspirants in cracking tough interviews with ease.
Accept cookies & close this