DP44/DataPRO_sql/dbo.sp_SensorsSquibUpdateInsert.StoredProcedure.sql

<EFBFBD><EFBFBD>IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_SensorsSquibUpdateInsert]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_SensorsSquibUpdateInsert]
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_SensorsSquibUpdateInsert]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[sp_SensorsSquibUpdateInsert] AS' 
END
GO
ALTER PROCEDURE [dbo].[sp_SensorsSquibUpdateInsert]
	@Id											INT = NULL,
	@SerialNumber								NVARCHAR (255),
	@BypassCurrentFilter						BIT,
	@BypassVoltageFilter						BIT,
	@DelayMS									FLOAT,
	@DurationMS									FLOAT,
	@FireMode									SMALLINT,
	@IsoCode									NVARCHAR (50),
	@IsoChannelName								NVARCHAR (255),
	@UserCode									NVARCHAR (50),
	@UserChannelName							NVARCHAR (255),
	@MeasurementType							SMALLINT,
	@SquibOutputCurrent							FLOAT,
	@SquibToleranceLow							FLOAT,
	@SquibToleranceHigh							FLOAT,
	@LimitDuration								BIT,
	@DefineDelayInTest					        BIT,
	@ArticleId									NVARCHAR (50),
	@Version									INT,
	@LastModified								DATETIME,
	@LastModifiedBy								NVARCHAR (50),
	@UserValue1									NVARCHAR (255),
	@UserValue2									NVARCHAR (255),
	@UserValue3									NVARCHAR (255),
	@UserTags									VARBINARY (MAX),
	@Broken										BIT,
	@DoNotUse									BIT,
	@new_id										INT OUTPUT,
	@errorNumber								INT OUTPUT,
	@errorMessage								NVARCHAR (255) OUTPUT
AS
BEGIN
	SET NOCOUNT ON
	SET @errorNumber = 0
	SET @errorMessage = ''

	DECLARE @SensorType AS TINYINT
	DECLARE @SensorId AS INT

	SELECT @SensorType = [TypeId] FROM [dbo].[SensorsType] WHERE [SensorType]='Squib'
	
	SELECT @SensorId = A.[Id] from [dbo].[Sensors] AS A INNER JOIN [dbo].SensorsType AS B ON A.SensorType=B.TypeId WHERE B.SensorType='Squib' AND A.SerialNumber=@SerialNumber

	IF( @SensorId IS NULL)
	BEGIN
		INSERT INTO [dbo].[Sensors] (SensorType, SerialNumber) VALUES (@SensorType, @SerialNumber)
		SELECT @SensorId = SCOPE_IDENTITY()
		SET @new_id = @SensorId
		INSERT INTO [dbo].[SensorsSquib] (
			[SensorId],	
			[BypassCurrentFilter],
			[BypassVoltageFilter],
			[DelayMS],
			[DurationMS],
			[FireMode],
			[IsoCode],
			[IsoChannelName],
			[UserCode],
			[UserChannelName],
			[MeasurementType],
			[SquibOutputCurrent],
			[SquibToleranceLow],
			[SquibToleranceHigh],
			[LimitDuration],
			[DefineDelayInTest],
			[ArticleId],
			[Version],
			[LastModified],
			[LastModifiedBy],
			[UserValue1],
			[UserValue2],
			[UserValue3],
			[UserTags],
			[Broken],
			[DoNotUse])
	VALUES (
			@SensorId,
			@BypassCurrentFilter,
			@BypassVoltageFilter,
			@DelayMS,
			@DurationMS,
			@FireMode,
			@IsoCode,
			@IsoChannelName,
			@UserCode,
			@UserChannelName,
			@MeasurementType,
			@SquibOutputCurrent,
			@SquibToleranceLow,
			@SquibToleranceHigh,
			@LimitDuration,
			@DefineDelayInTest,
			@ArticleId,
			@Version,
			@LastModified,
			@LastModifiedBy,
			@UserValue1,
			@UserValue2,
			@UserValue3,
			@UserTags,
			@Broken,
			@DoNotUse)	
	END	
	ELSE
	BEGIN
		SET @new_id = @SensorId
		UPDATE [dbo].[SensorsSquib] SET
			[BypassCurrentFilter]=@BypassCurrentFilter,
			[BypassVoltageFilter]=@BypassVoltageFilter,
			[DelayMS]=@DelayMS,
			[DurationMS]=@DurationMS,
			[FireMode]=@FireMode,
			[IsoCode]=@IsoCode,
			[IsoChannelName]=@IsoChannelName,
			[UserCode]=@UserCode,
			[UserChannelName]=@UserChannelName,
			[MeasurementType]=@MeasurementType,
			[SquibOutputCurrent]=@SquibOutputCurrent,
			[SquibToleranceLow]=@SquibToleranceLow,
			[SquibToleranceHigh]=@SquibToleranceHigh,
			[LimitDuration]=@LimitDuration,	
			[DefineDelayInTest] = @DefineDelayInTest,																																																							
			[ArticleId]=@ArticleId,
			[Version]=@Version,
			[LastModified]																																																																																																																																																																																																																																																																																																																																																		=@LastModified,
			[LastModifiedBy]=@LastModifiedBy,
			[UserValue1]=@UserValue1,
			[UserValue2]=@UserValue2,
			[UserValue3]=@UserValue3,
			[UserTags]=@UserTags,
			[Broken]=@Broken,
			[DoNotUse]=@DoNotUse WHERE [SensorId]=@SensorId
	END	
END
	IF(@@error != 0)
	BEGIN
		SET @errorNumber = error_number()
		SET @errorMessage = error_message()
		SET @new_id = @SensorId
	END

GO