Handling data-tier errors with SQL Server

Developers are trying to handle surprises properly so users don't have to worry about the Bible-like system error messages. For this reason, exception handling is each. A standard part of a NET application. The Try/catch block allows you to catch exceptions and control the execution of the application from that point. Many errors occur when interacting with a database, but many developers do not know how to handle database-level errors. In this article we will explore how to use SQL Server and T-SQL to handle errors in your database code.

Dealing with errors in T-SQL

SQL Server dialog allows you to easily handle non-fatal errors that may occur in stored processes and functions, but not all errors are easy to handle. In fact, there are many fatal and non-fatal errors. What is fatal and what is Non-fatal is not well documented, but your application code can always use Try/catch blocks to handle fatal database errors. For all other errors, you can use the following techniques.

Transaction

You should use transactions in your database code to ensure that all changes are completed when everything is fine. SQL Server's online Help describes a transaction as a logical unit of a task, consisting of a series of statements (selection, inserts, updates, or deletions). If no errors occur during the transaction, all changes to the transaction become a permanent part of the database. If an error occurs during this time, no modifications are made to the database.

Transactions are contained between the BEGIN TRANSACTION and end transaction statements. The ROLLBACK transaction statement can cancel all changes, so no changes will occur. You can make permanent changes with the COMMIT TRANSACTION statement. Now, let's focus on how to deal with the mistakes in T-SQL.

@ @Error

@ @Error function allows you to implement T-SQL error handling. It returns the error code returned by the system. Returns "0" if no error occurs. The @ @Error function must be called immediately after a statement because it is purged after each T-SQL statement.