How to Handle exceptions in SQL Server?

In SQL Server, exceptions can be handled using the TRY...CATCH block. The TRY block is used to enclose the code that may cause an exception, and the CATCH block is used to handle the exception.

Here is an example of how to handle an exception in SQL Server:

BEGIN TRY
-- code that may cause an exception
SELECT 1/0
END TRY
BEGIN CATCH
-- code to handle the exception
PRINT 'An error occurred: ' + ERROR_MESSAGE()
END CATCH;

In this example, the code in the TRY block attempts to divide 1 by 0, which will cause a divide-by-zero exception. The code in the CATCH block will then be executed, and the error message will be printed with function ERROR_MESSAGE.

You can also retrieve information about the error using the ERROR_NUMBER and ERROR_LINE functions, which return the error number and line number where the error occurred, respectively.

It's also a good practice to use a transaction when doing the operation that may cause the exception, so that you can rollback the transaction in case of exception, this way the data will not be in an invalid state. In addition, you can use the RAISERROR statement to raise a user-defined error, this way you can customize the error message and handle it accordingly.