SQL Server TRY_CONVERT returns an error instead of NULL

Can this be happened? The functions TRY_CONVERT and TRY_CAST have been designed for handling errors and they should return NULL when there is an error.

But, it is not always true.

What is the reason? Although it returns NULL when an error occurred with the conversion, it does not happen if the conversion is not a permittable. The above code tries to convert an integer into an unique identifier that is not permitted, hence it fails with an error. 

Result shows asterisk when converting int to varchar

I was sent a code by one engineer saying they he gets an odd error with one of calculations written. He gets the following error;

Conversion failed when converting the varchar value '* ' to data type int.

By looking at the error, I realized the issue, it is related to truncation. He converts a numeric value to a varchar variable and then again he uses the converted varchar value with another calculation, converting to back to int. This calculation causes the error because previously converted value contains asterisk, not the numeric value that was used with initial conversion.

How can it be happened? This happens when you convert character or binary expressions to an expression of a different data type. If the the result cannot be accommadate with the set data type, SQL Server either truncates the value, partially displays or throw an error.

See this code;

DECLARE @x int = 1000;
DECLARE @y char(2);

SET @y = @x;
SELECT @y;

When the value 1000 is converted to char(2), the result becomes asterisk because the set data type cannot hold 1000. The asterisk indicates that value is truncated.

Here are some known results when converting to an expression of a different data type.

TRY Counterparts for CAST, CONVERT and PARSE

For implementing explicit conversion from one type to another, we have been using either CAST or CONVERT functions given by Microsoft SQL Server. CAST is the standard ANSI way of converting and CONVERT is proprietary to Microsoft for converting. SQL Server 2012 introduces another function for converting string to date, time, and number, that accepts an additional argument for culture setting.

In addition to these three functions, CAST, CONVERT and PARSE have their counterparts called TRY_CAST, TRY_CONVERT, and TRY_PARSE. The different between them is, new functions return NULL for conversions with inconvertible input types instead of throwing errors.

Here is an example code;

1. DECLARE @input varchar(10) = '1000'
2.  
3. -- these three SELECTs
4. -- return values without
5. -- any issue
6. SELECT CAST(@input AS money)
7. SELECT CONVERT(money, @input)
8. SELECT PARSE(@input as money)
9.  
10. -- change the value
11. SET @input = 'Thousand'
12.  
13. -- these three SELECTs
14. -- throw errors
15. SELECT CAST(@input AS money)
16. SELECT CONVERT(money, @input)
17. SELECT PARSE(@input as money)
18.  
19. -- these three SELECTs
20. -- return NULLs
21. SELECT TRY_CAST(@input AS money)
22. SELECT TRY_CONVERT(money, @input)
23. SELECT TRY_PARSE(@input as money)

What should we use? It depends on the way you need to handle it. If you need to continue the execution without any error, TRY_* is the best. However if the execution should be stopped with inconvertible data types, traditional conversion would be the best.