116 lines
4.6 KiB
Plaintext
116 lines
4.6 KiB
Plaintext
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_DBImportGroupSensors]') AND type in (N'P', N'PC'))
|
|
DROP PROCEDURE [dbo].[sp_DBImportGroupSensors]
|
|
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_DBImportGroupSensors]') AND type in (N'P', N'PC'))
|
|
BEGIN
|
|
EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[sp_DBImportGroupSensors] AS'
|
|
END
|
|
GO
|
|
ALTER PROCEDURE [dbo].[sp_DBImportGroupSensors]
|
|
@TestObjectId int
|
|
,@TestObjectName varchar(50)
|
|
,@Sensors xml
|
|
,@errorNumber int output
|
|
,@errorMessage nvarchar(250) output
|
|
AS
|
|
BEGIN
|
|
set @errorNumber = 0
|
|
set @errorMessage = space(0)
|
|
begin try
|
|
begin transaction tTestObjectSensors
|
|
|
|
declare @tSensors table (
|
|
SensorId int
|
|
, DASId int
|
|
, TestObjectId int
|
|
, ISOChannel bit
|
|
, ISOChannelId int
|
|
, ChannelId int
|
|
, ChannelIdx int
|
|
, ZoneId nvarchar(50)
|
|
, LocalOnly bit)
|
|
|
|
insert into @tSensors
|
|
(SensorId
|
|
, DASId
|
|
, TestObjectId
|
|
, ISOChannel
|
|
, ISOChannelId
|
|
, ChannelId
|
|
, ChannelIdx
|
|
, ZoneId
|
|
, LocalOnly)
|
|
select
|
|
t.x.value('(@SensorId)', 'int')
|
|
, t.x.value('(@DASId)', 'int')
|
|
, @TestObjectId
|
|
, t.x.value('(@ISOChannel)', 'bit')
|
|
, t.x.value('(@ISOChannelId)', 'int')
|
|
, t.x.value('(@ChannelId)', 'int')
|
|
, t.x.value('(@ChannelIdx)', 'int')
|
|
, t.x.value('(@ZoneId)', 'nvarchar(50)')
|
|
, t.x.value('(@LocalOnly)', 'bit')
|
|
|
|
from @Sensors.nodes('/Sensors/Sensor') t(x)
|
|
|
|
insert into [dbo].[TestObjectSensors]
|
|
([SensorId]
|
|
, [DASId]
|
|
, [TestObjectId]
|
|
, [ISOChannel]
|
|
, [ISOChannelId]
|
|
, [ChannelId]
|
|
, [ZoneId]
|
|
, [LocalOnly]
|
|
, [ChannelIdx])
|
|
select
|
|
SensorId
|
|
, DASId
|
|
, TestObjectId
|
|
, ISOChannel
|
|
, ISOChannelId
|
|
, ChannelId
|
|
, ZoneId
|
|
, LocalOnly
|
|
, ChannelIdx
|
|
from @tSensors where SensorId != 0 /* or any othe restrictions/validations*/
|
|
commit transaction tTestObjectSensors
|
|
end try
|
|
begin catch
|
|
set @errorNumber = error_number()
|
|
set @errorMessage = error_message()
|
|
rollback transaction tTestObjectSensors
|
|
end catch;
|
|
END
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
GO
|