TRY_CAST And TRY_CONVERT Can Still Throw Errors In SQL Server

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 on 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.



8 thoughts on “TRY_CAST And TRY_CONVERT Can Still Throw Errors In SQL Server

  1. 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…

      1. 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?

  2. Even more surprising, when you realise that this is completely valid:
    SELECT CAST(CAST(1 AS VARBINARY(16)) AS UNIQUEIDENTIFIER)

Leave a Reply

Your email address will not be published.