SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER PROCEDURE [dbo].[usp_DeleteRoadMapRelations] -- Add the parameters for the stored procedure here @RoadMapGuid uniqueidentifier, @StatusMessage varchar(50) OUTPUT AS BEGIN -- DELETE RoadMap Document Mappings declare @IsTemplate bit DECLARE @DocumentGuid uniqueidentifier DECLARE @getDocument CURSOR SET @getDocument = CURSOR FOR SELECT DocumentGuid FROM RoadmapDocumentMapping where RoadmapGuid = @RoadMapGuid select @IsTemplate = IsTemplate from Roadmap where RoadmapGuid = @RoadMapGuid if @IsTemplate = 0 begin OPEN @getDocument FETCH NEXT FROM @getDocument INTO @DocumentGuid WHILE @@FETCH_STATUS = 0 BEGIN if exists(select * from Document where DocumentGuid = @DocumentGuid and IsFromTemplate = 0) begin delete from RoadmapDocumentMapping where DocumentGuid = @DocumentGuid delete from DocumentVersion where DocumentGuid = @DocumentGuid delete from Document where DocumentGuid = @DocumentGuid end else begin delete from RoadmapDocumentMapping where DocumentGuid = @DocumentGuid and RoadmapGuid = @RoadMapGuid end FETCH NEXT FROM @getDocument INTO @DocumentGuid END CLOSE @getDocument DEALLOCATE @getDocument end else begin OPEN @getDocument FETCH NEXT FROM @getDocument INTO @DocumentGuid WHILE @@FETCH_STATUS = 0 BEGIN delete from RoadmapDocumentMapping where DocumentGuid = @DocumentGuid delete from DocumentVersion where DocumentGuid = @DocumentGuid delete from Document where DocumentGuid = @DocumentGuid FETCH NEXT FROM @getDocument INTO @DocumentGuid END CLOSE @getDocument DEALLOCATE @getDocument end IF @@ERROR <> 0 BEGIN SELECT @StatusMessage = 'Error while DELETE FROM table RoadmapDocumentMapping' RETURN END -- DELETE RoadMap History DELETE FROM RoadmapHistory WHERE RoadmapGuid = @RoadMapGuid IF @@ERROR <> 0 BEGIN SELECT @StatusMessage = 'Error while deleting FROM RoadmapHistory RoadmapGuid Column' RETURN END --ProcessProcedureMapping DELETE FROM ProcessProcedureMapping WHERE ProcedureGuid = @RoadMapGuid IF @@ERROR <> 0 BEGIN SELECT @StatusMessage = 'Error while deleting FROM ProcessProcedureMapping ProcedureGuid Column' RETURN END --RoadmapKBArticleMapping DELETE FROM RoadmapKBArticleMapping where RoadmapGuid = @RoadMapGuid IF @@ERROR <> 0 BEGIN SELECT @StatusMessage = 'Error while DELETE FROM table RoadmapKBArticleMapping' RETURN END --RoadmapNotesMapping DELETE FROM RoadmapNotesMapping where RoadmapGuid = @RoadMapGuid IF @@ERROR <> 0 BEGIN SELECT @StatusMessage = 'Error while DELETE FROM table RoadmapNotesMapping' RETURN END --WorkItemRoadmapMapping DELETE FROM WorkItemRoadmapMapping where RoadmapGuid = @RoadMapGuid IF @@ERROR <> 0 BEGIN SELECT @StatusMessage = 'Error while DELETE FROM table WorkItemRoadmapMapping' RETURN END --CustomEntityRoadmapMapping DELETE FROM CustomEntityRoadmapMapping where RoadmapGuid = @RoadMapGuid IF @@ERROR <> 0 BEGIN SELECT @StatusMessage = 'Error while DELETE FROM table CustomEntityRoadmapMapping' RETURN END --RoadmapCustomFormTemplateMapping DELETE FROM RoadmapCustomFormTemplateMapping where RoadmapGuid = @RoadMapGuid IF @@ERROR <> 0 BEGIN SELECT @StatusMessage = 'Error while DELETE FROM table RoadmapCustomFormTemplateMapping' RETURN END -- RoadmapCustomFormMapping DELETE FROM RoadmapCustomFormMapping where RoadmapGuid = @RoadMapGuid IF @@ERROR <> 0 BEGIN SELECT @StatusMessage = 'Error while DELETE FROM table RoadmapCustomFormMapping' RETURN END update RoadmapTaskMapping set ParentPTMappingGuid = null where ParentPTMappingGuid in (select RoadmapTaskMappingGuid from RoadmapTaskMapping where ProcedureGuid = @RoadMapGuid) IF @@ERROR <> 0 BEGIN SELECT @StatusMessage = 'Error while DELETE FROM table RoadmapTaskMapping' RETURN END DELETE FROM RoadmapTaskPredecessorMapping where SuccessorPTMappingGuid in (select RoadmapTaskMappingGuid from RoadmapTaskMapping where ProcedureGuid= @RoadMapGuid) IF @@ERROR <> 0 BEGIN SELECT @StatusMessage = 'Error while DELETE FROM table Successor-RoadmapTaskMapping' RETURN END DELETE FROM RoadmapTaskPredecessorMapping where PredecessorPTMappingGuid in (select RoadmapTaskMappingGuid from RoadmapTaskMapping where ProcedureGuid = @RoadMapGuid) IF @@ERROR <> 0 BEGIN SELECT @StatusMessage = 'Error while DELETE FROM table Predecessor-RoadmapTaskMapping' RETURN END DELETE FROM RoadmapTaskMapping where ProcedureGuid = @RoadMapGuid IF @@ERROR <> 0 BEGIN SELECT @StatusMessage = 'Error while DELETE FROM table RoadmapTaskMapping' RETURN END --WPMCustomEntityRoadmapMapping DELETE FROM WPMCustomEntityRoadmapMapping where RoadMapGuid = @RoadMapGuid IF @@ERROR <> 0 BEGIN SELECT @StatusMessage = 'Error while DELETE FROM table WPMCustomEntityRoadmapMapping' RETURN END --BERoadmapMapping DELETE FROM BusinessEntityRoadmapMapping where RoadMapGuid = @RoadMapGuid IF @@ERROR <> 0 BEGIN SELECT @StatusMessage = 'Error while DELETE FROM table BusinessEntityRoadmapMapping' RETURN END SELECT @StatusMessage = 'Success' END
EXISTS
IN
DELETE
var
This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)