How To Resolve Error Handling in SQL Server Using TRY… CATCH Command

You can use SQL Server’s error handling to control your Transact-SQL code. For example, if something goes wrong, we have the opportunity to do something about it. Troubleshooting SQL Server errors can be as simple as recording an event, or you may want to fix the error.

Overview

Since we all know that SQL Server technical error messages are difficult to understand and understand, you can translate the errors into the SQL language as well. Fortunately, we can translate these messages into something more meaningful to keep users, developers, and more informed.

In this article, we’ll take a closer look at the TRY … CATCH statement (syntax, form, functionality, and what to do if it fails). This method is also described in the SQL Server case using T-SQL statements / block groups. This is basically the SQL Server way of handling errors. This is a very simple but structured method, and once you get used to it it can be very useful in many cases.

• More info: How To Find Out HTTP Request With PHP That Rarely Known

There is also a RAISERROR function that can be used to generate custom error messages. This is a great way to turn confusing error messages into something that matters to people.

Handling errors using TRY…CATCH

This is the syntax. It is very easy to understand the trick. There are two blocks of code.

Everything between BEGIN TRY and END TRY is the code that you want to monitor for errors. So if an error occurs within this TRY instruction, control is immediately transferred to the CATCH instruction and then starts executing the code line by line.

BEGIN TRY
–code to try
END TRY
BEGIN CATCH
–code to run if an error occurs
–is generated in try
END CATCH

Now you can fix a bug in the CATCH statement, report a bug, or log it so you know when it happened, who did it by logging the username and all the useful information. You can also access some special data that is only available in the CATCH statement.

• ERROR_NUMBER – returns the internal number of the error.
• ERROR_STATE returns information about the source.
• ERROR_SEVERITY-Returns information about errors that DBA users can correct in the event of informational errors
• ERROR_LINE – returns the line number in which the error occurred.
• ERROR_PROCEDURE – Returns the name of the stored procedure or function.
• ERROR_MESSAGE – returns the most important information and is the text of the error message.

That’s all you need to troubleshoot SQL Server. You can do it all with simple TRY and CATCH statements. The only part that can be tricky is handling the transaction. Why? This is because if there is a BEGIN TRANSACTION it should always end with a COMMIT or ROLLBACK transaction. The problem is when I get an error after starting and before committing or before rolling back. In this particular case, a special function can be used in the CATCH statement that can be used to check whether a transaction is in a committed state or not. This way you can decide to rollback or commit.

Let’s go to the SQL Server Management Studio (SSMS) and start with the basics of SQL Server error handling. The AdventureWorks 2014 sample database is used throughout the article. The following script is very simple.

USE AdventureWorks2014
GO
— Basic example of TRY…CATCH

BEGIN TRY
— Generate a divide-by-zero error
SELECT
1 / 0 AS Error;
END TRY
BEGIN CATCH
SELECT
ERROR_NUMBER() AS ErrorNumber,
ERROR_STATE() AS ErrorState,
ERROR_SEVERITY() AS ErrorSeverity,
ERROR_PROCEDURE() AS ErrorProcedure,
ERROR_LINE() AS ErrorLine,
ERROR_MESSAGE() AS ErrorMessage;
END CATCH;
GO

This is an example of how it looks and works. The only thing BEGIN TRY does is divide 1 by 0. Of course you get a mistake. Once you get to that block of code, it transfers control to the CATCH block and then selects all of the attributes using the intrinsic functions mentioned earlier. When you run the above script, you will get the following result:

Two SELECT statements resulted in two result grids. The first is a transmitted control that divides 1 by 0 to cause an error, and the second is actually a transmitted control that gives some result. From left to right are ErrorNumber, ErrorState, ErrorSeverity. In this case there is no procedure (NULL), ErrorLine and ErrorMessage.

Now let’s do something more meaningful. It’s a smart idea to keep track of these bugs. Nevertheless, anything that is prone to errors should be recorded and at least recorded. You can also get a bit of creativity to create triggers on these logged tables, set up email accounts and notify people when errors occur.

If you are not familiar with database e-mail, see this article for more information on e-mail systems. How to configure database e-mail in SQL Server

The following script creates a table called DB_Errors that can be used to store trace data.

— Table to record errors

CREATE TABLE DB_Errors
(ErrorID INT IDENTITY(1, 1),
UserName VARCHAR(100),
ErrorNumber INT,
ErrorState INT,
ErrorSeverity INT,
ErrorLine INT,
ErrorProcedure VARCHAR(MAX),
ErrorMessage VARCHAR(MAX),
ErrorDateTime DATETIME)
GO

Here is a simple identifying column and username so I know who generated the error and the rest is the exact information from the built-in functions I listed earlier.

Now let’s change the custom stored procedure in the database and put the error handler here.

ALTER PROCEDURE dbo.AddSale @employeeid INT,
@productid INT,
@quantity SMALLINT,
@saleid UNIQUEIDENTIFIER OUTPUT
AS
SET @saleid = NEWID()
BEGIN TRY
INSERT INTO Sales.Sales
SELECT
@saleid,
@productid,
@employeeid,
@quantity
END TRY
BEGIN CATCH
INSERT INTO dbo.DB_Errors
VALUES
(SUSER_SNAME(),
ERROR_NUMBER(),
ERROR_STATE(),
ERROR_SEVERITY(),
ERROR_LINE(),
ERROR_PROCEDURE(),
ERROR_MESSAGE(),
GETDATE());
END CATCH
GO

In this case, changing this stored procedure wraps the error handling around the only statement in the stored procedure. When I call this stored procedure and pass in valid data, the following occurs:

The Quick Select statement indicates that the record was inserted successfully.

However, if I call the above stored procedure again and pass the same parameters, the resulting grid will be filled differently.

This time, there are two metrics in the resulting grid.

0 rows affected – This row indicates that the sales table doesn’t really contain anything.

1 row affected – this line indicates that something has been entered into the newly created logging table.

So here you can look at the error table and see what happened. A simple statement Select does the job:

• Don’t miss: Easy Tutorial How to Make HTML Forms using PHP Programming Language

Here is all the information you have previously configured to connect. Only this time I filled in the procedure field and of course I also got a “friendly” technical message from SQL Server saying there was a violation.

Although this is a very artificial example, the fact is that having the wrong date is very common in the real world. For example, if you have a foreign key defined between the Sales table and the Employee table, you pass an employee ID that does not exist. In other words, you need to ask Employee to create a new record in the Sales table. This use case causes a foreign key constraint violation.

The general idea behind this is not to get rid of the error. We want to at least signal the individual that something is wrong and register it inside. If you actually had an application that relies on stored procedures, the developer would have known when the error occurred, so I would have coded SQL Server error handling somewhere. Also, it might be a good idea to return the error to the user / app. This can be done by adding the RAISERROR function, so that you can generate your own version of the error.

For example, if you know that you are more likely to enter an employee ID that does not exist, you can perform a search. This search can check if an employee ID exists and if it doesn’t, it can show the exact error that occurred. Or, in the worst case scenario, if you get an unexpected error and have no idea what it is, you can reset the error.