You are the database designer for a manufacturing company on an instance of SQL Server 2008. You are creating several stored procedures that will update tables in your database.
You want each stored procedure to return a custom error message to the user if any of the DML statements in the stored procedure fail. Each custom error message must include the name of the procedure in which the error occurred and the error’s severity level.
Which action should you take to implement error handling in your stored procedures?
A. Implement an output parameter in each stored procedure to return the error message number.
B. Include a TRY…CATCH construct in each stored procedure and use the RAISERROR statement within each CATCH block.
C. Check the value of @@ERROR after each stored procedure call.
D. Add a custom error message for each stored procedure to the sysmessages table using the sp_addmessage system stored procedure.
Correct Answer: B
Explanation/Reference:
A 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, you 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, you can pass the RAISERROR statement the error’s message_id as the first parameter. In this scenario, you could include each DML statement within your stored procedures in a TRY block, and use the RAISERROR statement within the associated CATCH block to return the desired information about the error. You 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.
You should not implement an output parameter in each stored procedure to return the error message number. In this scenario, some of your stored procedures execute multiple DML statements. Therefore, returning information for a single error is not sufficient.
You 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, you should call the function immediately following the Transact-SQL statement for which you want to inspect the error number.
You 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. You would still need to add code within the stored procedures to implement error handling.
