163 lines
9.8 KiB
Transact-SQL
163 lines
9.8 KiB
Transact-SQL
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
|