从 varchar 到 uniqueidentifier 的 SQL 转换在视图中失败
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/20474041/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL conversion from varchar to uniqueidentifier fails in view
提问by CyclingFreak
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.
我被困在以下场景中。我有一个数据库，其中包含一个包含客户数据的表和一个表，我在其中放置了用于监控 B2B 站点上发生的事情的记录。
The customer table is as follow:
客户表如下：
- ID, int, not null
- GUID, uniqueidentfier, not null, primary key
- Other stuff...
- ID，整数，不为空
- GUID，uniqueidentfier，非空，主键
- 其他的东西...
The monitoring table:
监控表：
- ID, int, not null
- USERGUID, uniqueidentifier, null
- PARAMETER2, varchar(50), null
- Other stuff...
- ID，整数，不为空
- USERGUID，唯一标识符，空
- 参数 2，varchar(50)，空
- 其他的东西...
In PARAMETER1 are customer guids as wel as other data types stored.
在 PARAMETER1 中是客户 guid 以及存储的其他数据类型。
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:
我正在使用实体框架，但在比较字符串和 guid 类型时遇到了问题，所以我决定在我的监控表上创建一个视图：
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.
我在 EF 中导入了视图并进行了 Linq 查询。它什么也没返回，所以我提取了生成的 SQL 查询。在 SQL Management Studio 中测试查询时，我收到以下错误： 从字符串转换为 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
我试图制作一个 SQL 小提琴，但它正在该网站上工作。http://sqlfiddle.com/#!3/66d68/3
Anyone who can point me in the right direction?
谁能指出我正确的方向？
回答by Martin Smith
Use
用
TRY_CONVERT(UNIQUEIDENTIFIER, parameter2) AS customerguid
instead of
代替
CONVERT(UNIQUEIDENTIFIER, parameter2) AS customerguid
Views are inlined into the query and the CONVERTcan run before the WHERE.
视图被内联到查询中，并且CONVERT可以在WHERE.
For some additional discussion see SQL Server should not raise illogical errors
有关其他讨论，请参阅SQL Server 不应引发不合逻辑的错误
