DP44/DataPRO_sql/dbo.sp_DBImportSensorsSquib.StoredProcedure.sql

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

	declare @tSensorsSquib table
				([SerialNumber] [nvarchar](50),
				[BypassCurrentFilter] [bit],
				[BypassVoltageFilter] [bit],
				[DelayMS] [float],
				[DurationMS] [float],
				[FireMode] [smallint],
				[ISOCode] [nvarchar](50),
				[MeasurementType] [smallint],
				[SquibOutputCurrent] [float],
				[SquibToleranceLow] [float],
				[SquibToleranceHigh] [float],
				[LimitDuration] [bit],
				[ArticleId] [nvarchar](50),
				[LocalOnly] [bit],
				[Version] [int],
				[LastModified] [datetime],
				[LastModifiedBy] [nvarchar](50),
				[UserValue1] [nvarchar](255),
				[UserValue2] [nvarchar](255),
				[UserValue3] [nvarchar](255),
				[UserTags] [varbinary](max),
				[Processed]	[bit])

	insert into @tSensorsSquib
			(SerialNumber
			, BypassCurrentFilter
			, BypassVoltageFilter
			, DelayMS
			, DurationMS
			, FireMode
			, ISOCode
			, MeasurementType
			, SquibOutputCurrent
			, SquibToleranceLow
			, SquibToleranceHigh
			, LimitDuration
			, ArticleId
			, LocalOnly
			, [Version]
			, LastModified
			, LastModifiedBy
			, UserValue1
			, UserValue2
			, UserValue3
			, UserTags
			,Processed)
	select
			t.x.value('@SquibDescription', 'varchar(50)')		as SerialNumber
		, t.x.value('@BypassFilter', 'bit')						as BypassCurrentFilter
		, t.x.value('@BypassVoltageFilter', 'bit')				as BypassVoltageFilter
		, t.x.value('@DelayMS', 'float')						as DelayMS
		, t.x.value('@DurationMS', 'float')						as DurationMS
		, t.x.value('@FireMode', 'varchar(50)')					as FireMode
		, t.x.value('@ISOCode', 'varchar(50)')					as ISOCode
		, t.x.value('@MeasurementType', 'varchar(50)')			as MeasurementType
		, t.x.value('@SquibOutputCurrent', 'float')				as SquibOutputCurrent
		, t.x.value('@SquibToleranceLow', 'float')				as SquibToleranceLow
		, t.x.value('@SquibToleranceHigh', 'float')				as SquibToleranceHigh
		, t.x.value('@LimitDuration', 'bit')					as LimitDuration
		, t.x.value('@ArticleId', 'varchar(max)')				as SquibArticleId
		, t.x.value('@LocalOnly', 'bit')						as LocalOnly
		, t.x.value('@Version', 'int')							as Version
		, getdate()												as LastModified /* t.x.value('@LastModified', 'datetime') */
		, 'DBImport'											as ModifiedBy	/* t.x.value('@ModifiedBy', 'varchar(50)') */

		, t.x.value('@UserValue1', 'varchar(50)')				as UserValue1
		, t.x.value('@UserValue2', 'varchar(50)')				as UserValue2
		, t.x.value('@UserValue3', 'varchar(50)')				as UserValue3
		, t.x.value('@UserTags', 'varbinary(max)')				as UserTags
		, 0
	from @SquibSensors.nodes('/Sensors/SensorData') t(x) OPTION (OPTIMIZE FOR ( @SquibSensors = NULL ))

	while (Select Count(*) From @tSensorsSquib Where Processed = 0) > 0
	Begin
		begin try
			begin transaction tSensorsSquib

			declare     @SerialNumber			nvarchar(50)
					   ,@BypassCurrentFilter	bit
					   ,@BypassVoltageFilter	bit
					   ,@DelayMS				float
					   ,@DurationMS				float
					   ,@FireMode				smallint
					   ,@ISOCode				nvarchar(50)
					   ,@MeasurementType		smallint
					   ,@SquibOutputCurrent		float
					   ,@SquibToleranceLow		float
					   ,@SquibToleranceHigh		float
					   ,@LimitDuration			bit
					   ,@ArticleId				nvarchar(50)
					   ,@LocalOnly				bit
					   ,@Version				int
					   ,@LastModified			datetime
					   ,@LastModifiedBy			nvarchar(50)
					   ,@UserValue1				nvarchar(255)
					   ,@UserValue2				nvarchar(255)
					   ,@UserValue3				nvarchar(255)
					   ,@UserTags				varbinary(max)
					   ,@new_id					int				
			
			select top 1
				 @SerialNumber				= SerialNumber		
				,@BypassCurrentFilter		= BypassCurrentFilter
				,@BypassVoltageFilter		= BypassVoltageFilter
				,@DelayMS					= DelayMS			
				,@DurationMS				= DurationMS			
				,@FireMode					= FireMode			
				,@ISOCode					= ISOCode			
				,@MeasurementType			= MeasurementType	
				,@SquibOutputCurrent		= SquibOutputCurrent	
				,@SquibToleranceLow			= SquibToleranceLow	
				,@SquibToleranceHigh		= SquibToleranceHigh	
				,@LimitDuration				= LimitDuration		
				,@ArticleId					= ArticleId			
				,@LocalOnly					= LocalOnly			
				,@Version					= Version			
				,@LastModified				= LastModified		
				,@LastModifiedBy			= LastModifiedBy		
				,@UserValue1				= UserValue1			
				,@UserValue2				= UserValue2			
				,@UserValue3				= UserValue3			
				,@UserTags					= UserTags			
			from @tSensorsSquib where Processed = 0

			exec dbo.sp_SensorsSquibUpdateInsert @SerialNumber
												,@BypassCurrentFilter	
												,@BypassVoltageFilter	
												,@DelayMS				
												,@DurationMS			
												,@FireMode				
												,@ISOCode				
												,@MeasurementType		
												,@SquibOutputCurrent	
												,@SquibToleranceLow		
												,@SquibToleranceHigh	
												,@LimitDuration			
												,@ArticleId				
												,@LocalOnly				
												,@Version				
												,@LastModified			
												,@LastModifiedBy		
												,@UserValue1			
												,@UserValue2			
												,@UserValue3			
												,@UserTags
												,@new_id					output 	
												,@errorNumber				output
												,@errorMessage				output

			update @tSensorsSquib set Processed = 1 where SerialNumber = @SerialNumber
			commit transaction tSensorsSquib
		end try
		begin catch
			set @errorNumber = error_number()
			set @errorMessage = error_message()
			rollback transaction tSensorsSquib
		end catch;
	end
END



























GO