It Was Written
I was a bit surprised by this, because I thought the whole point of these new functions was to avoid errors like this.
SELECT
oops = TRY_CONVERT(uniqueidentifier, 1);
GO
SELECT
oops = TRY_CAST(1 AS uniqueidentifier);
GO
Both of these selects will throw the same error message:
Msg 529, Level 16, State 2, Line 2
Explicit conversion from data type int to uniqueidentifier is not allowed.
Which, you know, fine. I get that limitation of an explicit cast or convert, but why not just throw a NULL like other cases where the expression isn’t successful?
Bummer.
Thanks for reading!
Going Further
If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that and need to solve performance problems quickly.
Intuiting “creator’s intent” (or “Creator’s intent”, depending on your particular affiliations and beliefs), the TRY_X functions are for things that may or may not work. For example, varchar could contain integer data. So TRY_CAST(@questionable AS INT) makes sense. But when there is no path from the source DT to the target DT, it seems helpful to shortcut the whole process and say “rather than go through a bunch of potentially expensive work, this will always return NULL. so let’s save ourselves some time and effort”.
I am curious about your use case though…
A sql_variant column 😃
Interesting. Isn’t the point of sql_variant that it is prototypical and as such should be able to be converted to most other DTs?
SELECTregistry_key,
value_name,
value_data,
tc =
TRY_CONVERT
(
uniqueidentifier,
value_data
)
FROM sys.dm_server_registry;
Yeah it seems like it should return nulls on that query, not error.
Even more surprising, when you realise that this is completely valid:
SELECT CAST(CAST(1 AS VARBINARY(16)) AS UNIQUEIDENTIFIER)
SELECT CAST(CAST(” AS VARBINARY(16)) AS UNIQUEIDENTIFIER);
😃