sql server - SQL conversion from varchar to uniqueidentifier fails in view

Question

I'm stuck on the following scenario. I have a database with a table with customer data and a table where I put records for monitoring what is happening on our B2B site.

The customer table is as follow:

• ID, int, not null
• GUID, uniqueidentfier, not null, primary key
• Other stuff...

The monitoring table:

• ID, int, not null
• USERGUID, uniqueidentifier, null
• PARAMETER2, varchar(50), null
• Other stuff...

In PARAMETER1 are customer guids as wel as other data types stored.

Now the question came to order our customers according their last visit date, the most recent visited customers must come on the top of a grid.

I'm using Entity Framework and I had problems of comparing the string and the guid type, so I decided to make a view on top of my monitoring table:

SELECT        
   ID, 
   CONVERT(uniqueidentifier, parameter2) AS customerguid, 
   USERguid, 
   CreationDate
FROM            
   MONITORING
WHERE        
   (dbo.isuniqueidentifier(parameter2) = 1) 
   AND 
   (parameter1 LIKE 'Customers_%' OR parameter1 LIKE 'Customer_%')

I imported the view in EF and made my Linq query. It returned nothing, so I extracted the generated SQL query. When testing the query in SQL Management Studio I got the following error: Conversion failed when converting from a character string to uniqueidentifier.

The problem lies in the following snippet (simplified for this question, but also gives an error:

SELECT *,
    (
        SELECT 
            [v_LastViewDateCustomer].[customerguid] AS [customerguid]
        FROM [dbo].[v_LastViewDateCustomer] AS [v_LastViewDateCustomer]
        WHERE c.GUID = [v_LastViewDateCustomer].[customerguid]
    )

FROM CM_CUSTOMER c

But when I do a join, I get my results:

SELECT *
FROM CM_CUSTOMER c
    LEFT JOIN
    [v_LastViewDateCustomer] v
on c.GUID = v.customerguid

I tried to make a SQL fiddle, but it is working on that site. http://sqlfiddle.com/#!3/66d68/3

Anyone who can point me in the right direction?

Answer 1

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