Programming example:

Declare @valu1 int;
Declare @valu2 int;
BEGIN TRY
Set @valu1 = 9;
Set @Valu2 = @Valu1 % 2;
If @Valu1 = 1
Print ‘Error Not occurred’
Else
BEGIN
Print ‘Error Occurs’;
Throw 80000, ‘Number is even’, 5
End
END TRY
BEGIN CATCH
Print ‘Error occurs that is: ‘
Print Error_Message ()
END CATCH
Output:
Error occurs
The Error occurs that is:
Number is even

Here 80000 denote the error number, and 5 represents the state where the error message has occurred.

System functions and keywords used within the CATCH block:

Now we are going to explain the few programming examples to define these functions and system keyword;
First, we need to create a table and enter some value into the table as follow;

CREATE TABLE emp
(
Emp_ID int Identify (1,1),
First_name Nvarchar (MAX) not Nullval,
Last_name Nvarchar (MAX) not Nullval,
Salary Int Not Null check (Salary > 20,000),
City Nvarchar (max) Not Nullval
)
Insert data into the table values;
Select ‘Kavya,’ ‘Gowda,’ 50000, ‘Alwar’ Union ALL
Select ‘Rahul,’ ‘chowdary,’ 25000, ‘Alwar’ Union ALL
Select ‘Sandeep,’ ‘Prajapati,’ 23000, ‘Alwar’ Union ALL
Select ‘Sanjeev,’ ‘Jangid,’ 27000, ‘Alwar’ Union ALL
Select ‘Neeraj,’ ‘Baldia,’ 24000, ‘Alwar’ Union ALL
Select ‘Narendra,’ ‘Saini,’ 22000, ‘Alwar’ Union ALL
è Now it’s time to execute the ‘SELECT’ statements by using the command,
Select * from Employee
Serl.no
Emp_id
First_name
Last_name
Salary
City
1
1
Kavya
Gowda
50000
Alwar
2
2
Rahul
Chowdary
25000
Alwar
3
3
Sandeep
Prajapat
23000
Alwar
4
4
Sanjeev
Jangid
27000
Alwar
5
5
Neeraj
Baldia
24000
Alwar
6
6
Narendra
Saini
22000
Alwar

Exception handling properties:

Example1: @ERROR;

This statement returns the error number for the last executed SQL statements. It returns zero if the previous transaction SQL statements were encountered, and it will not return an error number.

UPDATE Employee SET Salary = 20000 WHERE Emp_ID = 5
IF @ERROR = 547
PRINT ‘A check constraints violation error occurred. ‘;
Output:
Msg 557, level 14, State 0, Line 1
The UPDATE statement conflicted with the method CHECK constraint ‘CK_Employee_salary_68487DD7’.

Example 2: ERROR_NUMBER

This Exception handling property returns the number values which causes the errors. It would replace the ZERO if we called outside the CATCH block statement.

BEGIN TRY
UPDATE Employee SET salary_val = 20000 WHERE Emp_ID = 6
END TRY
BEGIN CATCH
SELECT ERROR_NUMBER () AS errorNumber;
END CATCH;
OUTPUT:
ERRORNUMBER = 557

Example 3:

ERROR_MESSAGE returns the errors which are caused by the messages. This returns the type of ERROR_MESSAGE is the type of nvarchar (5000).

BEGIN TRY
UPDATE Employee SET Salary =20000 WHERE EMP_ID = 6
END TRY
BEGIN CATCH
SELECT ERROR_MESSAGE () AS ERRORMSG;
END CATCH;
OUTPUT:
The UPDATE statement conflict the CHECK Constraint ‘CK_EMPLOYEE_SALARY_68487DD7’. This conflict method will be stored in HOME_MANAGMENT.

EXAMPLE 4: ERROR_STATE

ERROR_STATE returns the number of state errors. The data type of ERROR_STATE is Integer.

