174 lines
9.7 KiB
Transact-SQL
174 lines
9.7 KiB
Transact-SQL
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_SensorCalibrationRecordProsess]') AND type in (N'P', N'PC'))
|
||
DROP PROCEDURE [dbo].[sp_SensorCalibrationRecordProsess]
|
||
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_SensorCalibrationRecordProsess]') AND type in (N'P', N'PC'))
|
||
BEGIN
|
||
EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[sp_SensorCalibrationRecordProsess] AS'
|
||
END
|
||
GO
|
||
ALTER PROCEDURE [dbo].[sp_SensorCalibrationRecordProsess]
|
||
@SensorId int
|
||
,@SensorCalibrationsId int
|
||
,@CalibrationTypeId int
|
||
,@CalibrationRecords varchar(255)
|
||
,@errorNumber int output
|
||
,@errorMessage nvarchar(250) output
|
||
AS
|
||
BEGIN
|
||
set @errorNumber = 0
|
||
set @errorMessage = space(0)
|
||
if(@CalibrationRecords is not null and @CalibrationRecords != space(0))
|
||
begin
|
||
|
||
declare @tCalibrationRecord table(
|
||
[recordId] [int] identity(1,1)
|
||
, [SensorId] [int]
|
||
, [SensorCalibrationId] [int]
|
||
, [IsModel] [bit]
|
||
, [Poly] [nvarchar](255) NULL
|
||
, [Sensitivity] [float] NULL
|
||
, [AtCapacity] [bit] NULL
|
||
, [EngineeringUnits] [nvarchar](50) NULL
|
||
, [Excitation] [nvarchar](50) NULL
|
||
, [CapacityOutputIsBasedOn] [int] NULL
|
||
, [SensitivityUnits] [nvarchar](50)
|
||
, [ZeroPoint] [float] NULL
|
||
, [Processed] [bit])
|
||
|
||
declare @recordId int
|
||
declare @IsModel bit
|
||
declare @Poly nvarchar(255)
|
||
declare @Sensitivity float
|
||
declare @AtCapacity bit
|
||
declare @EngineeringUnits nvarchar(20)
|
||
declare @Excitation nvarchar(10)
|
||
declare @CapacityOutputIsBasedOn smallint
|
||
declare @SensitivityUnits nvarchar(10)
|
||
declare @ZeroPoint float
|
||
|
||
insert into @tCalibrationRecord
|
||
([SensorId]
|
||
,[SensorCalibrationId]
|
||
,[IsModel]
|
||
,[Poly]
|
||
,[Sensitivity]
|
||
,[AtCapacity]
|
||
,[EngineeringUnits]
|
||
,[Excitation]
|
||
,[CapacityOutputIsBasedOn]
|
||
,[SensitivityUnits]
|
||
,[ZeroPoint]
|
||
,[Processed])
|
||
select @SensorId
|
||
,@SensorCalibrationsId
|
||
,0
|
||
,Poly
|
||
,Sensitivity
|
||
,AtCapacity
|
||
,EngineeringUnits
|
||
,Excitation
|
||
,CapacityOutputIsBasedOn
|
||
,SensitivityUnits
|
||
,ZeroPoint
|
||
, 0
|
||
from dbo.foo_SplitSensorCalibrationRecord(@CalibrationRecords, '__x__')
|
||
|
||
while (select Count(*) From @tCalibrationRecord Where Processed = 0) > 0
|
||
Begin
|
||
begin try
|
||
begin transaction tCalibrationRecord
|
||
select top 1
|
||
@recordId = [recordId]
|
||
,@Poly = ltrim(rtrim(isnull([Poly], space(0))))
|
||
,@IsModel = isnull(@IsModel, 0)
|
||
,@Sensitivity = [Sensitivity]
|
||
,@AtCapacity = [AtCapacity]
|
||
,@EngineeringUnits = [EngineeringUnits]
|
||
,@Excitation = [Excitation]
|
||
,@CapacityOutputIsBasedOn = [CapacityOutputIsBasedOn]
|
||
,@SensitivityUnits = [SensitivityUnits]
|
||
,@ZeroPoint = [ZeroPoint]
|
||
from @tCalibrationRecord where Processed = 0
|
||
|
||
declare @SensorCalibrationRecordId int
|
||
|
||
exec [dbo].[sp_SensorCalibrationRecordInsert] @SensorId
|
||
, @SensorCalibrationsId
|
||
, @IsModel
|
||
, @Sensitivity
|
||
, @AtCapacity
|
||
, @EngineeringUnits
|
||
, @Excitation
|
||
, @CapacityOutputIsBasedOn
|
||
, @SensitivityUnits
|
||
, @ZeroPoint
|
||
, @SensorCalibrationRecordId output
|
||
, @errorNumber output
|
||
, @errorMessage output
|
||
if(@SensorCalibrationRecordId != 0 and @Poly != space(0))
|
||
begin
|
||
if(@CalibrationTypeId = 2)
|
||
begin
|
||
insert into dbo.SensorCalibrationRecordPolynomial ([SensorCalibrationRecordId], [c0], [c1], [c2], [c3], [c4], [c5], [c6], [S], [mV])
|
||
select @SensorCalibrationRecordId, c0, c1, c2, c3, c4, c5, c6, S, mV from dbo.foo_SensorCalibrationRecordPolynomial(@Poly, 'x_Separator_x')
|
||
end
|
||
else if(@CalibrationTypeId = 3)
|
||
begin
|
||
|
||
insert into dbo.SensorCalibrationRecordIRTracc ([SensorCalibrationRecordId], [Slope], [Intercept], [Exponent])
|
||
select @SensorCalibrationRecordId, Slope, Intercept, Exponent from dbo.foo_SensorCalibrationRecordIRTracc(@Poly, 'x')
|
||
end
|
||
end
|
||
update @tCalibrationRecord set Processed = 1 where @recordId = @recordId
|
||
commit transaction tCalibrationRecord
|
||
|
||
end try
|
||
begin catch
|
||
rollback transaction tCalibrationRecord
|
||
declare @message nvarchar(2048),
|
||
@severity int,
|
||
@state int
|
||
|
||
set @message = error_message()
|
||
set @severity = error_severity()
|
||
set @state = error_state()
|
||
RAISERROR(@message,@severity,@state, '[sp_SensorCalibrationRecordProsess]')
|
||
end catch;
|
||
End
|
||
end
|
||
|
||
End
|
||
|
||
|
||
|
||
|
||
|
||
|
||
|
||
|
||
|
||
|
||
|
||
|
||
|
||
|
||
|
||
|
||
|
||
|
||
|
||
|
||
|
||
|
||
|
||
|
||
|
||
|
||
|
||
|
||
GO
|