Error handling in SQL Server breaks down into two very distinct situations: you’re handling errors in SQL Server 2000 or you’re handling errors in SQL Server 2005 onwards.
.Where to Handle Error? – There are some scenarios like, we are expecting some rows should come when we will execute the store procedure, but unfortunately SP returns none of them. Below points can be some possible scenarios where we can use error handling: While executing some DML Statement like INSERT, DELETE, UPDATE we can handle the error for checking proper output or If transaction fails, then we need to rollback – This can be done by error handling or While using Cursor in SQL Server.
– The two most common mechanisms for error handling in SQL Server 2005 are: @@ERROR and TRY-CATCH Block (SQL 2005 onwards)
•Using @@Error: @@Error is a Global Variable in SQL Server. This variable automatically populates the error message when a certain error occurred in any statement. But we have to trace it within just after the next line where the actual error occurred, otherwise, it will reset to 0. General syntax for @@ERROR is as follows: Select @@ERROR. Return integer value. Use @@ERROR with @@ROWCOUNT to validate the operation of an UPDATE statement. The value of @@ERROR is checked for any indication of an error, and @@ROWCOUNT is used to ensure that the update was successfully applied to a row in the table.
•TRY-CATCH Block: This is generally used where want to trap or catch error for multiple SQL statements like or a SQL Block of statement.
TRY…CATCH blocks are the standard approach to exception handling in modern programming languages (c#, VB.NET). Use and syntax are likely same as normal programming language. Like Exception Handling in Programming Language, we can use nested Try-Catch block in SQL Server also. A TRY…CATCH construct catches all execution errors that have a severity higher than 10 that do not close database connection.
.Try block will catch the error and will throw it in the Catch block. Catch block then handles the scenario.
.SQL Server provides the following System function that we can use inside our Catch-block for retrieving the details of the error:
•ERROR_MESSAGE(): Returns the complete description of the error message. Returns NULL if called outside the scope of a CATCH block.
•ERROR_NUMBER(): Returns the number of the error message that caused the CATCH block to be run.
•ERROR_SEVERITY(): Returns the Severity of error message. When an error is raised by the SQL Server Database Engine, the severity of the error indicates the type of problem encountered by SQL Server.
.Severity level – a number from 0 to 24. If the severity level is in the range 0-10, the message is informational or a warning, and not an error. Errors resulting from programming errors in your SQL code have a severity level in the range 11-16 (Indicate errors that can be corrected by the user). Severity 12 is not used. Severity levels 17-29 (Indicate software errors that cannot be corrected by the user) indicate resource problems, hardware problems or internal problems in SQL Server, and a severity of 20 or higher is fatal, the connection will be terminated.
•ERROR_STATE(): Returns the error state number. A value between 0 and 127. The meaning of this item is specific to the error messages. Microsoft has not documented these values. When viewing databases of known issues, such as the Microsoft Knowledge Base, you can use the state number to determine if the recorded issue might be the same as the error you have encountered. For example, if a Knowledge Base article discusses an 1105 error message with a state of 2, and the 1105 error message you received had a state of 3, your error probably had a different cause than the one reported in the article. A SQL Server support engineer can also use the state code from an error to find the location in the source code where that error is being raised, which may provide additional ideas on how to diagnose the problem.
•ERROR_PROCEDURE(): Returns the name of the procedure, trigger or user-defined function where the error occurred. Blank if the error occurred in a plain batch of SQL statements (including dynamic SQL).
•ERROR_LINE(): Returns the line number that caused the error. This function may be called anywhere within the scope of a CATCH block.
-Get all message detail by using this query: SELECT * FROM master.dbo.sysmessages
•RAISERROR: Generates an error message and initiates error processing for the session. RAISERROR can either reference a user-defined
message stored in the sys.messages catalog view or build a message dynamically. The message is returned as a server error message to the calling application or to an associated CATCH block of a TRY…CATCH construct. RAISERROR (‘You made a HUGE mistake’, 10, 1).A user-defined error message number stored in the sys.messages catalog view using sp_addmessage. Error numbers for user-defined error messages should be greater than 50000. When msg_id is not specified, RAISERROR raises an error message with an error number of 50000.
. The errors generated by RAISERROR operate the same as errors generated by the Database Engine code. The values specified by RAISERROR are reported by the ERROR_LINE, ERROR_MESSAGE, ERROR_NUMBER, ERROR_PROCEDURE, ERROR_SEVERITY, ERROR_STATE, and @@ERROR system functions. When RAISERROR is run with a severity of 11 or higher in a TRY block, it transfers control to the associated CATCH block. The error is returned to the caller if RAISERROR is run: Outside the scope of any TRY block or With a severity of 10 or lower in a TRY block or With a
severity of 20 or higher that terminates the database connection.
•FORMATMESSAGE: Constructs a message from an existing message in sys.messages. The functionality of FORMATMESSAGE resembles that of RAISERROR statement. RAISERROR prints message immediately, while FORMATMESSAGE returns formatted message for further processing.
•XACT_STATE: Is a scalar function that reports the user transaction state of a current running request. XACT_STATE indicates whether the request has an active user transaction, and whether the transaction is capable of being committed. Return value can be 1,0 and -1. Both the XACT_STATE and @@TRANCOUNT functions can be used to detect whether the current request has an active user transaction. @@TRANCOUNT cannot be used to determine whether that transaction has been classified as an uncommittable transaction. XACT_STATE cannot be used to determine whether there are nested transactions.