DP44/DataPRO_sql/dbo.sp_SensorDelete.StoredProcedure.sql

<EFBFBD><EFBFBD>IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_SensorDelete]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_SensorDelete]
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_SensorDelete]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[sp_SensorDelete] AS' 
END
GO
ALTER PROCEDURE [dbo].[sp_SensorDelete]
	 @SensorSerialNumber		nvarchar(50)	= null
	,@SensorType				int				= null
	,@errorNumber				int				output
	,@errorMessage				nvarchar(250)	output
AS
BEGIN
	SET NOCOUNT ON;
	
	begin try
		begin transaction [tDeleteSensors]
		declare @sensorId	int
		set @sensorId = 0

		set @sensorId = dbo.foo_IdGetSensor(@SensorSerialNumber)

		exec [dbo].[sp_TestSetupsMarkIncomplete] @sensorId, @SensorSerialNumber, 'Some channels have no sensor assigned.', null, null, @errorNumber output ,@errorMessage output

		
		delete from [dbo].[TestObjectSensors]			where SensorId = @sensorId
		delete from [dbo].[TestObjectChannelSettings]	where SensorId = @sensorId
		delete from [dbo].[TestChannelSettings]			where SensorId = @sensorId

		exec [dbo].[sp_SensorCalibrationsDelete] @SensorSerialNumber, null, null, @errorNumber output, @errorMessage output

		
		if @SensorType = 0 
		begin
			delete from [dbo].[SensorsAnalog]			where Id = dbo.foo_IdGetSensorByType(@SensorSerialNumber, @SensorType)
		end
		else if @SensorType = 1
		begin
			delete from [dbo].[SensorsDigitalIn]		where Id = dbo.foo_IdGetSensorByType(@SensorSerialNumber, @SensorType)
		end
		else if @SensorType = 2
		begin
			delete from [dbo].[SensorsDigitalOut]		where Id = dbo.foo_IdGetSensorByType(@SensorSerialNumber, @SensorType)
		end
		else if @SensorType = 3
		begin
			delete from [dbo].[SensorsSquib]			where Id = dbo.foo_IdGetSensorByType(@SensorSerialNumber, @SensorType)
		end

		delete from [dbo].[Sensors]						where SensorId = @sensorID


		commit transaction [tDeleteSensors]
	end try
	begin catch
		rollback transaction [tDeleteSensors]
		set @errorMessage	= error_message()
		set @errorNumber	= error_number()
	end catch	
END




























GO