IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_TestObjectTemplatesDelete]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_TestObjectTemplatesDelete]
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_TestObjectTemplatesDelete]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[sp_TestObjectTemplatesDelete] AS' 
END
GO
ALTER PROCEDURE [dbo].[sp_TestObjectTemplatesDelete]
            @TemplateName					nvarchar(255) = null
		   ,@errorNumber					int				output
		   ,@errorMessage					nvarchar(250)	output
AS
BEGIN
	set @errorNumber = 0; 	set @errorMessage = space(0);
	begin try
		begin transaction tTestObjectTemplatesDelete

		declare @TemplateId int
		set @TemplateId  = [dbo].[foo_IdGetTemplate](@TemplateName)

		if(not exists(select TemplateId  FROM [dbo].[TestObjects] where TemplateId = @TemplateId))
		begin
			delete from [dbo].[TemplateChannels]	where [TemplateId] = @TemplateId 
			delete from [dbo].[TemplateZones]		where [TemplateId] = @TemplateId 
			delete from [dbo].[TemplateRegions]		where [TemplateId] = @TemplateId 

			delete from [dbo].[TestObjectTemplates] where [TemplateId] = @TemplateId 
		end
		commit transaction tTestObjectTemplatesDelete
	end try
	begin catch
		
		set @errorMessage	= error_message()
		set @errorNumber	= error_number()

		rollback transaction tTestObjectTemplatesDelete
	end catch;	
END































GO