IF exists(SELECT * FROM sysobjects WHERE name = 'LD0SPDeleteInvestigation')
	DROP PROCEDURE LD0SPDeleteInvestigation
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE LD0SPDeleteInvestigation
 @INVESTIGATIONID int,
 @PROCESSMETADATAID int
   
AS

BEGIN

BEGIN TRY
  
  DECLARE @SUCCESS DECIMAL(1,0); set @SUCCESS = 0;
  
  BEGIN TRANSACTION DeleteInvestigation

	  -- Delete dependent data first

	  -- TABLE: LD0P0SAVEDCOMPONENTS
	  
	  delete from LD0P0SAVEDCOMPONENTS where PROCESSMETADATAID = @PROCESSMETADATAID;

	  -- TABLE: LD0P0PROCESSSUMMARY
	  delete from LD0P0PROCESSSUMMARY where PROCESSMETADATAID = @PROCESSMETADATAID;
  
	  -- TABLE: LD0P0CLIENTTASKS
	  delete from LD0P0CLIENTTASKS where PROCESSMETADATAID = @PROCESSMETADATAID;

	  -- TABLE: LD0P0CASEWORKERHISTORY
	  delete from LD0P0CASEWORKERHISTORY where PROCESSID = @PROCESSMETADATAID;
   
	  -- TABLE: LD0P0PROCESSMETADATA
	  delete from LD0P0PROCESSMETADATA where ID = @PROCESSMETADATAID;

	  --Delete Investigation
	  delete from LD0P0C where AEVERKNR=@INVESTIGATIONID

  COMMIT TRANSACTION DeleteInvestigation;

  SET @SUCCESS = 1;

END TRY

BEGIN CATCH

  ROLLBACK TRANSACTION DeleteInvestigation;

  SET @SUCCESS = 0;

END CATCH

select @SUCCESS as 'SUCCESS';

END

GRANT EXECUTE ON LD0SPDeleteInvestigation TO LD0ROANV 

GO