当将varchar值'N'转换为数据类型int时，转换失败。

This is in SQL Server 2008 R2.

这是在SQL Server 2008 R2中。

I'm pulling my hair with this one. Follow closely, please.

我要用这个来拉我的头发。请密切关注。

When I run this, I get 27 rows returned:

当我运行这个时，我得到27行返回:

select * 
from dbo.[12STD_NO_VISIT] 
where (
         (dbo.fPhoneExists(PhoneNumber1) = 1  
         AND (NextCall1 BETWEEN GETDATE() AND DATEADD(hh, 1, GETDATE())))
      )

And when I run this, I get 21 rows returned (notice the change to PhoneNumber2 and NextCall2):

当我运行这个时，会返回21行(注意到PhoneNumber2和NextCall2的变化):

select * 
from dbo.[12STD_NO_VISIT] 
where (
          (dbo.fPhoneExists(PhoneNumber2) = 1  
           AND (NextCall2 BETWEEN GETDATE() AND DATEADD(hh, 1, GETDATE())))
      )

But, when I run this, 'ORing' the 2 conditions, I get an error:

但是，当我运行这两个条件时，我得到一个错误:

Conversion failed when converting the varchar value 'N' to data type int

当将varchar值'N'转换为数据类型int时，转换失败。

select * 
from dbo.[12STD_NO_VISIT] 
where (
          (dbo.fPhoneExists(PhoneNumber1) = 1  
           AND (NextCall1 BETWEEN GETDATE() AND DATEADD(hh, 1, GETDATE())))
       OR  
          (dbo.fPhoneExists(PhoneNumber2) = 1
           AND (NextCall2 BETWEEN GETDATE() AND  DATEADD(hh, 1, GETDATE())))
      )

But it doesn't just give me the error. It first retrieves 42 rows, displaying that for a split second (Results tab), and then it displays the error (Messages tab).

但它并不只是给出误差。它首先检索42行，显示为split second (Results选项卡)，然后显示错误(message选项卡)。

I can't figure this one out. Any help is very appreciated.

我想不出来。非常感谢您的帮助。

Thanks!

谢谢!

FUNCTION [dbo].[fPhoneExists](@PhoneNumber varchar) 
RETURNS BIT 
WITH EXECUTE AS CALLER 
AS 
BEGIN 
DECLARE @GoodNumber bit 
IF (@PhoneNumber is NULL or @PhoneNumber = 0 or @PhoneNumber = '') 
  SET @GoodNumber = 0; 
ELSE 
  SET @GoodNumber = 1; 
  Return(@GoodNumber); 
END

3 个解决方案

#1

What are the data types of PhoneNumber1 and PhoneNumber2? What is the definition of dbo.fPhoneExists? I suspect the problem lies there somewhere - or as Lynn suggests maybe there is more to the query than you've shown us (the query would either succeed or fail as a whole; it wouldn't produce 42 rows and then an error).

PhoneNumber1和PhoneNumber2的数据类型是什么?dbo的定义是什么?我怀疑这个问题存在于某个地方——或者正如Lynn所建议的那样，查询可能比您向我们展示的要多(查询要么成功，要么失败;它不会产生42行然后是一个错误。

Now that we see the function, here is a re-write:

现在我们看到了这个函数，这里有一个重写:

ALTER FUNCTION [dbo].[fPhoneExists]
(
  @PhoneNumber VARCHAR -- varchar(what)? This should match definition of column
) 
RETURNS BIT 
WITH EXECUTE AS CALLER 
AS 
BEGIN
  RETURN (SELECT CASE WHEN COALESCE(@PhoneNumber, '') IN ('0', '') THEN 0 ELSE 1 END);
END
GO

There is no reason to store the temporary logic in a variable, also notice that you are returning the variable only in the ELSE condition.

没有理由将临时逻辑存储在变量中，也注意到只在ELSE条件中返回变量。

#2

Your dbo.fPhoneExists function contains an implicit cast in the PhoneNumber = 0 expression that, according to the rules of Data Type Peecendence casts the PhoneNumber VARCHAR value to an int. This cast will fail if the value in the string is not a numeric. You are also falling into the fallacy of assuming that boolean operator short circuit is guaranteed in SQL, which is simply not true. SQL is a declarative language and the order of evaluation of boolean operators is not guaranteed.

dbo。fPhoneExists函数包含在PhoneNumber = 0表达式中的隐式转换，根据数据类型Peecendence的规则，将PhoneNumber VARCHAR值转换为int类型，如果字符串中的值不是数字，则该类型将失败。你也陷入了假设布尔运算符短路得到SQL保证的谬论，这是不正确的。SQL是一种声明性语言，不保证布尔运算符的评价顺序。

#3

You'll get better performance replacing fnPhoneExists by replacing the function with a CASE:

通过用例替换函数，您将获得更好的性能替换fnphone。

select * 
from dbo.[12STD_NO_VISIT] 
where (
          (case when ISNULL(PhoneNumber1,'') not in ('0','') then 1 else 0 end=1
           AND (NextCall1 BETWEEN GETDATE() AND DATEADD(hh, 1, GETDATE())))
       OR  
          (case when ISNULL(PhoneNumber2,'') not in ('0','') then 1 else 0 end=1
           AND (NextCall2 BETWEEN GETDATE() AND  DATEADD(hh, 1, GETDATE())))
      )

This is because the optimizer won't optimize the contents of fnPhoneExists.

这是因为优化器不会优化fnphone的内容。

#1