如何优化这个sp

问题描述

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

推荐答案

这篇关于如何优化这个sp的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！