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