JurgenStillaert JurgenStillaert - 4 months ago 17
SQL Question

SQL conversion from varchar to uniqueidentifier fails in view

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

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

Comments