243 lines
16 KiB
Transact-SQL
243 lines
16 KiB
Transact-SQL
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_DBImport]') AND type in (N'P', N'PC'))
|
||
DROP PROCEDURE [dbo].[sp_DBImport]
|
||
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_DBImport]') AND type in (N'P', N'PC'))
|
||
BEGIN
|
||
EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[sp_DBImport] AS'
|
||
END
|
||
GO
|
||
ALTER PROCEDURE [dbo].[sp_DBImport]
|
||
@xFile xml
|
||
,@errorNumber int output
|
||
,@errorMessage nvarchar(250) output
|
||
AS
|
||
BEGIN
|
||
set @errorNumber = 0
|
||
set @errorMessage = space(0)
|
||
|
||
declare @TotalItems varchar(50)
|
||
declare @Version varchar(50)
|
||
declare @Software varchar(50)
|
||
declare @SoftwareVersion varchar(50)
|
||
|
||
declare @CustomerDetails xml
|
||
declare @TestEngineerDetails xml
|
||
declare @LabDetails xml
|
||
declare @DASList xml
|
||
declare @SensorModels xml
|
||
declare @Sensors xml
|
||
declare @SensorsCalibrations xml
|
||
declare @GroupTemplates xml
|
||
declare @Groups xml
|
||
declare @TestSetups xml
|
||
declare @Users xml
|
||
declare @GlobalSettings xml
|
||
|
||
declare @MMEDirections xml
|
||
declare @MMEFilterClasses xml
|
||
declare @MMEFinLoc1 xml
|
||
declare @MMEFinLoc2 xml
|
||
declare @MMEFinLoc3 xml
|
||
declare @MMEMainLocation xml
|
||
declare @MMEPhysicalDimensions xml
|
||
declare @MMEPositions xml
|
||
declare @MMEPossibleChannels xml
|
||
|
||
if(@xFile is null)
|
||
begin
|
||
set @errorMessage = 1560
|
||
set @errorNumber = 'An invalid parameter or option was specified for procedure'
|
||
end
|
||
else
|
||
begin
|
||
select @TotalItems = t.x.value('@TotalItems','nvarchar(50)')
|
||
,@Version = t.x.value('@Version','nvarchar(50)')
|
||
,@Software = t.x.value('@Software','nvarchar(50)')
|
||
,@SoftwareVersion = t.x.value('@SoftwareVersion','nvarchar(50)')
|
||
,@CustomerDetails = t.x.query('/ExportFile/CustomerDetails')
|
||
,@TestEngineerDetails = t.x.query('/ExportFile/TestEngineerDetails')
|
||
,@LabDetails = t.x.query('/ExportFile/LabDetails')
|
||
,@DASList = t.x.query('/ExportFile/DASList')
|
||
,@SensorModels = t.x.query('/ExportFile/SensorModels')
|
||
,@Sensors = t.x.query('/ExportFile/Sensors')
|
||
,@SensorsCalibrations = t.x.query('/ExportFile/Calibrations')
|
||
,@GroupTemplates = t.x.query('/ExportFile/GroupTemplates')
|
||
,@Groups = t.x.query('/ExportFile/Groups')
|
||
,@TestSetups = t.x.query('/ExportFile/TestSetups')
|
||
|
||
,@MMEDirections = t.x.query('/ExportFile/CustomDirections')
|
||
,@MMEFilterClasses = t.x.query('/ExportFile/MMEFilterClasses')
|
||
,@MMEFinLoc1 = t.x.query('/ExportFile/CustomFinLoc1s')
|
||
,@MMEFinLoc2 = t.x.query('/ExportFile/CustomFinLoc2s')
|
||
,@MMEFinLoc3 = t.x.query('/ExportFile/CustomFinLoc3s')
|
||
,@MMEMainLocation = t.x.query('/ExportFile/CustomMainLocs')
|
||
,@MMEPhysicalDimensions = t.x.query('/ExportFile/CustomPhysicalDimensions')
|
||
,@MMEPositions = t.x.query('/ExportFile/CustomPositions')
|
||
,@MMEPossibleChannels = t.x.query('/ExportFile/CustomChannels')
|
||
,@Users = t.x.query('/ExportFile/Users')
|
||
,@GlobalSettings = t.x.query('/ExportFile/GlobalSettings')
|
||
from @xFile.nodes('/ExportFile') t(x)
|
||
|
||
|
||
/* */
|
||
if(@CustomerDetails is not null)
|
||
begin
|
||
exec dbo.sp_DBImportCustomerDetails @CustomerDetails, @errorNumber output, @errorMessage output
|
||
select 'dbo.sp_DBImportCustomerDetails', @errorNumber , @errorMessage
|
||
end
|
||
/* */
|
||
if(@TestEngineerDetails is not null)
|
||
begin
|
||
exec dbo.sp_DBImportTestEngineerDetails @TestEngineerDetails, @errorNumber output, @errorMessage output
|
||
select 'dbo.sp_DBImportTestEngineerDetails', @errorNumber , @errorMessage
|
||
end
|
||
/* */
|
||
if(@LabDetails is not null)
|
||
begin
|
||
exec dbo.sp_DBImportLabDetails @LabDetails, @errorNumber output, @errorMessage output
|
||
select 'dbo.sp_DBImportLabDetails', @errorNumber , @errorMessage
|
||
end
|
||
/* */
|
||
if(@SensorModels is not null)
|
||
begin
|
||
exec dbo.sp_DBImportSensorModel @SensorModels, @errorNumber output, @errorMessage output
|
||
select 'dbo.sp_DBImportSensorModel', @errorNumber , @errorMessage
|
||
end
|
||
/* */
|
||
if(@Sensors is not null)
|
||
begin
|
||
exec dbo.sp_DBImportSensors @Sensors, @errorNumber output, @errorMessage output
|
||
select 'dbo.sp_DBImportSensors', @errorNumber , @errorMessage
|
||
end
|
||
|
||
/*** Sensors Calibrations *********/
|
||
if(@SensorsCalibrations is not null)
|
||
begin
|
||
exec [dbo].[sp_DBImportSensorsCalibration] @SensorsCalibrations, @errorNumber output, @errorMessage output
|
||
select 'dbo.sp_DBImportSensorsCalibration', @errorNumber , @errorMessage
|
||
end
|
||
/* */
|
||
if(@DASList is not null)
|
||
begin
|
||
exec dbo.sp_DBImportDASList @DASList, @errorNumber output, @errorMessage output
|
||
select 'dbo.sp_DBImportDASList', @errorNumber , @errorMessage
|
||
end
|
||
/* */
|
||
if(@MMEDirections is not null)
|
||
begin
|
||
exec dbo.sp_DBImportMMEDirections @MMEDirections, @errorNumber output, @errorMessage output
|
||
select 'dbo.sp_DBImportMMEDirections', @errorNumber , @errorMessage
|
||
end
|
||
/* */
|
||
if(@MMEFilterClasses is not null)
|
||
begin
|
||
exec dbo.sp_DBImportMMEFilterClasses @MMEFilterClasses, @errorNumber output, @errorMessage output
|
||
select 'dbo.sp_DBImportMMEFilterClasses', @errorNumber , @errorMessage
|
||
end
|
||
/* */
|
||
if(@MMEFinLoc1 is not null)
|
||
begin
|
||
exec dbo.sp_DBImportMMEFineLocations1 @MMEFinLoc1, @errorNumber output, @errorMessage output
|
||
select 'dbo.sp_DBImportMMEFineLocations1', @errorNumber , @errorMessage
|
||
end
|
||
/* */
|
||
if(@MMEFinLoc2 is not null)
|
||
begin
|
||
exec dbo.sp_DBImportMMEFineLocations2 @MMEFinLoc2, @errorNumber output, @errorMessage output
|
||
select 'dbo.sp_DBImportMMEFineLocations2', @errorNumber , @errorMessage
|
||
end
|
||
/* */
|
||
if(@MMEFinLoc3 is not null)
|
||
begin
|
||
exec dbo.sp_DBImportMMEFineLocations3 @MMEFinLoc3, @errorNumber output, @errorMessage output
|
||
select 'dbo.sp_DBImportMMEFineLocations3', @errorNumber , @errorMessage
|
||
end
|
||
/* */
|
||
if(@MMEMainLocation is not null)
|
||
begin
|
||
exec dbo.sp_DBImportMMEMainLocations @MMEMainLocation, @errorNumber output, @errorMessage output
|
||
select 'dbo.sp_DBImportMMEMainLocations', @errorNumber , @errorMessage
|
||
end
|
||
|
||
if(@MMEPhysicalDimensions is not null)
|
||
begin
|
||
exec dbo.sp_DBImportMMEPhysicalDimensions @MMEPhysicalDimensions, @errorNumber output, @errorMessage output
|
||
select 'dbo.sp_DBImportMMEPositions', @errorNumber , @errorMessage
|
||
end
|
||
/* */
|
||
if(@MMEPositions is not null)
|
||
begin
|
||
exec dbo.sp_DBImportMMEPositions @MMEPositions, @errorNumber output, @errorMessage output
|
||
select 'dbo.sp_DBImportMMEFilterClasses', @errorNumber , @errorMessage
|
||
end
|
||
/* */
|
||
if(@MMEPossibleChannels is not null)
|
||
begin
|
||
exec dbo.sp_DBImportMMEPossibleChannels @MMEPossibleChannels, @errorNumber output, @errorMessage output
|
||
select 'dbo.sp_DBImportMMEPossibleChannels', @errorNumber , @errorMessage
|
||
end
|
||
/* */
|
||
if(@GroupTemplates is not null)
|
||
begin
|
||
exec dbo.sp_DBImportGroupTemplate @GroupTemplates, @errorNumber output, @errorMessage output
|
||
select 'dbo.sp_DBImportGroupTemplate', @errorNumber , @errorMessage
|
||
end
|
||
/* */
|
||
if(@Groups is not null)
|
||
begin
|
||
exec dbo.sp_DBImportGroup @Groups, @errorNumber output, @errorMessage output
|
||
select 'dbo.sp_DBImportGroup', @errorNumber , @errorMessage
|
||
end
|
||
/* old and new */
|
||
if(@TestSetups is not null)
|
||
begin
|
||
exec dbo.sp_DBImportTestSetups @TestSetups, @errorNumber output, @errorMessage output
|
||
select 'dbo.sp_DBImportTestSetups', @errorNumber , @errorMessage
|
||
end
|
||
/* */
|
||
if(@Users is not null)
|
||
begin
|
||
exec dbo.sp_DBImportUsers @Users, @errorNumber output, @errorMessage output
|
||
select 'dbo.sp_DBImportUsers', @errorNumber , @errorMessage
|
||
end
|
||
/* */
|
||
if(@GlobalSettings is not null)
|
||
begin
|
||
exec dbo.sp_DBImportSettings @GlobalSettings, @errorNumber output, @errorMessage output
|
||
select 'dbo.sp_DBImportSettings', @errorNumber , @errorMessage
|
||
end
|
||
end
|
||
END
|
||
|
||
|
||
|
||
|
||
|
||
|
||
|
||
|
||
|
||
|
||
|
||
|
||
|
||
|
||
|
||
|
||
|
||
|
||
|
||
|
||
|
||
|
||
|
||
|
||
|
||
|
||
|
||
GO
|