Click here to Skip to main content
15,846,346 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
SQL
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
Posted
Comments
Ali_100 18-Apr-14 8:14am    
I wrote 1 in place of *
if exists(select 1 from Document where DocumentGuid = @DocumentGuid and IsFromTemplate = 0)
This is vast. Who is going to look at all these code? Please describe any specific problem in this, if you have.
NeverJustHere 18-Apr-14 9:24am    
Two thoughts:

1. Run it through the profiler to see which parts are slow.
2. Try to re-write it to not use cursors. If this logic can be replaced with normal SQL set based operations, you shuld see a big improvement in speed. Cursors are slow.

1 solution

Agree with NeverJustHere.

0) Avoid Cursors.
Cursor alternatives[^]
1) And improve the exception handling.
Overview of Error Handling in SQL Server 2005[^]
A Closer Look Inside RAISERROR - SQLServer 2005 [^]
2) Use EXISTS instead of IN for DELETE queries.
N) Check this answer, I have explained things on many topic to improve things.
How to improve performance of a website?[^]
Check Database optimization for your issue.
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900