Querying Microsoft SQL Server 2012/2014 — Question 12

You use Microsoft SQL Server 2012 to develop a database application.
Your application sends data to an NVARCHAR(MAX) variable named @var.
You need to write a Transact-SQL statement that will find out the success of a cast to a decimal (36,9).
Which code segment should you use?

Answer options

• A. BEGIN TRY SELECT convert (decimal(36,9), @var) as Value, 'True' As BadCast END TRY BEGIN CATCH SELECT convert (decimal(36,9), @var) as Value, 'False' As BadCast END CATCH
• B. TRY( SELECT convert (decimal(36,9), @var) SELECT 'True' As BadCast ) CATCH( SELECT 'False' As BadCast )
• C. SELECT CASE WHEN convert (decimal(36,9), @var) IS NULL THEN 'True' ELSE 'False' END AS BadCast
• D. SELECT IIF(TRY_PARSE(@var AS decimal(36,9)) IS NULL, 'True', 'False' )

Correct answer: D

Explanation

The correct answer is D because TRY_PARSE is designed to attempt a conversion and returns NULL if it fails, making it ideal for this situation. Option A incorrectly uses a TRY...CATCH block that does not directly address the conversion success check, while B is syntactically incorrect. Option C uses a CASE statement which does not appropriately handle the error in conversion as it doesn’t utilize a TRY...CATCH mechanism.