Which Transact-SQL segment should you use for each requirement?

HOTSPOT

You need to develop a Transact-SQL statement that meets the following requirements:

– The statement must return a custom error when there are problems updating a table.

– The error number must be value50555.

– The error severity level must be14.

– A Microsoft SQL Server alert must be triggered when the error condition occurs.

Which Transact-SQL segment should you use for each requirement? To answer, select the appropriate Transact-SQL segments in the answer area.

View Answer

Answer:

Explanation:

Box 1: TRY…CATCH

The TRY…CATCH Transact-SQL construct implements error handling for Transact-SQL that is similar to the exception handling in the Microsoft Visual C# and Microsoft Visual C++ languages. A group of Transact-SQL statements can be enclosed in a TRY block. If an error occurs in the TRY block, control is passed to another group of statements that is enclosed in a CATCH block.

Box 2: RAISERROR (50555, 14, 1 ‘The update failed.") WITH LOG

We must use RAISERROR to be able to specify the required severity level of 14, and we should also use the LOG option, which Logs the error in the error log and the application log for the instance of the Microsoft SQL Server Database Engine, as this enable a MS MS SQL SERVER alert to be triggered.

Note: 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.

Incorrect Answers:

Not THROW: THROW does not have a severity parameter.

References:

https://msdn.microsoft.com/en-us/library/ms175976.aspx

https://msdn.microsoft.com/en-us/library/ms178592.aspx