sql加浮点数的问题及其解决方法！

李维强-15级

由于在服务器windows事件查看器里面，发现.net应用程序池老是不定时报错，报错位置就是SQL执行的地方，

问题很简单 例子就拿现成的来说

以下是我执行的SQL语句：

[SQL] syntaxhighlighter_viewsource syntaxhighlighter_copycode

begin 
select * into #myTempTable FROM (select '687216083021602' AS GPSIMEI,108.234048 as lng,22.844226 as lat,0 as speed,'2016-10-24 01:31:14' as serverTime,'20161024013114000' as GPSinformationID,'设备不正常 device_info=3' as log union all select '687216083021560' AS GPSIMEI,108.487501 as lng,22.823695 as lat,69 as speed,'2016-10-24 01:31:14' as serverTime,'20161024013114001' as GPSinformationID,'正常' as log ) a;

UPDATE a SET a.Latitude=b.lat ,a.Longitude=b.lng,a.UpdateTime=b.serverTime,a.Log='state='+b.log+'&speed='+CONVERT(VARCHAR(20),CONVERT(DECIMAL(20,7),b.speed)) FROM T_M_GPSInformationTemp a INNER JOIN #myTempTable as b ON a.gpsimei=b.gpsimei;

drop table #myTempTable;

end

在上面这个语句执行update的时候，会引发触发器，以下是触发器代码

[SQL] syntaxhighlighter_viewsource syntaxhighlighter_copycode

BEGIN

INSERT INTO T_M_GPSInformation (GPSInformationID,GPSIMEI,Longitude,Latitude,UpdateTime,speed,Log) 
SELECT 
m.GPSinformationID,i.GPSIMEI,i.Longitude,i.Latitude,m.serverTime,m.speed,i.log
from deleted d INNER JOIN inserted i ON d.GPSIMEI=i.GPSIMEI
INNER JOIN #myTempTable m ON m.GPSIMEI=d.GPSIMEI
where
ABS(6378137*ACOS(cos(i.Latitude/57.2958)*cos(d.Latitude/57.2958)*cos((d.Longitude-i.Longitude)/57.2958) + sin(d.Latitude/57.2958)*sin(i.Latitude/57.2958)))>30 

END

那么执行上面的SQL语句就会报错，错误显示“[SQL Server]出现无效的浮点操作。”
问题的关键就是出在这个ACOS(cos(i.Latitude/57.2958)*cos(d.Latitude/57.2958)*cos((d.Longitude-i.Longitude)/57.2958) + sin(d.Latitude/57.2958)*sin(i.Latitude/57.2958))里面，
这里还是多谢周老师提醒，不然我仅到都试不出来哈。
==============================================================================
下面是原因分析
我调试过后，跟踪发现 执行上面的SQL，发现 但是这一段cos(i.Latitude/57.2958)*cos(d.Latitude/57.2958)*cos((d.Longitude-i.Longitude)/57.2958) 算出来是0.849536178179997，而另一段sin(d.Latitude/57.2958)*sin(i.Latitude/57.2958)算出来是0.150463821820003 按理说 这两个数相加 就是1，那么最后取acos(1)=0;这个是正常的，但是在SQL SERVER里面，两个这种长度的带小数的数据相加，加起来不是1，可能是1.00000几。而acos（x）的定义域范围是[-1,1]，所以就超过了该定义域范围，导致报错。
但是如果我们只是执行算着两个值相加，SQL也会正常显示出1，如下面的语句

[SQL] syntaxhighlighter_viewsource syntaxhighlighter_copycode

select  cos(i.Latitude/57.2958)*cos(d.Latitude/57.2958)*cos((d.Longitude-i.Longitude)/57.2958) + sin(d.Latitude/57.2958)*sin(i.Latitude/57.2958) 
from deleted d INNER JOIN inserted i ON d.GPSIMEI=i.GPSIMEI
--这种正常输出1

但是 在前面加个acos的话，就直接报错了，肯定就是超出范围了

[SQL] syntaxhighlighter_viewsource syntaxhighlighter_copycode

select  acos(cos(i.Latitude/57.2958)*cos(d.Latitude/57.2958)*cos((d.Longitude-i.Longitude)/57.2958) + sin(d.Latitude/57.2958)*sin(i.Latitude/57.2958)) 
from deleted d INNER JOIN inserted i ON d.GPSIMEI=i.GPSIMEI
--这种就报浮点数错误

好了 只要找到原因，问题就好解决。在触发器里面来个case when 即可

[SQL] syntaxhighlighter_viewsource syntaxhighlighter_copycode

INSERT INTO T_M_GPSInformation (GPSInformationID,GPSIMEI,Longitude,Latitude,UpdateTime,speed,Log) 
SELECT 
m.GPSinformationID,i.GPSIMEI,i.Longitude,i.Latitude,m.serverTime,m.speed,i.log
from deleted d INNER JOIN inserted i ON d.GPSIMEI=i.GPSIMEI
INNER JOIN #myTempTable m ON m.GPSIMEI=d.GPSIMEI
where
ABS(6378137*ACOS(
case when (cos(i.Latitude/57.2958)*cos(d.Latitude/57.2958)*cos((d.Longitude-i.Longitude)/57.2958) + sin(d.Latitude/57.2958)*sin(i.Latitude/57.2958))>=1 then 1  ELSE (cos(i.Latitude/57.2958)*cos(d.Latitude/57.2958)*cos((d.Longitude-i.Longitude)/57.2958) + sin(d.Latitude/57.2958)*sin(i.Latitude/57.2958)) END
))>30 

在以上代码中 那个>=1是关键，因为 前面说了 如果单独这么算，是正常的，但是套在acos里面就不正常了