Total Page Preview:   000000000164

What are the different types of Error Handling in SQL Server?

There are many type of error handing in SQL Server:
1. TRY CATCH
The first error encountered in a TRY block will direct you to its CATCH block ignoring the rest of the code in the TRY block will generate an error or not.
 
BEGIN TRY  
   -- Write statements here that may cause exception
END TRY
BEGIN CATCH  
   -- Write statements here to handle exception
END CATCH
 
 
2. @@error
stores the error code for the last executed SQL statement. If there is no error, then it is equal to 0.
If there is an error, then it has another number (error code).
 
Update Product set ProductCode=190 Where ProductId=5  
IF @@ERROR = 547  
PRINT 'A check constraint violation occurred.'; 
 
3. RAISERROR() function
A system defined function that is used to return messages back to applications using the same format which SQL uses for errors or warning message.
 
BEGIN TRY  
SELECT ProductId + ProductName FROM  tblProduct Where ProductId=5  
END TRY  
BEGIN CATCH  
RAISERROR(N'An Error Is Occur',16,3); 
END CATCH;  
GO 
 
4. THROW Statement
THROW statement in SQL Server raises an exception and transfers the control to a CATCH block.
 
BEGIN TRY
   Delete FROM tblProduct Where [ProductId] = 9
END TRY
BEGIN CATCH
   THROW 50000, N'Unable to delete record...', 1;
END CATCH
 
 
5. ERROR_MESSAGE() 
You can take advantage of this function to get the complete error message.
 
BEGIN TRY  
Update Product set ProductCode=190 Where ProductId=5  
END TRY  
BEGIN CATCH  
SELECT ERROR_NUMBER() AS ErrorNumber;  
END CATCH;  
GO 
 
6. ERROR_LINE() 
This function can be used to get the line number on which the error occurred.
 
BEGIN TRY  
SELECT ProductId + ProductName FROM  tblProduct Where ProductId=5  
END TRY  
BEGIN CATCH  
SELECT ERROR_LINE() AS ErrorLine; 
END CATCH;  
GO 
 
7. ERROR_NUMBER()
This function can be used to get the error number of the error.
 
BEGIN TRY  
Update tblProduct set ProductName=100 Where ProductId=5  
END TRY  
BEGIN CATCH  
SELECT ERROR_NUMBER() AS ErrorNumber;  
END CATCH;  
GO  
 
 
8. ERROR_SEVERITY() 
This function can be used to get the severity level of the error.
 
BEGIN TRY  
SELECT ProductId + ProductName FROM tblProduct  Where ProductId=5  
END TRY  
BEGIN CATCH  
SELECT ERROR_SEVERITY() AS ErrorSeverity;  
END CATCH;  
GO 
 
9. ERROR_STATE() 
This function can be used to get the state number of the error.
 
BEGIN TRY  
SELECT ProductId + ProductName FROM  tblProduct Where ProductId=5  
END TRY  
BEGIN CATCH  
SELECT ERROR_STATE() AS ErrorState , ERROR_MESSAGE() ErrorMsg ; 
END CATCH;  
GO 
 
10. ERROR_PROCEDURE() 
This function can be used to know the name of the stored procedure or trigger that has caused the error.
 
BEGIN TRY  
SELECT ProductId + ProductName FROM tblProduct  Where ProductId=5  
END TRY  
BEGIN CATCH  
SELECT ERROR_PROCEDURE() AS ProcName; 
END CATCH;  
GO 
 
 
 

 

Thank You

About Author

Brijesh Kumar

Database Developer

I have more then 6 years Experience in Microsoft Technologies - SQL Server Database, ETL Azure Cloud - Azure SQL Database, CosmosDB, Azure Data Factory, PowerBI, Web Job, Azure Function, Azure Storage, Web Apps, Powershall and Database Migration On-Premise to Azure Cloud.
LinkedIn : https://www.linkedin.com



Comments


                           
                           

                           

                           

Recent Posts

How to get the alternate rows or records from table in sql server
07-Jun-2017
Data Warehouse Three Tier Architecture
20-Aug-2017
SQL Server - Fragmentation on azure sql database
07-Oct-2018
Limitations of Azure SQL Database
19-Sep-2019
PIVOT and UNPIVOT in SQL Server with Example
29-Jun-2017
SQL Server - Find all index with key columns on Azure SQL Database
02-Oct-2018
SQL Server - Find unused indexes on azure SQL database scripts
02-Oct-2018
Find Resource usage in Azure SQL Database using script or T-SQL
07-Oct-2018
How to Set client IP Address using the firewall security in Azure SQL Using Portal
29-Jun-2017
What is a Trigger and type of trigger in SQL server?
10-Sep-2021
What is Table Variable? Explain its advantages and disadvantages?
10-Sep-2021
Top 60 SQL Server Interview Questions Answers for freshers and experienced
17-Sep-2021
Cloud Data Engineer SQL Server Interview Questions Answers for freshers and experienced
31-Aug-2021
Top 16 performance query optimization techniques in SQL Database?
16-Sep-2021
Data warehouse Interview Questions Answers for freshers and experienced
16-Sep-2021
What are the types of User Define Function and difference between a nested UDF and recursive UDF in SQL server?
10-Sep-2021
What are the different types of Error Handling in SQL Server?
11-Sep-2021
What are the differences between OLTP and OLAP?
07-Sep-2021
How many types of dimension tables available in data warehouse?
16-Sep-2021
What are characteristics and advantages of stored procedure?
10-Sep-2021

Facebook User: