DP44/DataPRO_sql/dbo.sp_DBImportSensorsCalibration.StoredProcedure.sql

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

	declare @tSensorCalibrations table (
					[recordId]					[int] identity(1,1)
				,[SensorId]					[int] 
				,[CalibrationDate]			[datetime]
				,[Username]					[nvarchar](50)
				,[LocalOnly]				[bit] 
				,[NonLinear]				[bit] 
				,[CalibrationRecords]		[nvarchar](255)
				,[ModifyDate]				[datetime]
				,[IsProportional]			[bit] 
				,[RemoveOffset]				[bit] 
				,[ZeroMethod]				[nvarchar](255) 
				,[CertificationDocuments]	[nvarchar](2048) 
				,[InitialOffset]			[nvarchar](50) 
				,[Processed]				bit)

		insert into @tSensorCalibrations
		select 
			  z.SensorId
			, z.CalibrationDate
			, z.Username
			, z.LocalOnly
			, z.NonLinear
			, z.CalibrationRecords
			, z.ModifyDate
			, z.IsProportional
			, z.RemoveOffset
			, z.ZeroMethod
			, z.CertificationDocuments
			, z.InitialOffset
			,0
		from 
		(select 
			dbo.foo_IdGetSensor(t.x.value('@SerialNumber', 'varchar(50)'))		as SensorId
			, t.x.value('@CalibrationDate', 'datetime')							as CalibrationDate
			, t.x.value('@Username', 'varchar(50)')								as Username
			, t.x.value('@LocalOnly', 'bit')										as LocalOnly
			, t.x.value('@NonLinear', 'bit')										as NonLinear
			, t.x.value('@CalibrationRecords', 'varchar(255)')					as CalibrationRecords
			, t.x.value('@ModifyDate', 'datetime')								as ModifyDate
			, t.x.value('@IsProportional', 'bit')									as IsProportional
			, t.x.value('@RemoveOffset', 'bit')									as RemoveOffset
			, t.x.value('@ZeroMethod', 'varchar(255)')							as ZeroMethod
			, t.x.value('@CertificationDocuments', 'varchar(2048)')				as CertificationDocuments
			, t.x.value('@InitialOffset', 'varchar(50)')							as InitialOffset
		from @SensorsCalibrations.nodes('/Calibrations/SensorCalibration') t(x) where t.x.value('@SerialNumber', 'varchar(50)') != '') z 
		OPTION (OPTIMIZE FOR ( @SensorsCalibrations = NULL ))

		while (select count(*) From @tSensorCalibrations Where Processed = 0) > 0
		Begin
		begin try
			begin transaction tSensorCalibrations
				declare @recordId				int
				declare @SensorCalibrationsId	int
				declare @SensorId				int
				declare @CalibrationDate		datetime
				declare @Username				nvarchar(50)
				declare @LocalOnly				bit
				declare @NonLinear				bit 
				declare @CalibrationRecords		nvarchar(255)
				declare @ModifyDate				datetime
				declare @IsProportional			bit
				declare @RemoveOffset			bit
				declare @ZeroMethod				nvarchar(255) 
				declare @CertificationDocuments	nvarchar(2048)
				declare @InitialOffset			nvarchar(50) 
				declare @Processed				bit

				select top 1 @recordId				= [recordId]
							,@SensorId				= [SensorId]					
							,@CalibrationDate		= [CalibrationDate]			
							,@Username				= [Username]					
							,@LocalOnly				= [LocalOnly]				
							,@NonLinear				= [NonLinear]
							,@CalibrationRecords    = ltrim(rtrim(isnull([CalibrationRecords], space(0))))
							,@ModifyDate			= [ModifyDate]				
							,@IsProportional		= [IsProportional]			
							,@RemoveOffset			= [RemoveOffset]				
							,@ZeroMethod			= [ZeroMethod]				
							,@CertificationDocuments= [CertificationDocuments]	
							,@InitialOffset			= [InitialOffset]			
				from @tSensorCalibrations  Where Processed = 0

				if (not exists(select * from SensorCalibrations where SensorId = @SensorId and CalibrationDate = @CalibrationDate))
				begin
					exec sp_SensorCalibrationsInsert @SensorId
													,@CalibrationDate		
													,@Username				
													,@LocalOnly				
													,@NonLinear	
													,@ModifyDate				
													,@IsProportional			
													,@RemoveOffset			
													,@ZeroMethod				
													,@CertificationDocuments	
													,@InitialOffset
													,@CalibrationRecords
													,@SensorCalibrationsId output, @errorNumber output, @errorMessage output 


				end
				update @tSensorCalibrations set Processed = 1 where recordId = @recordId
	
				commit transaction tSensorCalibrations

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



























GO