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

	declare @tSensorsDigitalOut table (
				[SerialNumber] [nvarchar](50),
				[DelayMS] [float],
				[DurationMS] [smallint],
				[OutputMode] [smallint],
				[LimitDuration] [bit],
				[LastModified] [datetime],
				[LastModifiedBy] [nvarchar](50),
				[Version] [int],
				[LocalOnly] [bit],
				[DurationMSFloat] [float],
				[UserTags] [varbinary](max),
				[Processed]	[bit])

	insert into @tSensorsDigitalOut
					(SerialNumber
					, DelayMS
					, DurationMS
					, OutputMode
					, LimitDuration
					, LastModified
					, LastModifiedBy
					, [Version]
					, LocalOnly
					, DurationMSFloat
					, UserTags
					,Processed)
		select 
				t.x.value('@ChannelDescription', 'varchar(50)')	as SerialNumber
			, t.x.value('@DelayMS', 'float')						as DelayMS
			, t.x.value('@DurationMS', 'smallint')					as DurationMS
			, t.x.value('@OutputMode', 'smallint')					as OutputMode
			, t.x.value('@LimitDuration', 'bit')					as LimitDuration

			, getdate()														as LastModified /* t.x.value('@LastModified', 'datetime') */
			, 'DBImport'													as ModifiedBy	/* t.x.value('@ModifiedBy', 'varchar(50)') */
			, t.x.value('@Version', 'int')									as Version
			, t.x.value('@LocalOnly', 'bit')								as LocalOnly
			, t.x.value('@DurationMSFloat', 'float')						as DurationMSFloat /* ???? */
			, t.x.value('@UserTags', 'varbinary(max)')						as UserTags
			, 0
		from @DigitalOutSensors.nodes('/Sensors/SensorData') t(x) OPTION (OPTIMIZE FOR ( @DigitalOutSensors = NULL ))
			
		while (Select Count(*) From @tSensorsDigitalOut Where Processed = 0) > 0
		Begin
		begin try
			begin transaction tSensorsDigitalOut

	
			declare @SerialNumber			nvarchar(50) = null
				   ,@DelayMS				float
				   ,@DurationMS				smallint
				   ,@DurationMSFloat		float
				   ,@OutputMode				smallint
				   ,@LimitDuration			bit
				   ,@LastModified			datetime
				   ,@LastModifiedBy			nvarchar(50)
				   ,@Version				int
				   ,@UserTags				varbinary(max)
				   ,@new_id					int 

			select top 1 @SerialNumber			= SerialNumber	
						,@DelayMS				= DelayMS		
						,@DurationMS			= DurationMS		
						,@DurationMSFloat		= DurationMSFloat
						,@OutputMode			= OutputMode		
						,@LimitDuration			= LimitDuration	
						,@LastModified			= LastModified	
						,@LastModifiedBy		= LastModifiedBy	
						,@Version				= [Version]
						,@UserTags				= UserTags		
			from @tSensorsDigitalOut where Processed = 0

			exec dbo.sp_SensorsDigitalOutUpdateInsert	 @SerialNumber
														,@DelayMS		
														,@DurationMS		
														,@DurationMSFloat
														,@OutputMode		
														,@LimitDuration	
														,@LastModified	
														,@LastModifiedBy	
														,@Version		
														,@UserTags		
														,@new_id					output
														,@errorNumber				output
														,@errorMessage				output 

			update @tSensorsDigitalOut set Processed = 1 where SerialNumber = @SerialNumber

			commit transaction tSensorsDigitalOut

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



























GO