142 lines
7.0 KiB
Transact-SQL
142 lines
7.0 KiB
Transact-SQL
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_SensorsDigitalOutUpdateAll]') AND type in (N'P', N'PC'))
|
||
DROP PROCEDURE [dbo].[sp_SensorsDigitalOutUpdateAll]
|
||
GO
|
||
SET ANSI_NULLS ON
|
||
GO
|
||
SET QUOTED_IDENTIFIER ON
|
||
GO
|
||
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_SensorsDigitalOutUpdateAll]') AND type in (N'P', N'PC'))
|
||
BEGIN
|
||
EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[sp_SensorsDigitalOutUpdateAll] AS'
|
||
END
|
||
GO
|
||
ALTER PROCEDURE [dbo].[sp_SensorsDigitalOutUpdateAll]
|
||
@sensors xml = null
|
||
,@errorNumber int output
|
||
,@errorMessage nvarchar(250) output
|
||
AS
|
||
BEGIN
|
||
set @errorNumber = 0
|
||
set @errorMessage = space(0)
|
||
|
||
if(@sensors is null)
|
||
begin
|
||
set @errorNumber = 1560
|
||
set @errorMessage = 'An invalid parameter or option was specified for procedure';
|
||
end
|
||
else
|
||
begin
|
||
SET NOCOUNT ON;
|
||
declare @SensorsDigitalOut table(
|
||
[Id] [int] IDENTITY(1,1) NOT NULL,
|
||
[SerialNumber] [nvarchar](50) NOT NULL,
|
||
[DelayMS] [float] NOT NULL,
|
||
[DurationMS] [smallint] NOT NULL,
|
||
[OutputMode] [smallint] NOT NULL,
|
||
[LimitDuration] [bit] NOT NULL,
|
||
[LastModified] [datetime] NOT NULL,
|
||
[LastModifiedBy] [nvarchar](50) NOT NULL,
|
||
[Version] [int] NOT NULL,
|
||
[LocalOnly] [bit] NOT NULL,
|
||
[DurationMSFloat] [float] NOT NULL,
|
||
[UserTags] [varbinary](max) NULL,
|
||
[Exist] [bit] NOT NULL DEFAULT ((0)))
|
||
declare @exist bit
|
||
set @exist =(select @sensors.exist('/DigitalIn/Sensor'))
|
||
if(@exist = 1)
|
||
begin
|
||
insert into @SensorsDigitalOut select
|
||
t.x.value('@SerialNumber','nvarchar(50)')
|
||
,t.x.value('@DelayMS','float')
|
||
,t.x.value('@DurationMS','smallint')
|
||
,t.x.value('@OutputMode','smallint')
|
||
,t.x.value('@LimitDuration','bit')
|
||
,t.x.value('@LastModified','datetime')
|
||
,t.x.value('@LastModifiedBy','nvarchar(50)')
|
||
,t.x.value('@Version','int')
|
||
,t.x.value('@LocalOnly','bit')
|
||
,t.x.value('@DurationMSFloat','float')
|
||
,t.x.value('@UserTags','varbinary(max)')
|
||
,0
|
||
from @sensors.nodes('/DigitalOut/Sensor') t(x)
|
||
OPTION (OPTIMIZE FOR ( @sensors = NULL ))
|
||
|
||
declare @count int
|
||
set @count = (select count(*) from @SensorsDigitalOut)
|
||
if(@count > 0)
|
||
begin
|
||
update @SensorsDigitalOut set Exist = case when EXISTS(select SerialNumber from v_SensorSerialNumber where SensorType = 1) then 1 else 0 end
|
||
|
||
update [dbo].[SensorsDigitalOut] SET
|
||
[DelayMS] = s.DelayMS
|
||
,[DurationMS] = s.DurationMS
|
||
,[OutputMode] = s.OutputMode
|
||
,[LimitDuration] = s.LimitDuration
|
||
,[LastModified] = s.LastModified
|
||
,[LastModifiedBy] = s.LastModifiedBy
|
||
,[Version] = s.[Version]
|
||
,[LocalOnly] = s.LocalOnly
|
||
,[DurationMSFloat] = s.DurationMSFloat
|
||
,[UserTags] = s.UserTags
|
||
from @SensorsDigitalOut s
|
||
where [dbo].[SensorsDigitalOut].[SerialNumber] = s.SerialNumber
|
||
|
||
insert into [dbo].[SensorsDigitalOut]
|
||
(SerialNumber
|
||
,DelayMS
|
||
,DurationMS
|
||
,OutputMode
|
||
,LimitDuration
|
||
,LastModified
|
||
,LastModifiedBy
|
||
,Version
|
||
,LocalOnly
|
||
,DurationMSFloat
|
||
,UserTags)
|
||
select
|
||
SerialNumber
|
||
,DelayMS
|
||
,DurationMS
|
||
,OutputMode
|
||
,LimitDuration
|
||
,LastModified
|
||
,LastModifiedBy
|
||
,[Version]
|
||
,LocalOnly
|
||
,DurationMSFloat
|
||
,UserTags
|
||
from @SensorsDigitalOut where Exist = 0
|
||
end
|
||
end
|
||
end
|
||
END
|
||
|
||
|
||
|
||
|
||
|
||
|
||
|
||
|
||
|
||
|
||
|
||
|
||
|
||
|
||
|
||
|
||
|
||
|
||
|
||
|
||
|
||
|
||
|
||
|
||
|
||
|
||
|
||
|
||
GO
|