Total Page Preview: 000000002021
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