BEGIN TRY
SELECT SALARY+ FIRST _NAME from EMPLOYEE WHERE Emp_ID = 6
END TRY
BEGIN CATCH
SELECT ERROR_STATE () AS ERRORState, ERROR_MESSAGE () ErrorMSG;
END CATCH;
OUTPUT:
ErrorState = 1
ErrorMsg = Conversion failed error message where it returns the value in Nvarchar.

Example 5: ERROR_LINE

ERROR_LINE exception handling returns the line number where the Error has occurred. The return type of ERROR_LINE is Integer.

BEGIN TRY
SELECT SALARY + First_name from Employee WHERE EMP_ID = 6
END TRY
BEGIN CATCH
SELECT ERROR_STATE () As ErrorLine;
END CATCH;
OUTPUT:
ErrorLine = 1

Example 6: ERROR_PROCEDURE

ERROR_PROCEDURE returned the name of the stored triggers and procedure when an error occurred. The return type is ERROR_PROCEDURE is nvarchar (128).

Join our BMC Marimba training today and enhance your skills to new heights!

Subscribe to our YouTube channel to get new updates..!

RETURN VALUE:

Return Value Returns the stored procedure Name if any Error has occurred in the stored procedure or trigger once the CATCH statement is blocked.

It returns the NULL value if the Error does not occur within a stored procedure or any type of trigger that is called outside the CATCH block.

The code is as follows;

CREATE procedure My_prnc
AS
BEGIN TRY
SELECT SALARY + First_Name FROM Employee Where Emp_ID = 6
END TRY
BEGIN CATCH
SELECT ERROR_PROCEDURE () As Prnc;
END CATCH;
END
OUTPUT:
01. Exec My_Prnc,
Prnc:
My_Prnc

Example 7: ERROR_SEVERITY

ERROR_SEVERITY returns the severity of the Error. The data type of ERROR_SEVERITY is Int.

The code is as follows;

BEGIN TRY
SELECT SALARY + First_Name from Employee WHERE EMP_ID = 6
END TRY
BEGIN CATCH
SELECT ERROR_SEVERITY () AS ErrorSeverity;
END CATCH;
OUTPUT:
ErrorSeverity
16

The severity level of the error message provides an indication of error type that occurred in Microsoft SQL Server. In the below example the Severity level is 15 so the system will give you a message like Permission denied.

Some Important Severity levels are;
Serial.no of severity
Description

13
Which indicates the transaction deadlock errors

14
Indicates the security-related error levels, by that time system will generate an error message like permission denied

15
This severity indicates the syntax error in the form of Transact-SQL command

16
This severity level indicates the general errors that will be corrected by the user

TRANSACTION management in Exception handling

This is a very important type of exception handling; let’s get into the full details

BEGIN TRANSACTION Trans
BEGIN TRY
DELETE From Employee Where Employee. Emp_ID <4
UPDATE EMPLOYEE SET Employee. First_Name = ‘Kavya’ where Employee. EMP_ID = ‘5
IF @@TRANSCount > 0
BEGIN COMMIT Transaction Transt
END
END TRY
BEGIN CATCH
If @@TransCount > 0
Print ‘ERROR is occurred in Transaction’
BEGIN Rollback Transaction Transt
END
END CATCH
SELECT *FROM Employee
OUTPUT:
Serial number
Emp_id
First_name
Last_name
Salary
City
1
1
Kavya
Gowda
50000
Alwar
2
2
Rahul
Chowdary
25000
Alwar
3
3
Sandeep
Prajapat
23000
Alwar
4
4
Sanjeev
Jangid
27000
Alwar
5
5
Neeraj
Baldia
24000
Alwar
6
6
Narendra
Saini
22000
Alwar

Top 30 frequently asked Maximo Interview Questions!

INSIGHT:

In this article, I have explained the important concepts about Exception handling in SQL server. The Exception is nothing but Errors that occur during the time of programming execution.The mechanism which is used to handle the Errors is called Handling. And also I have explained the properties and types of the Exception handling mechanism in the SQL server.I hope this article may help a few of you to learn the important concepts of Error handling and also enables you to communicate with experts across the world via social forums.

Related Articles:

1. Isolation Levels in SQL Server

2. SQL Server Joins

3. SQL Server Data Tools