DP44/DataPRO_sql/dbo.sp_DBImportTestSetupGroups.StoredProcedure.sql

<EFBFBD><EFBFBD>IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_DBImportTestSetupGroups]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_DBImportTestSetupGroups]
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_DBImportTestSetupGroups]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[sp_DBImportTestSetupGroups] AS' 
END
GO
ALTER PROCEDURE [dbo].[sp_DBImportTestSetupGroups]
		 @TestSetupId		int
		,@TestSetup			xml
		,@errorNumber				int				output
		,@errorMessage				nvarchar(250)	output 
AS
BEGIN
	set @errorNumber = 0 
	set @errorMessage = space(0)

	declare @TestGroups xml
	
	declare @tTestGroup table(
			 recId					int identity(1,1)
			,TestSetupId			int
			,TestObjectId			int
			,TestObjectName			varchar(50)
			,TestSetupName			varchar(50)
			,TargetSampleRate		int
			,ExcitationWarmupTimeMS float
			,LocalOnly				bit
			,TestObjectType			varchar(50)
			,TestObjectPosition		varchar(50)
			,ChannelSetting			xml
			,Processed				bit)

	declare @t1 table (x xml)

	insert into @t1
	select convert(xml, replace(replace(convert(varchar(max), t.x.query('.')), '<TestObject>', '<AddedGroup>1</AddedGroup>'), '</TestObject>', space(0))) 
	from @TestSetup.nodes('/TestSetup/AddedGroups/TestObject') t(x)

	insert into @t1
	select convert(xml, replace(replace(convert(varchar(max), t.x.query('.')), '<TestObject>', '<AddedGroup>0</AddedGroup>'), '</TestObject>', space(0))) 
	from @TestSetup.nodes('/TestSetup/TestObjects/TestObject') t(x)

	set @TestGroups = (select x TestObject  from @t1 for xml path(''), root('TestObjects'))

	insert into @tTestGroup 
			(TestSetupId			
			,TestObjectId			
			,TestObjectName			
			,TestSetupName			
			,TargetSampleRate		
			,ExcitationWarmupTimeMS 
			,LocalOnly				
			,TestObjectType			
			,TestObjectPosition		
			,ChannelSetting			
			,Processed)
	select 
		 @TestSetupId /* dbo.foo_IdGetTestSetup(t.x.value('(TestSetupName)[1]', 'nvarchar(50)')) */
		,dbo.foo_IdGetTestSetupObject(t.x.value('(TestObjectSerialNumber)[1]', 'nvarchar(50)') )
		,t.x.value('(TestObjectSerialNumber)[1]', 'nvarchar(50)') 
		,t.x.value('(TestSetupName)[1]', 'nvarchar(50)')
		,t.x.value('(TargetSampleRate)[1]', 'int')
		,t.x.value('(ExcitationWarmupTimeMS)[1]', 'float')
		,t.x.value('(LocalOnly)[1]', 'bit')
		,t.x.value('(TestObjectType)[1]', 'nvarchar(50)')
		,t.x.value('(TestObjectPosition)[1]', 'nvarchar(50)')
		,convert(xml, '<ChannelSettings>' + replace(replace(convert(varchar(max), t.x.query('ChannelSettings')), '<ChannelSettings>', '<ChannelSetting>'), '</ChannelSettings>', '</ChannelSetting>') + '</ChannelSettings>')
		,0
	from @TestGroups.nodes('/TestObjects/TestObject') t(x)
	
	while (Select Count(*) From @tTestGroup Where Processed = 0) > 0
		Begin
			begin try
				begin transaction tTestObject
				declare @recId						int
						,@TestObjectId				int
						,@TestObjectName			varchar(50)
						,@TestSetupName				varchar(50)
						,@TargetSampleRate			int
						,@ExcitationWarmupTimeMS	float
						,@LocalOnly					bit
						,@TestObjectType			int
						,@TestObjectPosition		varchar(1)
						,@ChannelSetting			xml

				select top 1
					 @recId						= recId
					,@TestSetupId				= TestSetupId			
					,@TestObjectId				= TestObjectId			
					,@TestObjectName			= TestObjectName		
					,@TestSetupName				= TestSetupName			
					,@TargetSampleRate			= TargetSampleRate		
					,@ExcitationWarmupTimeMS	= ExcitationWarmupTimeMS
					,@LocalOnly					= LocalOnly				
					,@TestObjectType			= TestObjectType		
					,@TestObjectPosition		= TestObjectPosition	
					,@ChannelSetting			= ChannelSetting		
				from @tTestGroup where Processed = 0 

				declare @TestSetupObjectId int
				set @TestObjectId = dbo.foo_IdGetTestObject(@TestObjectName)

				exec [dbo].[sp_TestSetupObjectsUpdateInsert]	 @TestSetupId			
																,@TestSetupName			
																,@TestObjectId			
																,@TestObjectName		
																,@TargetSampleRate		
																,@ExcitationWarmupTimeMS
																,@LocalOnly				
																,@TestObjectType		
																,@TestObjectPosition
																,@TestSetupObjectId			output
																,@errorNumber				output
																,@errorMessage				output 
					
				exec dbo.sp_DBImportGroupChannelSetting  @TestObjectId, @TestObjectName,  @ChannelSetting, @errorNumber output, @errorMessage output 

				update @tTestGroup set [Processed] = 1 where recId = @recId

				commit transaction tTestObject
			end try
			begin catch
				set @errorNumber = error_number()
				set @errorMessage = error_message()
				rollback transaction tTestObject
			end catch;
		end
END



























GO