Exception Handling in SQL Server

I recently worked on creating the table and encountered an error. Errors are common in a development project. Exception handling or Error handling is an essential concept in software development. Like other programming languages, namely C, C++, and Java, SQL Server also provides exception handling. TRY and CATCH can handle errors in SQL Server. This tutorial will teach you to handle errors using TRY and catch statements.

Exception Handling in SQL Server

We face errors in databases and programming languages. When an error occurs in a programming language, the program is stopped, and the statements unrelated to the error are allowed to continue executing.

However, when an error occurs in SQL Server, the related statements are prevented from continuing to execute.

Try/Catch blocks were added to SQL Server 2005. Because of this, the database’s error-handling capabilities are now very similar to those of programming languages like Java and C#.

Syntax of Exception Handling

Below is the syntax for handling errors in the SQL Server.

Begin Try
End Try
Begin Catch 
End Catch 

Let’s see a simple example with output to understand

Begin Try
select 10/2
Print 'Yes'
End Try
Begin Catch 
Print 'No'
End Catch 

If an error is encountered, the result will be thrown as No.

Create a Procedure for Exception Handling

Now, we will see how to create a procedure for handling exceptions.

Create Proc EXCHAN
AS
BEGIN
Begin Try
select 10/0
Print 'Yes'
End Try
Begin Catch 
Print 'No'
End Catch 
END

In error handling, SQL Server provides several functions, such as Select ERROR_LINE(), ERROR_SEVERITY(), ERROR_STATE(), ERROR_LINE(), ERROR_PROCEDURE(), and ERROR_MESSAGE(). These are all used by developers when used in projects.

• Error Number – This will return the error number and its value for @@ERROR.
• Error Line – This gives back the T-SQL statement’s line number, which resulted in an error.
• Error Severity – This will return the severity level of the error
• Error state – This returns the state number of the error.
• Error Procedure -This gives back the name of the trigger or stored procedure that caused the error.
• Error Message: This fully returns the error message. The values provided for any substitutable parameters, such as lengths, object names, or times, are included in the text.

Here are the details of the error, such as the number, severity, state line of error, procedure name, and error message.

Exception Handling with Two Datatype

Here, we will see how exception handling works when we try to join two data types. The code with the output is below.

We are trying to use two data types and see the error list.

Begin Try
Declare @number int
select @number+'peter'
End Try
Begin Catch 
Select 
ERROR_Number() ErrorNumber
,ERROR_SEVERITY() Errorseverity
,ERROR_STATE() ErrorState
,ERROR_LINE() Errorline
,ERROR_PROCEDURE() ErrorProcedure
,ERROR_MESSAGE() ErrorMessage;
End Catch ;

Let’s see one more example with the sample table. Here, my table name is HospitalInfo. We will try exception handling for this table. Below is the query with the output.

Begin Try
select Hospitalid + Hospitalname from HospitalInfo
End try
Begin catch
Print ' Cannot add a integer value with string value'
End catch

Types of SQL Server Exception

There are two types of Exception, namely,

• System Defined
• User Defined

System Defined Exception

In a system-defined exception, the system generates the error.

Declare @val1 int;
Declare @val2 int;
BEGIN TRY
Set @val1=8;
Set @val2=@val1/0; 
END TRY
BEGIN CATCH
Print 'Error Occur that is:'
Print Error_Message()
END CATCH

User Defined Exception

The user generates this type of error.

Declare @val1 int;
Declare @val2 int;
BEGIN TRY
Set @val1=8;
Set @val2=@val1%2;
if @val1=1
Print ' Error Not Occur'
else
Begin
Print 'Error Occur';
Throw 60000,'Number Is Even',5
End

END TRY
BEGIN CATCH
Print 'Error Occur that is:'
Print Error_Message()
END CATCH

FAQs

In SQL Server, which keyword is used to begin an exception handling block?

Answer: BEGIN TRY

How exception handling is possible in SQL Server using?

Answer: Using the “TRY…CATCH” block.

Conclusion

I hope this simple tutorial helps you to understand the try-and-catch technique in SQL Server to implement exception handling in SQL Server. Try from your end and see the changes in your database.

You may like the following SQL Server tutorials:

• SQL Server Convert Datetime to String
• How to execute function in SQL with parameters
• SQL Server Agent won’t start