274 lines
17 KiB
Plaintext
274 lines
17 KiB
Plaintext
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
|