SQL Server: TRY CATCH

TRY...CATCH construct can be used to catch execution errors with a severity level higher than 10, as long as the error does not end the database connection. Transact-SQL code that might generate an error is included in the TRY block. If an error with severity level greater than 10 that does not end the database connection occurs in the TRY block, control is transferred to the CATCH block. The CATCH block can contain Transact- SQL code to handle the error that occurred. With a TRY...CATCH construct, DBA/Developer can also use functions to retrieve additional information about an error, such as ERROR_LINE, ERROR_MESSAGE, ERROR_PROCEDURE, and ERROR_SEVERITY. The RAISERROR statement returns a user-defined error message and sets a flag to indicate that an error occurred. The full syntax of the RAISERROR statement is as follows:

RAISERROR ({msg_id | msg_str | @local_variable}

{, severity, state}[, argument [,...n]])
[WITH [LOG]
[NOWAIT]
[SETERROR]];

The RAISERROR statement can return specific error message text or a custom error message that has been stored in the sysmessages table. To return an error message stored in sysmessages, DBA/Developer can pass the RAISERROR statement the error's message_id as the first parameter. In this scenario, DBA/Developer could include each DML statement within DBA/Developerr stored procedures in a TRY block, and use the RAISERROR statement within the associated CATCH block to return the desired information about the error. DBA/Developer could use the ERROR_PROCEDURE function in the CATCH block to return the name of the procedure that generated the error and the ERROR_SEVERITY function to return the error's severity level. DBA/Developer should not implement an output parameter in each stored procedure to return the error message number. In this scenario, some of DBA/Developerr stored procedures execute multiple DML statements. Therefore, returning information for a single error is not sufficient. DBA/Developer should not check the value of @@ERROR after each stored procedure call because the @@ERROR function returns the error number of only the most recently executed Transact-SQL statement. The @@ERROR function returns a value of zero if the most recently executed Transact-SQL statement ran without errors. If an error occurred that corresponds to an error stored in the sysmessages table, the function returns the message_id for the error. The value of @@ERROR is reset each time a new Transact-SQL statement is executed. Therefore, DBA/Developer should call the function immediately following the Transact-SQL statement for which DBA/Developer want to inspect the error number. DBA/Developer should not add a custom error message for each stored procedure to the sysmessages table using the sp_addmessage system stored procedure. The sp_addmessage system stored procedure creates a custom error message and stores the new message in the sysmessages table in the master database. After a user- defined error message is created with sp_addmessage, Transact-SQL constructs or applications can reference it using the RAISERROR statement. In this scenario, adding custom error messages with the sp_addmessage system stored procedure would define custom error message, but not ensure these message were returned by the stored procedures. DBA/Developer would still need to add code within the stored procedures to implement error handling.