超奇怪的问题(关于insert into ...select from)，世界上没有更奇怪的事了!

javanow 2003-03-12 01:30:49

情况1：
我写的这个，Insert into TimeLength96163 ( UserID,IPAddress,LoginTime,LogoutTime)后面什么都没有了，可是语法检查是对的。

if exists(Select UserID from WorkTable96163 where IPAddress = @chvIPAddress) --查看这个IP有用户登录
begin
-- 只有一次登录与登出时间相差10分钟才有效
--print ''
Insert into TimeLength96163 ( UserID,IPAddress,LoginTime,LogoutTime) -- select UserID,IPAddress,LoginTime,LogoutTime From WorkTable96163 where IPAddress = @chvIPAddress and datediff(Minute,LoginTime,LogoutTime)>=10

--Insert into TimeLength96163_Backup ( UserID,IPAddress,LoginTime,LogoutTime) select UserID,IPAddress,LoginTime,LogoutTime From WorkTable96163 where IPAddress = @chvIPAddress and datediff(Minute,LoginTime,LogoutTime)>=10

Update WorkTable96163 set UserID = @intUserID,LoginTime = Getdate(), LogoutTime = Getdate() where IPAddress = @chvIPAddress

end

情况2: 在情况1的基础上，我的
--print ''
的注释去掉，语法检查又是通不过的

情况3:如下

begin
-- 只有一次登录与登出时间相差10分钟才有效
--print ''
Insert into TimeLength96163 ( UserID,IPAddress,LoginTime,LogoutTime) select UserID,IPAddress,LoginTime,LogoutTime From WorkTable96163 where IPAddress = @chvIPAddress and datediff(Minute,LoginTime,LogoutTime)>=10

Insert into TimeLength96163_Backup ( UserID,IPAddress,LoginTime,LogoutTime) select UserID,IPAddress,LoginTime,LogoutTime From WorkTable96163 where IPAddress = @chvIPAddress and datediff(Minute,LoginTime,LogoutTime)>=10

Update WorkTable96163 set UserID = @intUserID,LoginTime = Getdate(), LogoutTime = Getdate() where IPAddress = @chvIPAddress

end

-------
Insert into TimeLength96163 ( UserID,IPAddress,LoginTime,LogoutTime) select UserID,IPAddress,LoginTime,LogoutTime From WorkTable96163 where IPAddress = @chvIPAddress and datediff(Minute,LoginTime,LogoutTime)>=10
这条语句是不会插入记录的

情况4:在情况3的基础上，将　
--print ''
这个注释去掉，那么
Insert into TimeLength96163 ( UserID,IPAddress,LoginTime,LogoutTime) select UserID,IPAddress,LoginTime,LogoutTime From WorkTable96163 where IPAddress = @chvIPAddress and datediff(Minute,LoginTime,LogoutTime)>=10
生效了。

说明：在后台JOB执行时，情况3的2条插入语句是生效的。

我觉得太奇怪了，不相信的请看图例　

http://61.141.247.227/resource/strange.jpg

...全文

21 4 打赏收藏转发到动态举报

写回复

用AI写文章

4 条回复

切换为时间正序

请发表友善的回复…

发表回复

SophiaWang 2003-03-12

打赏
举报

CREATE PROCEDURE aa
@chvIPAddress int,
@intUserID int
As
if exists(Select UserID from WorkTable96163 where IPAddress = @chvIPAddress) --查看这个IP有用户登录
begin
-- 只有一次登录与登出时间相差10分钟才有效
print ''
Insert into TimeLength96163 ( UserID,IPAddress,LoginTime,LogoutTime) select UserID,IPAddress,LoginTime,LogoutTime From WorkTable96163 where IPAddress = @chvIPAddress and datediff(Minute,LoginTime,LogoutTime)>=10

Insert into TimeLength96163_Backup ( UserID,IPAddress,LoginTime,LogoutTime) select UserID,IPAddress,LoginTime,LogoutTime From WorkTable96163 where IPAddress = @chvIPAddress and datediff(Minute,LoginTime,LogoutTime)>=10

Update WorkTable96163 set UserID = @intUserID,LoginTime = Getdate(), LogoutTime = Getdate() where IPAddress = @chvIPAddress

end
确实是对的！

pengdali 2003-03-12

打赏
举报

javanow 2003-03-12

打赏
举报

在JOB（作业）里的script语法检查里，　

情况1：
我写的这个，Insert into TimeLength96163 ( UserID,IPAddress,LoginTime,LogoutTime)后面什么都没有了，可是语法检查是对的。

if exists(Select UserID from WorkTable96163 where IPAddress = @chvIPAddress) --查看这个IP有用户登录
begin
-- 只有一次登录与登出时间相差10分钟才有效
--print ''
Insert into TimeLength96163 ( UserID,IPAddress,LoginTime,LogoutTime) -- select UserID,IPAddress,LoginTime,LogoutTime From WorkTable96163 where IPAddress = @chvIPAddress and datediff(Minute,LoginTime,LogoutTime)>=10

--Insert into TimeLength96163_Backup ( UserID,IPAddress,LoginTime,LogoutTime) select UserID,IPAddress,LoginTime,LogoutTime From WorkTable96163 where IPAddress = @chvIPAddress and datediff(Minute,LoginTime,LogoutTime)>=10

Update WorkTable96163 set UserID = @intUserID,LoginTime = Getdate(), LogoutTime = Getdate() where IPAddress = @chvIPAddress

end

是通不过的

javanow 2003-03-12