SQL Server：唯一标识符上的 ISNULL
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/32280994/
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 Server: ISNULL on uniqueidentifier
提问by TVogt
I am trying to compare a column col1and a variable @myvarin a WHEREclause. Both usually contain GUIDs, but may also have NULL values.
I thought I could get around the fact that NULL=NULLevaluates to FALSE by using WHERE ISNULL(col1, '')=ISNULL(@myvar, ''). That would compare two empty strings instead, and evaluate to TRUE.
我想比较的列col1和可变@myvar一个在WHERE条款。两者通常都包含 GUID，但也可能包含 NULL 值。我想我可以NULL=NULL通过使用WHERE ISNULL(col1, '')=ISNULL(@myvar, ''). 这将比较两个空字符串，并评估为 TRUE。
This will, however, produce the following error message:
但是，这将产生以下错误消息：
Msg 8169, Level 16, State 2, Line 3 Conversion failed when converting from a character string to uniqueidentifier.
消息 8169，级别 16，状态 2，第 3 行从字符串转换为 uniqueidentifier 时转换失败。
I tried
我试过
DECLARE @myvar uniqueidentifier = NULL
SELECT ISNULL(@myvar,'') as col1
Same error message.
同样的错误信息。
Two questions: First, I am trying to convert a uniqueidentifier variable - even though it has a NULL value - to an (empty!) string, not the other way around, as the error message suggests. What gives?
两个问题：首先，我试图将 uniqueidentifier 变量 - 即使它具有 NULL 值 - 转换为（空！）字符串，而不是相反，正如错误消息所暗示的那样。是什么赋予了？
Second, is there a better way to word that WHERE clause I need, to allow for comparing uniqueidentifiers that might be NULL?
其次，是否有更好的方式来表达我需要的 WHERE 子句，以允许比较可能为 NULL 的唯一标识符？
采纳答案by Mureinik
Since the first argument you are passing isnullis not a literal null, it will determine the return type of that call, a uniqueidentifierin your case. The second argument, '', cannot be cast to this type, hence the error you're getting.
由于您传递的第一个参数isnull不是文字null，因此它将确定该调用的返回类型，uniqueidentifier在您的情况下为 a 。第二个参数 ,''不能转换为这种类型，因此会出现错误。
One way around this is just to explicitly check for nulls:
解决此问题的一种方法是明确检查nulls：
WHERE (@myvar IS NULL AND col1 IS NULL) OR (col1 = @myvar)
回答by NidhinSPradeep
I think below expression can be used to check if the GUID column is empty
我认为下面的表达式可用于检查 GUID 列是否为空
CAST(0x0 AS UNIQUEIDENTIFIER)
some thing like
就像是
...WHERE GuidId <> CAST(0x0 AS UNIQUEIDENTIFIER)
回答by Amit
The reason ISNULLisn't working for you is that the replacement value (the value to be used if the check expression really is null) must be implicitly convertible to the type of the check expression.
ISNULL对您不起作用的原因是替换值（如果检查表达式确实为空时要使用的值）必须隐式转换为检查表达式的类型。
Your WHEREclause can use a col IS NULL AND @var IS NULLto check that state.
您的WHERE子句可以使用 acol IS NULL AND @var IS NULL来检查该状态。
回答by Emacs User
As others have pointed out, exclude the NULL values from the results and THEN do the comparison. You can use COALESCE to exclude NULL values from comparisons.
正如其他人指出的那样，从结果中排除 NULL 值，然后进行比较。您可以使用 COALESCE 从比较中排除 NULL 值。
回答by Sanjeev Gaur
Try the following code:
试试下面的代码：
WHERE ISNULL([Guid], NEWID()) = @myvar
回答by dasarghya
Here is another way to overcome this issue:
这是解决此问题的另一种方法：
DECLARE @myvar uniqueidentifier = NEWID()
SELECT * FROM TABLE
Where ISNULL(col1,@myvar) = ISNULL(Col2,@myvar)
This will resolve your error. Conversion failed when converting from a character string to uniqueidentifier.
这将解决您的错误。从字符串转换为 时转换失败uniqueidentifier。
