Files
DP44/DataPRO_sql/dbo.sp_DBImportGroupTemplateChannels.StoredProcedure.sql

146 lines
8.6 KiB
MySQL
Raw Permalink Normal View History

2026-04-17 14:55:32 -04:00
<EFBFBD><EFBFBD>IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_DBImportGroupTemplateChannels]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_DBImportGroupTemplateChannels]
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_DBImportGroupTemplateChannels]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[sp_DBImportGroupTemplateChannels] AS'
END
GO
ALTER PROCEDURE [dbo].[sp_DBImportGroupTemplateChannels]
@TemplateId int
,@GroupTemplateChannels xml
,@errorNumber int output
,@errorMessage nvarchar(250) output
AS
BEGIN
set @errorNumber = 0
set @errorMessage = space(0)
begin try
begin transaction tGroupTemplateChannels
insert into [dbo].[TemplateChannels]
([TemplateId]
,[TestObjectNumber]
,[NameOfTheChannel]
,[LaboratoryChannelCode]
,[CustomerChannelCode]
,[Comments1]
,[Location]
,[Dimension]
,[Direction]
,[ChannelFrequencyClass]
,[Unit]
,[ReferenceSystem]
,[TransducerType]
,[TransducerId]
,[PreFilterType]
,[CutOffFrequency]
,[ChannelAmplitudeClass]
,[ReferenceChannel]
,[ReferenceChannelName]
,[DataSource]
,[DataStatus]
,[SamplingInterval]
,[BitResolution]
,[TimeOfFirstSample]
,[NumberOfSamples]
,[OffsetPostTest]
,[TransducerNaturalFrequency]
,[TransducerDampingRatio]
,[Comments]
,[FirstGlobalMaximumValue]
,[TimeOfMaximumValue]
,[FirstGlobalMinimumValue]
,[TimeOfMinimumValue]
,[StartOffsetInterval]
,[EndOffsetInterval]
,[Required]
,[LocalOnly]
,[MMEChannelId]
,[MMEChannelType]
,[DisplayOrder])
select
@TemplateId
,t.x.value('(TestObjectNumber)[1]', 'nvarchar(50)')
,t.x.value('(NameOfTheChannel)[1]', 'nvarchar(50)')
,t.x.value('(LaboratoryChannelCode)[1]', 'nvarchar(50)')
,t.x.value('(CustomerChannelCode)[1]', 'nvarchar(50)')
,t.x.value('(Comments1)[1]', 'nvarchar(50)')
,t.x.value('(Location)[1]', 'nvarchar(50)')
,t.x.value('(Dimension)[1]', 'nvarchar(50)')
,t.x.value('(Direction)[1]', 'nvarchar(50)')
,t.x.value('(ChannelFrequencyClass)[1]', 'nvarchar(50)')
,t.x.value('(Unit)[1]', 'nvarchar(50)')
,t.x.value('(ReferenceSystem)[1]', 'nvarchar(50)')
,t.x.value('(TransducerType)[1]', 'nvarchar(50)')
,t.x.value('(TransducerId)[1]', 'nvarchar(50)')
,t.x.value('(PreFilterType)[1]', 'nvarchar(50)')
,t.x.value('(CutOffFrequency)[1]', 'nvarchar(50)')
,t.x.value('(ChannelAmplitudeClass)[1]', 'nvarchar(50)')
,t.x.value('(ReferenceChannel)[1]', 'nvarchar(50)')
,t.x.value('(ReferenceChannelName)[1]', 'nvarchar(50)')
,t.x.value('(DataSource)[1]', 'nvarchar(50)')
,t.x.value('(DataStatus)[1]', 'nvarchar(50)')
,t.x.value('(SamplingInterval)[1]', 'nvarchar(50)')
,t.x.value('(BitResolution)[1]', 'nvarchar(50)')
,t.x.value('(TimeOfFirstSample)[1]', 'nvarchar(50)')
,t.x.value('(NumberOfSamples)[1]', 'nvarchar(50)')
,t.x.value('(OffsetPostTest)[1]', 'nvarchar(50)')
,t.x.value('(TransducerNaturalFrequency)[1]', 'nvarchar(50)')
,t.x.value('(TransducerDampingRatio)[1]', 'nvarchar(50)')
,t.x.value('(Comments)[1]', 'nvarchar(50)')
,t.x.value('(FirstGlobalMaximumValue)[1]', 'nvarchar(50)')
,t.x.value('(TimeOfMaximumValue)[1]', 'nvarchar(50)')
,t.x.value('(FirstGlobalMinimumValue)[1]', 'nvarchar(50)')
,t.x.value('(TimeOfMinimumValue)[1]', 'nvarchar(50)')
,t.x.value('(StartOffsetInterval)[1]', 'nvarchar(50)')
,t.x.value('(EndOffsetInterval)[1]', 'nvarchar(50)')
,t.x.value('(Required)[1]', 'bit')
,t.x.value('(LocalOnly)[1]', 'bit')
,t.x.value('(MMEChannelId)[1]', 'bigint')
,t.x.value('(MMEChannelType)[1]', 'int')
,t.x.value('(DisplayOrder)[1]', 'int')
from @GroupTemplateChannels.nodes('/TemplateChannels/TemplateChannel') t(x)
commit transaction tTestObjectSensors
end try
begin catch
set @errorNumber = error_number()
set @errorMessage = error_message()
rollback transaction tTestObjectSensors
end catch;
END
GO