DP44/DataPRO_sql/dbo.sp_TestSetupsDelete.StoredProcedure.sql

<EFBFBD><EFBFBD>USE [DataPRO]
GO
/****** Object:  StoredProcedure [dbo].[sp_TestSetupsDelete]    Script Date: 6/14/2018 5:04:59 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[sp_TestSetupsDelete]
		 @TestSetupId		int = 0
		,@TestSetupName		nvarchar(50) = null
		,@errorNumber		int				output
		,@errorMessage		nvarchar(250)	output
AS
BEGIN
	set @errorNumber = 0; 	set @errorMessage = space(0);
		begin try
		begin transaction [tDeleteTestSetups]

		if(@TestSetupId = 0)
		begin
			set @TestSetupId = [dbo].foo_IdGetTestSetup(@TestSetupName)
		end
	
		delete from [dbo].[TestSetupDASSettings]			where TestSetupId = @TestSetupId
		delete from [dbo].[TestSetupObjectMetaData]			where TestSetupId = @TestSetupId
		delete from [dbo].[TestChannelSettings]				where TestSetupId = @TestSetupId
		delete from [dbo].[TestSetupHardware]				where TestSetupId = @TestSetupId
		delete from [dbo].[LevelTriggers]					where TestSetupId = @TestSetupId
		delete from [dbo].[CalculatedChannels]				where TestSetupId = @TestSetupId
		delete from [dbo].[TestGraphs]						where TestSetupId = @TestSetupId

		declare @tIds table (id int)
		insert into @tIds select TestObjectId   from [dbo].[TestSetupObjects] where TestSetupId = @TestSetupId
		
		declare @templateIds table (id int)
		insert into @templateIds SELECT C.TemplateId FROM [dbo].[TestSetupObjects] as A inner join [dbo].[TestObjects] as B on A.TestObjectId=B.TestObjectId inner join [dbo].[TestObjectTemplates] as C on B.TemplateId=C.TemplateId WHERE A.TestSetupId=@TestSetupId;

		delete from [dbo].[TestObjectHardware]			where TestObjectId	in (select id   from @tIds)
		
		delete from [dbo].[TestObjectSensors]			where TestObjectId	in (select id   from @tIds)
		delete from [dbo].[TestObjectChannelSettings]	where TestObjectId	in (select id   from @tIds)

		delete from [dbo].[TestSetupObjects]			where TestSetupId = @TestSetupId

		delete from [dbo].[TestObjects]					where TestObjectId	in (select id   from @tIds)

		delete from [dbo].[TemplateChannels]			where TemplateId	in (select id   from @templateIds)
		delete from [dbo].[TemplateZones]				where TemplateId	in (select id   from @templateIds)
		delete from [dbo].[TemplateRegions]				where TemplateId	in (select id   from @templateIds) 
		delete from [dbo].[TestObjectTemplates]			where TemplateId	in (select id   from @templateIds)

		delete from [dbo].[TestSetups]					where TestSetupId = @TestSetupId
		
		commit transaction [tDeleteTestSetups]
	end try
	begin catch
			set @errorNumber = error_number()
			set @errorMessage = error_message()
			rollback transaction [tDeleteTestSetups]
	end catch;
END