22,209
社区成员
发帖
与我相关
我的任务
分享
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
--exec [sp_EleRealTime] '00-19-25-b8-17-16',0.00,10,1,1,0,0,0,1,1,1,1,1,1,0,5,'2014-05-05 15:15:15'
-- =============================================
-- Author: <Author,WD、DMY>
-- Create date: <Create Date,20130930,>
-- Description: <Description,实时数据插库,>
-- =============================================
ALTER PROCEDURE [dbo].[sp_EleRealTime]
@MACAddress varchar(40) ,--Mac地址
@speed decimal(18,2) , --速度
@DoorZone bit, --门区
@Fixed bit, --检修
@FireOp bit, --消防操作
@DoorLock bit, --门联锁
@SafeBack bit, --安全回路
@Power bit, --电源
@IsAlarm bit, --故障标志位
@BaseStation bit, --基站信号
@HasPerson bit, --轿厢内有人
@Doorstate bit , --门信号
@DriverState bit, --电梯司机状态
@Dir int , --运行方向
@Floor int , --楼层
@RunTime_Open int , --电梯运行一次时间数据
@ReceiveDate datetime --数据接收时间
AS
BEGIN
declare @ElevatorID int,@pv varchar(50),@v varchar(20), @ef varchar(20),@efnow varchar(20),@newValue varchar(20)
select @ElevatorID=id from B_Elevators where Sign=@MACAddress
select @pv=ParamValue from dbo.S_El_Parameter where ElevatorID=@ElevatorID and ParamName='elevfloor'
set @pv=REPLACE(@pv,'}]','')
set @pv=REPLACE(@pv,'[{totle:','')
set @pv=REPLACE(@pv,'begin:','')
set @pv=REPLACE(@pv,'empty:','')
set @v=dbo.mysplit(@pv,2,',')
set @ef=dbo.mysplit(@pv,3,',')
set @efnow=REPLACE(@ef,'''','')
set @newValue=convert(int,@v)
if(@newValue<0)
begin
if( @Floor<=abs(@newValue))
set @Floor=@Floor+@newValue-1
else
set @Floor=@Floor-abs(@newValue)
end
else if(@newValue=0)
begin
set @Floor=@Floor
end
else if(@newValue>0)
begin
set @Floor=@Floor+@newValue-1
end
if (isnull(@efnow,'')<>'')
begin
set @Floor= dbo.GetRealFloor(@Floor,@efnow)
end
update dbo.R_EleRealTime
set Doorstate=@Doorstate, speed=@speed, Dir=@Dir, Floor=@Floor,
IsAlarm=@IsAlarm,IsFixed=@Fixed,IsFireOp=@FireOp,IsDriver=@DriverState
where Mac=@MACAddress
END
UPDATE dbo.r_elerealtime
SET doorstate = list.Doorstate,
speed = list.speed,
dir = list.Dir,
floor = list.Floor,
isalarm = list.IsAlarm,
isfixed = list.Fixed,
isfireop = list.FireOp,
isdriver = list.DriverState
FROM dbo.r_elerealtime, list
WHERE dbo.r_elerealtime.mac = list.MACAddress
AND list.批号 = @批号 -- 这是 SP 唯一需要的参数
update dbo.R_EleRealTime
set Doorstate=@Doorstate, speed=@speed, Dir=@Dir, Floor=@Floor,
IsAlarm=@IsAlarm,IsFixed=@Fixed,IsFireOp=@FireOp,IsDriver=@DriverState
where Mac=@MACAddress
我不用存储过程了 , 我直接把每条数据的SQL文拼好,放到一个 list 里,当满足您说的条件时,一起执行 ?