163 lines
9.6 KiB
Transact-SQL
163 lines
9.6 KiB
Transact-SQL
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
|