A custom error or exception that occurs during the execution of an SQL query is known as an error condition. Exception handling is the mechanism used to resolve error conditions that occur during program execution. SQL Server includes TRY and CATCH blocks for handling exceptions. By using the THROW block, users can also create their own error conditions.
Syntax:
BEGIN TRY
/*QUERY*/
END TRY
BEGIN CATCH
-Print error OR
-Rollback Transaction
END CATCH
Types of SQL Server Exceptions
Retrieving Error Information
When executing a CATCH block, the following system functions can be utilized to retrieve information about the error that caused the block to be executed:
ERROR_NUMBER() returns the error number.
ERROR_SEVERITY() returns the severity of the error.
ERROR_STATE() returns the error state number.
ERROR_PROCEDURE() returns the name of the stored procedure or trigger where the error occurred.
ERROR_LINE() returns the line number inside the routine that caused the error.
ERROR_MESSAGE() returns the complete text of the error message, including the values of any substitutable parameters (such as object names, lengths, or times). • If any of these functions return NULL in the result set, then they are considered to be outside the scope of the CATCH block.
System Defined Error
In the System Defined Exception the errors are generated by the system.
Syntax:
BEGIN TRY
–QUERY
END TRY
BEGIN CATCH
-Print error OR
-Rollback Transaction
END CATCH
Example1:
DECLARE @num1 INT;
DECLARE @num2 INT;
BEGIN TRY
SET @num1=1;
SET @num2=@num1/0; —ERROR OCCUR—-\
PRINT ‘Hello’;
PRINT ‘Hi World’;
PRINT ‘Hi People’;
PRINT ‘Hi Man’;
END TRY
BEGIN CATCH
PRINT ‘Catch Block Information’;
PRINT CONCAT(‘Error Message=’,ERROR_MESSAGE());
PRINT CONCAT(‘Error Line=’,ERROR_LINE());
PRINT CONCAT(‘Error Number=’,ERROR_NUMBER());
PRINT CONCAT(‘Error Severity=’,ERROR_SEVERITY());
PRINT CONCAT(‘Error State=’,ERROR_STATE());
PRINT CONCAT(‘Error Procedure=’,ERROR_PROCEDURE());
END CATCH
Example2:(With changing error line in syntax)
DECLARE @num1 INT;
DECLARE @num2 INT;
BEGIN TRY
SET @num1=1;
PRINT ‘Hello’;
PRINT ‘Hi World’;
PRINT ‘Hi People’;
SET @num2=@num1/0; —ERROR OCCUR—-\
PRINT ‘Hi Man’;
END TRY
BEGIN CATCH
PRINT ‘Catch Block Information’;
PRINT CONCAT(‘Error Message=’,ERROR_MESSAGE());
PRINT CONCAT(‘Error Line=’,ERROR_LINE());
PRINT CONCAT(‘Error Number=’,ERROR_NUMBER());
PRINT CONCAT(‘Error Severity=’,ERROR_SEVERITY());
PRINT CONCAT(‘Error State=’,ERROR_STATE());
PRINT CONCAT(‘Error Procedure=’,ERROR_PROCEDURE());
END CATCH
User Defined Errors
SQL Server allows for user-defined errors to be created using the THROW statement, which generates an exception and transfers control to a CATCH block. • The syntax for using THROW is as follows: THROW @errorNumber, @errorMessage, @errorSeverity • The @errorNumber parameter can be any value between 50000 and 2147483647. • The @errorSeverity parameter can be set to:
- 13, indicating transaction deadlock errors
- 14, indicating security-related errors (e.g., permission denied)
- 15, indicating syntax errors in the Transact-SQL command
- 16, indicating a general error that can be corrected by the user
Example:
DECLARE @NumB1 INT;
BEGIN TRY
SET @Num1=65;
PRINT ‘HELLO WORLD’;
PRINT ‘HI PEOPLE’;
IF @NumB1 > 40
THROW 50000,’Age is greater than 30′,16;
PRINT ‘HI MAN’;
PRINT ‘GOOD MORNING’;
END TRY
BEGIN CATCH
PRINT ‘Catch Block’
PRINT CONCAT(‘ERROR MESSAGE=’,ERROR_MESSAGE());
PRINT CONCAT(‘ERROR LINE=’,ERROR_LINE());
PRINT CONCAT(‘ERROR NUMBER=’,ERROR_NUMBER());
PRINT CONCAT(‘ERROR SEVERITY=’,ERROR_SEVERITY());
PRINT CONCAT(‘ERROR STATE=’,ERROR_STATE());
PRINT CONCAT(‘ERROR PROCEDURE=’,ERROR_PROCEDURE());
END CATCH
- SQL is a powerful language for managing data and interacting with relational databases. However, errors can occur when working with SQL that can affect the functionality of your code. To ensure that your code runs smoothly, it is essential to implement error handling techniques in SQL.
- Error handling in SQL involves identifying and responding to errors that occur during the execution of SQL statements. These errors may arise due to syntax errors, invalid data, or database connection issues, among others. By incorporating error handling into your SQL code, you can ensure that your application continues to run even when errors occur.
Here are some best practices for SQL error handling:
Use TRY-CATCH blocks:
- TRY-CATCH blocks are a powerful mechanism for handling errors in SQL. They allow you to specify the code to execute when an error occurs and handle the error gracefully. With TRY-CATCH blocks, you can catch errors and take appropriate action to recover from them.
Use RAISERROR:
- RAISERROR is a function in SQL that allows you to generate custom error messages. You can use it to raise an error with a custom message and severity level. RAISERROR is a useful function for providing meaningful error messages to your users or for logging purposes.
Use @@ERROR:
- @@ERROR is a system function in SQL that returns the error number of the last executed statement. You can use this function to check for errors after executing a statement and take appropriate action based on the error code.
Use Transactions:
- Transactions are a powerful mechanism for managing errors in SQL. By using transactions, you can group a set of SQL statements into a single transaction and roll back the transaction if an error occurs. This can help you maintain data consistency and prevent data loss.
Implement logging:
- Logging is an essential part of error handling in SQL. By implementing logging, you can capture details about errors that occur in your code and use that information to improve your application. You can log errors to a file or a database table, and include details such as the error message, severity level, and time of occurrence.
Conclusion
In conclusion, error handling is an essential aspect of SQL development. By following best practices such as using TRY-CATCH blocks, RAISERROR, @@ERROR, and transactions, and implementing logging, you can create more robust and reliable SQL applications that can handle errors gracefully.