DP44/DataPRO_sql/dbo.sp_DBImportDASList.StoredProcedure.sql

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

	set @errorNumber = 0 
	set @errorMessage = space(0)
	declare @tDAS table (
			[DASId]							[int]				NULL,
			[SerialNumber]					[nvarchar](50)		NULL,
			[Type]							[int]				NULL,
			[MaxModules]					[int]				NULL,
			[MaxMemory]						[bigint]			NULL,
			[MaxSampleRate]					[decimal](18, 0)	NULL,
			[MinSampleRate]					[decimal](18, 0)	NULL,
			[FirmwareVersion]				[nvarchar](50)		NULL,
			[CalDate]						[datetime]			NULL,
			[ProtocolVersion]				[int]				NULL,
			[LastModified]					[datetime]			NULL,
			[LastModifiedBy]				[nvarchar](50)		NULL,
			[Version]						[int]				NULL,
			[LocalOnly]						[bit]				NULL,
			[LastUsed]						[datetime]			NULL,
			[LastUsedBy]					[nvarchar](50)		NULL,
			[Connection]					[nvarchar](50)		NULL,
			[Channels]						[int]				NULL,
			[Position]						[nvarchar](50)		NULL,
			[ChannelTypes]					[nvarchar](255)		NULL,
			[Reprogramable]					[bit]				NULL,
			[Reconfigurable]				[bit]				NULL,
			[IsModule]						[bit]				NULL,
			[PositionOnDistributor]			[smallint]			NULL,
			[PositionOnChain]				[smallint]			NULL,
			[Port]							[smallint]			NULL,
			[ParentDAS]						[nvarchar](50)		NULL,
			[DASChannels]					[xml]				NULL,
			[Processed]						[bit]				NULL)

	declare  @DASId					int
			,@SerialNumber          nvarchar(50) 
			,@Type					int
			,@MaxModules			int
			,@MaxMemory				bigint
			,@MaxSampleRate			decimal(18,0)
			,@MinSampleRate			decimal(18,0)
			,@FirmwareVersion		nvarchar(50)
			,@CalDate				datetime
			,@ProtocolVersion		int
			,@LastModified			datetime
			,@LastModifiedBy		nvarchar(50)
			,@Version				int
			,@LocalOnly				bit
			,@LastUsed				datetime
			,@LastUsedBy			nvarchar(50)
			,@Connection			nvarchar(50)
			,@Channels				int
			,@Position				nvarchar(50)
			,@ChannelTypes			nvarchar(255)
			,@Reprogramable			bit
			,@Reconfigurable		bit
			,@IsModule				bit
			,@PositionOnDistributor	smallint
			,@PositionOnChain		smallint	
			,@Port					smallint	
			,@ParentDAS				nvarchar(50)
			,@new_id				int

	insert into @tDAS
			([DASId]
			,[SerialNumber]			
			,[Type]					
			,[MaxModules]			
			,[MaxMemory]				
			,[MaxSampleRate]			
			,[MinSampleRate]			
			,[FirmwareVersion]		
			,[CalDate]				
			,[ProtocolVersion]		
			,[LastModified]			
			,[LastModifiedBy]		
			,[Version]				
			,[LocalOnly]				
			,[LastUsed]				
			,[LastUsedBy]			
			,[Connection]			
			,[Channels]				
			,[Position]				
			,[ChannelTypes]			
			,[Reprogramable]			
			,[Reconfigurable]		
			,[IsModule]
			,[PositionOnDistributor]	
			,[PositionOnChain]		
			,[Port]					
			,[ParentDAS]
			,[DASChannels]
			,[Processed])				
	select 
			 isnull(t.x.value('@DASId', 'int'), 0)
			,t.x.value('@SerialNumber', 'nvarchar(50)')
			,t.x.value('@Type', 'int')
			,t.x.value('@MaxModules', 'int')
			,t.x.value('@MaxMemory', 'bigint')
			,t.x.value('@MaxSampleRate', 'decimal(18, 0)')
			,t.x.value('@MinSampleRate', 'decimal(18, 0)')
			,t.x.value('@FirmwareVersion', 'nvarchar(50)')
			,t.x.value('@CalDate', 'datetime')
			,t.x.value('@ProtocolVersion', 'int')
			,t.x.value('@LastModified', 'datetime')
			,t.x.value('@LastModifiedBy', 'nvarchar(50)')
			,t.x.value('@Version', 'int')							
			,t.x.value('@LocalOnly', 'bit')							
			,t.x.value('@LastUsed', 'datetime')							
			,t.x.value('@LastUsedBy', 'nvarchar(50)')							
			,t.x.value('@Connection', 'nvarchar(50)')							
			,t.x.value('@Channels', 'int')							
			,t.x.value('@Position', 'nvarchar(50)')							
			,t.x.value('@ChannelTypes', 'nvarchar(255)')							
			,t.x.value('@Reprogramable', 'bit')							
			,t.x.value('@Reconfigurable', 'bit')							
			,t.x.value('@IsModule', 'bit')							
			,t.x.value('@PositionOnDistributor', 'smallint')							
			,t.x.value('@PositionOnChain', 'smallint')							
			,t.x.value('@Port', 'smallint')							
			,t.x.value('@ParentDAS', 'nvarchar(50)')
			,t.x.query('./DASChannels')
			,0							
	from @DASList.nodes('/DASList/DASHardware') t(x)
	where ltrim(rtrim(isnull(t.x.value('@SerialNumber', 'nvarchar(50)'), space(0)))) != space(0)
	OPTION (OPTIMIZE FOR ( @DASList = NULL ))

	while (Select Count(*) From @tDAS Where Processed = 0) > 0
	Begin
		begin try
			begin transaction tDAS
			declare @DASSerialNumber	nvarchar(50)
			declare @DASChannels		xml
			set @DASSerialNumber = (select top 1 SerialNumber from @tDAS t where t.[Processed] = 0)
						
			select top 1
					 @DASId					= [DASId]
					,@SerialNumber			= [SerialNumber]			
					,@Type					= [Type]					
					,@MaxModules			= [MaxModules]				
					,@MaxMemory				= [MaxMemory]				
					,@MaxSampleRate			= [MaxSampleRate]			
					,@MinSampleRate			= [MinSampleRate]			
					,@FirmwareVersion		= [FirmwareVersion]			
					,@CalDate				= [CalDate]					
					,@ProtocolVersion		= [ProtocolVersion]			
					,@LastModified			= getdate() /* [LastModified]*/
					,@LastModifiedBy		= 'DBImport' /* [LastModifiedBy] */
					,@Version				= [Version]					
					,@LocalOnly				= [LocalOnly]				
					,@LastUsed				= [LastUsed]				
					,@LastUsedBy			= [LastUsedBy]				
					,@Connection			= [Connection]				
					,@Channels				= [Channels]				
					,@Position				= [Position]				
					,@ChannelTypes			= [ChannelTypes]			
					,@Reprogramable			= [Reprogramable]			
					,@Reconfigurable		= [Reconfigurable]			
					,@IsModule				= [IsModule]				
					,@PositionOnDistributor	= [PositionOnDistributor]	
					,@PositionOnChain		= [PositionOnChain]			
					,@Port					= [Port]					
					,@ParentDAS				= [ParentDAS]
					,@DASChannels			= [DASChannels]
			from @tDAS t where t.[SerialNumber] = @DASSerialNumber
			
			exec sp_DASUpdateInsert  @DASId
									,@SerialNumber
									,@Type					
									,@MaxModules			
									,@MaxMemory				
									,@MaxSampleRate			
									,@MinSampleRate			
									,@FirmwareVersion		
									,@CalDate				
									,@ProtocolVersion		
									,@LastModified			
									,@LastModifiedBy		
									,@Version				
									,@LocalOnly				
									,@LastUsed				
									,@LastUsedBy			
									,@Connection			
									,@Channels				
									,@Position				
									,@ChannelTypes			
									,@Reprogramable			
									,@Reconfigurable		
									,@IsModule				
									,@PositionOnDistributor	
									,@PositionOnChain		
									,@Port					
									,@ParentDAS	
									,@DASId						output	
									,@errorNumber				output
									,@errorMessage				output 	
			
			delete from dbo.DASChannels where DASId = @DASId

			insert into dbo.DASChannels
					([DASId]
					,[ChannelIdx]
					,[SupportedBridges]
					,[SupportedExcitations]
					,[DASDisplayOrder]
					,[LocalOnly]
					,[SupportedDigitalInputModes]
					,[SupportedSquibFireModes]
					,[SupportedDigitalOutputModes]
					,[ModuleSerialNumber]
					,[SettingId]
					,[ModuleArrayIndex])
					select		 isnull(t.x.value('@DASId', 'int'), 0)
						,t.x.value('@ChannelIdx','int')
						,t.x.value('@SupportedBridges','int')
						,t.x.value('@SupportedExcitations','int')
						,t.x.value('@DASDisplayOrder','int')
						,t.x.value('@LocalOnly','bit')
						,t.x.value('@SupportedDigitalInputModes'	,'int')
						,t.x.value('@SupportedSquibFireModes','int')
						,t.x.value('@SupportedDigitalOutputModes','int')
						,t.x.value('@ModuleSerialNumber','nvarchar(16)')
						,0
						,t.x.value('@ModuleArrayIndex','int')
			from @DASChannels.nodes('/DASChannels/DASChannel') t(x)
			OPTION (OPTIMIZE FOR ( @DASChannels = NULL ))

			update @tDAS set [Processed] = 1 where SerialNumber = @DASSerialNumber 

			commit transaction tDAS
		end try
		begin catch
			set @errorMessage	= error_message()
			set @errorNumber	= error_number()

			rollback transaction tDAS
		end catch;
	end
END


























GO