DP44/DataPRO_sql/dbo.sp_DBImportGroupTemplateChannels.StoredProcedure.sql

<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