155 lines
7.1 KiB
Transact-SQL
155 lines
7.1 KiB
Transact-SQL
USE [DataPRO]
|
||
GO
|
||
/****** Object: StoredProcedure [dbo].[sp_SensorsDigitalInUpdateAll] Script Date: 6/19/2018 12:30:39 PM ******/
|
||
SET ANSI_NULLS ON
|
||
GO
|
||
SET QUOTED_IDENTIFIER ON
|
||
GO
|
||
ALTER PROCEDURE [dbo].[sp_SensorsDigitalInUpdateAll]
|
||
@sensors xml = null
|
||
,@errorNumber int output
|
||
,@errorMessage nvarchar(250) output
|
||
AS
|
||
BEGIN
|
||
set @errorNumber = 0
|
||
set @errorMessage = space(0)
|
||
|
||
if(@sensors is null)
|
||
begin
|
||
RAISERROR(15600,-1,-1, '[sp_SensorsDigitalInUpdateAll]') /* Error 1560 - An invalid parameter or option was specified for procedure*/
|
||
end
|
||
else
|
||
begin
|
||
SET NOCOUNT ON;
|
||
declare @SensorsDigitalIn Table(
|
||
[Id] [int] IDENTITY(1,1) NOT NULL,
|
||
[SerialNumber] [nvarchar](50) NOT NULL,
|
||
[SettingMode] [int] NOT NULL,
|
||
[ScaleMultiplier] [nvarchar](50) NOT NULL,
|
||
[LastModified] [datetime] NOT NULL,
|
||
[LastModifiedBy] [nvarchar](50) NOT NULL,
|
||
[eId] [nvarchar](50) NOT NULL,
|
||
[UserValue1] [nvarchar](255) NULL,
|
||
[UserValue2] [nvarchar](255) NULL,
|
||
[UserValue3] [nvarchar](255) NULL,
|
||
[UserTags] [varbinary](max) NULL,
|
||
[MeasurementUnit] [nvarchar](50) NOT NULL,
|
||
[FilterClass] [nvarchar](50) NOT NULL,
|
||
[Exist] [bit] DEFAULT ((0))
|
||
)
|
||
declare @exist bit
|
||
set @exist =(select @sensors.exist('/DigitalIn/Sensor'))
|
||
if(@exist = 1)
|
||
begin
|
||
|
||
insert into @SensorsDigitalIn select
|
||
t.x.value('@SerialNumber','nvarchar(50)')
|
||
, t.x.value('@SettingMode','int')
|
||
, t.x.value('@ScaleMultiplier','nvarchar(50)')
|
||
, t.x.value('@LastModified','datetime')
|
||
, t.x.value('@LastModifiedBy','nvarchar(50)')
|
||
, t.x.value('@eId','varchar(50)')
|
||
, t.x.value('@UserValue1','nvarchar(50)')
|
||
, t.x.value('@UserValue2','nvarchar(50)')
|
||
, t.x.value('@UserValue3','nvarchar(50)')
|
||
, t.x.value('@UserTags','varbinary(max)')
|
||
, t.x.value('@MeasurementUnit','nvarchar(50)')
|
||
, t.x.value('@FilterClass','nvarchar(50)')
|
||
, 0
|
||
from @sensors.nodes('/DigitalIn/Sensor') t(x)
|
||
OPTION (OPTIMIZE FOR ( @sensors = NULL ))
|
||
|
||
declare @count int
|
||
set @count = (select count(*) from @SensorsDigitalIn)
|
||
if(@count > 0)
|
||
begin
|
||
update @SensorsDigitalIn set Exist = case when EXISTS(select SerialNumber from v_SensorSerialNumber where SensorType = 2) then 1 else 0 end
|
||
|
||
update [dbo].[SensorsDigitalIn] SET
|
||
[SettingMode] = s.SettingMode
|
||
,[ScaleMultiplier] = s.ScaleMultiplier
|
||
,[LastModified] = s.LastModified
|
||
,[LastModifiedBy] = s.LastModifiedBy
|
||
,[eId] = s.eId
|
||
,[UserValue1] = s.UserValue1
|
||
,[UserValue2] = s.UserValue2
|
||
,[UserValue3] = s.UserValue3
|
||
,[UserTags] = s.UserTags
|
||
,[MeasurementUnit] = s.MeasurementUnit
|
||
,[FilterClass] = s.FilterClass
|
||
from @SensorsDigitalIn s where [dbo].[SensorsDigitalIn].[SerialNumber] = s.SerialNumber
|
||
|
||
insert into [dbo].[SensorsDigitalIn]
|
||
([SerialNumber]
|
||
,[SettingMode]
|
||
,[ScaleMultiplier]
|
||
,[LastModified]
|
||
,[LastModifiedBy]
|
||
,[eId]
|
||
,[UserValue1]
|
||
,[UserValue2]
|
||
,[UserValue3]
|
||
,[UserTags]
|
||
,[MeasurementUnit]
|
||
,[FilterClass])
|
||
select
|
||
[SerialNumber]
|
||
,[SettingMode]
|
||
,[ScaleMultiplier]
|
||
,[LastModified]
|
||
,[LastModifiedBy]
|
||
,[eId]
|
||
,[UserValue1]
|
||
,[UserValue2]
|
||
,[UserValue3]
|
||
,[UserTags]
|
||
,[MeasurementUnit]
|
||
,[FilterClass]
|
||
from @SensorsDigitalIn where Exist = 0
|
||
end
|
||
end
|
||
end
|
||
END
|
||
|
||
|
||
|
||
|
||
|
||
|
||
|
||
|
||
|
||
|
||
|
||
|
||
|
||
|
||
|
||
|
||
|
||
|
||
|
||
|
||
|
||
|
||
|
||
|
||
|
||
|
||
|
||
|
||
|
||
|
||
|
||
|
||
|
||
|
||
|
||
|
||
|
||
|
||
|
||
|
||
|
||
|