从视图中将SQL从varchar转换为uniqueidentifier失败 [英] SQL conversion from varchar to uniqueidentifier fails in view

问题描述

我遇到以下情况。
我有一个数据库，其中包含一个包含客户数据的表和一个表，在该表中放置记录以监视B2B站点上发生的情况。

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.

客户表如下：

• ID，整数，不为空


• GUID，唯一标识符，不为null，主键


• 其他内容...

监视表：

• ID，整数，不为空


• USERGUID，uniqueidentifier，空


• PARAMETER2，varchar（50），空


• 其他内容...

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

在PARAMETER1中，客户指导与其他数据类型一样存储。

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.

我正在使用Entity Framework，但在比较字符串时遇到问题和guid类型，所以我决定在监视表的顶部进行查看：

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_%')

我在EF中导入了视图并进行了Linq查询。它什么也没返回，所以我提取了生成的SQL查询。在SQL Management Studio中测试查询时，出现以下错误：
从字符串转换为uniqueidentifier时转换失败。

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试图制作一个SQL提琴，但是它正在该站点上运行。 htt p：//sqlfiddle.com/#！3 / 66d68 / 3

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?

推荐答案

使用

TRY_CONVERT(UNIQUEIDENTIFIER, parameter2) AS customerguid

而不是

 CONVERT(UNIQUEIDENTIFIER, parameter2) AS customerguid

视图已内联到查询中并且 CONVERT 可以在之前运行。

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

有关其他讨论，请参见 SQL Server不应引发不合逻辑的错误

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

这篇关于从视图中将SQL从varchar转换为uniqueidentifier失败的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！