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

		declare @tSensorModel table
				([recId]				[int] IDENTITY(1,1),
				[Model]					[nvarchar](50) NOT NULL,
				[Manufacturer]			[nvarchar](50) NOT NULL,
				[UserPartNumber]		[nvarchar](50) NOT NULL,
				[Capacity]				[float] NOT NULL,
				[OffsetToleranceLow]	[float] NOT NULL,
				[OffsetToleranceHigh]	[float] NOT NULL,
				[MeasurementUnit]		[nvarchar](50) NOT NULL,
				[Bridge]				[smallint] NOT NULL,
				[Shunt]					[smallint] NOT NULL,
				[BridgeResistance]		[float] NOT NULL,
				[FilterClass]			[nvarchar](50) NOT NULL,
				[UniPolar]				[bit] NOT NULL,
				[IgnoreRange]			[bit] NOT NULL,
				[CouplingMode]			[smallint] NOT NULL,
				[Version]				[int] NOT NULL,
				[RangeLow]				[float] NOT NULL,
				[RangeAve]				[float] NOT NULL,
				[RangeHigh]				[float] NOT NULL,
				[LastModified]			[datetime] NOT NULL,
				[ModifiedBy]			[nvarchar](50) NOT NULL,
				[LocalOnly]				[bit] NOT NULL,
				[NumberOfAxes]			[smallint] NOT NULL,
				[CalInterval]			[int] NOT NULL,
				[AxisNumber]			[smallint] NOT NULL,
				[Polarity]				[nvarchar](10) NOT NULL,
				[Invert]				[bit] NOT NULL,
				[CheckOffset]			[bit] NOT NULL,
				[CalibrationRecord]		[nvarchar](255) NOT NULL,
				[ISOCode]				[nvarchar](20) NOT NULL,
				[SupportedExcitation]	[nvarchar](255) NOT NULL,
				[InitialEU]				[float] NULL,
				[Processed]				[bit])

		insert into @tSensorModel
					([Model]
					,[Manufacturer]
					,[UserPartNumber]
					,[Capacity]
					,[OffsetToleranceLow]
					,[OffsetToleranceHigh]
					,[MeasurementUnit]
					,[Bridge]
					,[Shunt]
					,[BridgeResistance]
					,[FilterClass]
					,[UniPolar]
					,[IgnoreRange]
					,[CouplingMode]
					,[Version]
					,[RangeLow]
					,[RangeAve]
					,[RangeHigh]
					,[LastModified]
					,[ModifiedBy]
					,[LocalOnly]
					,[NumberOfAxes]
					,[CalInterval]
					,[AxisNumber]
					,[Polarity]
					,[Invert]
					,[CheckOffset]
					,[CalibrationRecord]
					,[ISOCode]
					,[SupportedExcitation]
					,[InitialEU]
					,[Processed])
		select
						t.x.value('@Model', 'nvarchar(50)')
					, t.x.value('@Manufacturer', 'nvarchar(50)')
					, t.x.value('@UserPartNumber', 'nvarchar(50)')
					, t.x.value('@Capacity', 'float')
					, t.x.value('@OffsetToleranceLow', 'float')
					, t.x.value('@OffsetToleranceHigh', 'float')
					, t.x.value('@MeasurementUnit', 'nvarchar(50)')
					, t.x.value('@Bridge', 'smallint')
					, t.x.value('@Shunt', 'smallint')
					, t.x.value('@BridgeResistance', 'float')
					, t.x.value('@FilterClass', 'nvarchar(50)')
					, t.x.value('@UniPolar', 'bit')
					, t.x.value('@IgnoreRange', 'bit')
					, t.x.value('@CouplingMode', 'smallint')
					, t.x.value('@Version', 'int')
					, t.x.value('@RangeLow', 'float')
					, t.x.value('@RangeAve', 'float')
					, t.x.value('@RangeHigh', 'float')
					, t.x.value('@LastModified', 'datetime')
					, t.x.value('@ModifiedBy', 'nvarchar(50)')
					, t.x.value('@LocalOnly', 'bit')
					, t.x.value('@NumberOfAxes', 'smallint')
					, t.x.value('@CalInterval', 'int')
					, t.x.value('@AxisNumber', 'smallint')
					, t.x.value('@Polarity', 'nvarchar(10)')
					, t.x.value('@Invert', 'bit')
					, t.x.value('@CheckOffset', 'bit')
					, t.x.value('@CalibrationRecord', 'nvarchar(255)')
					, t.x.value('@ISOCode', 'nvarchar(20)')
					, t.x.value('@SupportedExcitation',	'nvarchar(255)')
					, t.x.value('@InitialEU', 'float')
					,0
		from @SensorModel.nodes('/SensorModels/SensorModel') t(x)
			
		while (Select Count(*) From @tSensorModel Where Processed = 0) > 0
		Begin
			begin try
				begin transaction tSensorModel
				declare @recId			int
				declare @SensorModelId	int
				,@Model					nvarchar(50)
				,@Manufacturer			nvarchar(50)
				,@UserPartNumber		nvarchar(50)
				,@Capacity				float
				,@OffsetToleranceLow	float
				,@OffsetToleranceHigh	float
				,@MeasurementUnit		nvarchar(50)
				,@Bridge				smallint
				,@Shunt					smallint
				,@BridgeResistance		float
				,@FilterClass			nvarchar(50)
				,@UniPolar				bit
				,@IgnoreRange			bit
				,@CouplingMode			smallint
				,@Version				int
				,@RangeLow				float
				,@RangeAve				float
				,@RangeHigh				float
				,@LastModified			datetime
				,@LastModifiedBy		nvarchar(50)
				,@LocalOnly				bit
				,@NumberOfAxes			smallint
				,@CalInterval			int
				,@AxisNumber			smallint
				,@Polarity				nvarchar(10)
				,@Invert				bit
				,@CheckOffset			bit
				,@CalibrationRecord		nvarchar(255)
				,@ISOCode				nvarchar(20)
				,@SupportedExcitation	nvarchar(255)
				,@InitialEU				float

				select top 1
								  @recId				= recId
								, @Model				= [Model]
								, @Manufacturer			= [Manufacturer]			
								, @UserPartNumber		= [UserPartNumber]		
								, @Capacity				= [Capacity]				
								, @OffsetToleranceLow	= [OffsetToleranceLow]	
								, @OffsetToleranceHigh	= [OffsetToleranceHigh]	
								, @MeasurementUnit		= [MeasurementUnit]		
								, @Bridge				= [Bridge]				
								, @Shunt				= [Shunt]					
								, @BridgeResistance		= [BridgeResistance]		
								, @FilterClass			= [FilterClass]			
								, @UniPolar				= [UniPolar]				
								, @IgnoreRange			= [IgnoreRange]			
								, @CouplingMode			= [CouplingMode]			
								, @Version				= [Version]				
								, @RangeLow				= [RangeLow]				
								, @RangeAve				= [RangeAve]				
								, @RangeHigh			= [RangeHigh]				
								, @LastModified			= getdate() /* [LastModified]*/
								, @LastModifiedBy		= 'DBImport' /* [LastModifiedBy] */			
								, @LocalOnly			= [LocalOnly]				
								, @NumberOfAxes			= [NumberOfAxes]			
								, @CalInterval			= [CalInterval]			
								, @AxisNumber			= [AxisNumber]			
								, @Polarity				= [Polarity]				
								, @Invert				= [Invert]				
								, @CheckOffset			= [CheckOffset]			
								, @CalibrationRecord	= [CalibrationRecord]		
								, @ISOCode				= [ISOCode]				
								, @SupportedExcitation	= [SupportedExcitation]	
								, @InitialEU			= [InitialEU]	
			   from @tSensorModel where [Processed] = 0			

			   exec [dbo].[sp_SensorModelsUpdateInsert]    @Model
														 , @Manufacturer			
														 , @UserPartNumber		
														 , @Capacity				
														 , @OffsetToleranceLow	
														 , @OffsetToleranceHigh	
														 , @MeasurementUnit		
														 , @Bridge				
														 , @Shunt					
														 , @BridgeResistance		
														 , @FilterClass			
														 , @UniPolar				
														 , @IgnoreRange			
														 , @CouplingMode			
														 , @Version				
														 , @RangeLow				
														 , @RangeAve				
														 , @RangeHigh				
														 , @LastModified			
														 , @LastModifiedBy			
														 , @LocalOnly				
														 , @NumberOfAxes			
														 , @CalInterval			
														 , @AxisNumber			
														 , @Polarity				
														 , @Invert				
														 , @CheckOffset			
														 , @CalibrationRecord		
														 , @ISOCode				
														 , @SupportedExcitation	
														 , @InitialEU
														 , @SensorModelId			output	
														 , @errorNumber				output
														 , @errorMessage			output 														 			

				update dbo.SensorsAnalog set SensorModelId = sm.SensorModelId from dbo.SensorModels sm where dbo.SensorsAnalog.Model = sm.Model

				update @tSensorModel set Processed = 1 where recId = @recId

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



























GO