SQL Conversion from Varchar to Uniqueidentifier Fails in View

SQL conversion from varchar to uniqueidentifier fails in view

Use

TRY_CONVERT(UNIQUEIDENTIFIER, parameter2) AS customerguid

instead of

 CONVERT(UNIQUEIDENTIFIER, parameter2) AS customerguid

Views are inlined into the query and the CONVERT can run before the WHERE.

For some additional discussion see SQL Server should not raise illogical errors

Conversion failed when converting from a character string to uniqueidentifier - Two GUIDs

The problem was that the ID column wasn't getting any value. I saw on @Martin Smith SQL Fiddle that he declared the ID column with DEFAULT newid and I didn't..

Conversion failed error is showing for uniqueidentifier in SQL query

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

Conversion failed when converting from a character string to uniqueidentifier in TSQL

It looks like I needed to do a better job matching the appkey. It was grabbing extra data on a few of the compares adding quotes and extra characters which are not valid in a GUID.

Convert varchar to uniqueidentifier in SQL Server

DECLARE @uuid VARCHAR(50)
SET @uuid = 'a89b1acd95016ae6b9c8aabb07da2010'
SELECT  CAST(
        SUBSTRING(@uuid, 1, 8) + '-' + SUBSTRING(@uuid, 9, 4) + '-' + SUBSTRING(@uuid, 13, 4) + '-' +
        SUBSTRING(@uuid, 17, 4) + '-' + SUBSTRING(@uuid, 21, 12)
        AS UNIQUEIDENTIFIER)

SQL Server: converting UniqueIdentifier to string in a case statement

I think I found the answer:

convert(nvarchar(36), RequestID)

Here's the link where I found this info:

http://msdn.microsoft.com/en-us/library/ms187928.aspx

---

Related Topics