搞不懂啥问题,存储过程就两条Update语句。可是不知为啥总感觉只能有一条能执行。
ALTER PROCEDURE [dbo].[USP_AutoRun]
AS
BEGIN UPDATE dbo.Equipment SET Status=1 WHERE PK_ID IN( SELECT FK_EquipmentID FROM dbo.DeviceLight AS L WHERE DATEDIFF(ss,L.UpdateTime,GETDATE())<1 )
UPDATE dbo.Equipment SET Status=0 WHERE Status>0 and PK_ID IN (SELECT FK_EquipmentID FROM dbo.DeviceLight AS L WHERE DATEDIFF(ss,L.UpdateTime,GETDATE())>2)
END
ALTER PROCEDURE [dbo].[USP_AutoRun]
AS
BEGIN UPDATE dbo.Equipment SET Status=1 WHERE PK_ID IN( SELECT FK_EquipmentID FROM dbo.DeviceLight AS L WHERE DATEDIFF(ss,L.UpdateTime,GETDATE())<1 )
UPDATE dbo.Equipment SET Status=0 WHERE Status>0 and PK_ID IN (SELECT FK_EquipmentID FROM dbo.DeviceLight AS L WHERE DATEDIFF(ss,L.UpdateTime,GETDATE())>2)
END
FROM dbo.Equipment
WHERE PK_ID IN (SELECT FK_EquipmentID
FROM dbo.DeviceLight AS L
WHERE DATEDIFF(ss, L.UpdateTime, GETDATE()) < 1)
SELECT COUNT(1)
FROM dbo.Equipment
WHERE STATUS > 0
AND PK_ID IN (SELECT FK_EquipmentID
FROM dbo.DeviceLight AS L
WHERE DATEDIFF(ss, L.UpdateTime, GETDATE()) > 2)
先用相同条件的 select 语句, 查一下再说吧。