Gopal Biswas Gopal Biswas - 1 year ago 73
SQL Question

Conversion failed error is showing for uniqueidentifier in SQL query

I have a SQL Server table which contains a column

of type
and the column value is

When I'm quering like this:

WHERE userid = '9EBC02CE-FA3A-4A62-A3B7-B9B6CFD33B7E'

The query is running successfully. If I add extra characters at the end of the string it is also working fine like below

WHERE userid = '9EBC02CE-FA3A-4A62-A3B7-B9B6CFD33B7Eqweqweqwemmmmmmmmmm'

But the problem is when I'm adding extra characters at the start of the string the query is showing error.

WHERE userid = 'A9EBC02CE-FA3A-4A62-A3B7-B9B6CFD33B7E'

The error showing like

Conversion failed when converting from a character string to uniqueidentifier

My question is why the error is showing for only adding character at the start of the string and how to track this error inside a stored procedure

Answer Source

According to Microsoft documentation:

The uniqueidentifier type is considered a character type for the purposes of conversion from a character expression, and therefore is subject to the truncation rules for converting to a character type. That is, when character expressions are converted to a character data type of a different size, values that are too long for the new data type are truncated. See the Examples section.

That explains why it works fine if you append characters after the 36th position.

When you prepend characters to the guid, you are breaking the formatting rules for the guid and then the conversion fails.

In a stored procedure you can validate the guid by using TRY_CONVERT. It will return NULL if the conversion is not possible:

      .... report error ...

TRY_CONVERT is only available from SQL Server 2012. If you need to validate a string before conversion to UNIQUEIDENTIFIER on older versions, you can use the following code:

IF NOT @userId LIKE REPLACE('00000000-0000-0000-0000-000000000000', '0', '[0-9a-fA-F]')+'%'
          .... report error ...
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download