3

I have a SQL Server table which contains a column userid of type uniqueidentifier and the column value is 9EBC02CE-FA3A-4A62-A3B7-B9B6CFD33B7E.

When I'm querying 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

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Gopal Biswas
  • 409
  • 1
  • 7
  • 34
  • 1
    This [answer](http://stackoverflow.com/a/31876166/2451726) may help you! – Arulkumar Jul 28 '16 at 05:51
  • This one as well along with comments ...http://stackoverflow.com/questions/1390109/convert-varchar-to-uniqueidentifier-in-sql-server – TheGameiswar Jul 28 '16 at 05:54
  • 4
    If you stop passing things around as strings, I suspect everything will be a lot easier. Your stored procedure is accepting a *string* when what it wants is a uniqueidentifier. So why not make that the parameter type? – Damien_The_Unbeliever Jul 28 '16 at 05:54
  • I have a local variable of uniqueidentifier datatype in my stored procedure but in case the front end passed value to stored procedure like 'A9EBC02CE-FA3A-4A62-A3B7-B9B6CFD33B7E' which contains an extra character of "A" then how to track the query error @Damien_The_Unbeliever – Gopal Biswas Jul 28 '16 at 06:11
  • There might be valid reasons for receiving the uniqueidentifier as string, for example if it originates from a JSON or XML file. Then the conversion and validation has to happen somewhere... – tomislav_t Jul 28 '16 at 06:11
  • If I need to validate the uniqueidentifier value in StoredProcedure then what is the process? I cannot check the value from front end.The TRY_CONVERT is not working in my SQL version. @ tomislav_t – Gopal Biswas Jul 28 '16 at 06:31

1 Answers1

4

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:

IF TRY_CONVERT(UNIQUEIDENTIFIER,@userId) IS NULL
   BEGIN
      .... report error ...
   END

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]')+'%'
    BEGIN
          .... report error ...
    END
tomislav_t
  • 527
  • 2
  • 9