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
4
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
3
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
1
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
4
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
3
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
1
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的内容。