146 lines
8.6 KiB
Transact-SQL
146 lines
8.6 KiB
Transact-SQL
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
|