73 lines
4.6 KiB
Transact-SQL
73 lines
4.6 KiB
Transact-SQL
USE [EnVisagePr]
|
||
GO
|
||
/****** Object: StoredProcedure [dbo].[sp_DeleteProject] Script Date: 29.09.2015 19:26:19 ******/
|
||
SET ANSI_NULLS ON
|
||
GO
|
||
SET QUOTED_IDENTIFIER ON
|
||
GO
|
||
|
||
ALTER PROCEDURE [dbo].[sp_DeleteProject] (@id uniqueidentifier) AS
|
||
BEGIN
|
||
DECLARE @ProjectPartsCount int
|
||
DECLARE @ScenarioId uniqueidentifier
|
||
DECLARE @ProjectPartId uniqueidentifier
|
||
|
||
SELECT @ProjectPartsCount = COUNT(1) FROM Project WHERE ParentProjectId = @id
|
||
|
||
IF (@ProjectPartsCount > 0)
|
||
BEGIN
|
||
-- Delete project parts (if has any)
|
||
DECLARE PartsToDelete CURSOR FOR
|
||
SELECT Id from Project where ParentProjectId = @id
|
||
FOR READ ONLY
|
||
|
||
--PRINT 'Parts for project found: ' + STR(@ProjectPartsCount);
|
||
OPEN PartsToDelete;
|
||
FETCH NEXT FROM PartsToDelete INTO @ProjectPartId;
|
||
WHILE @@FETCH_STATUS = 0
|
||
BEGIN
|
||
--PRINT 'Deleting part ' + CAST(@ProjectPartId AS VARCHAR(50));
|
||
EXEC sp_DeleteProject @ProjectPartId
|
||
FETCH NEXT FROM PartsToDelete INTO @ProjectPartId;
|
||
END;
|
||
|
||
CLOSE PartsToDelete;
|
||
DEALLOCATE PartsToDelete;
|
||
--PRINT 'All parts deleted';
|
||
END
|
||
|
||
-- Delete scenarios
|
||
DECLARE ScenariosToDelete CURSOR FOR
|
||
SELECT Id from Scenario where ParentId = @id
|
||
FOR READ ONLY
|
||
|
||
--PRINT 'Deleting scenarios for ' + CAST(@id AS VARCHAR(50)) + '...';
|
||
OPEN ScenariosToDelete;
|
||
FETCH NEXT FROM ScenariosToDelete INTO @ScenarioId;
|
||
WHILE @@FETCH_STATUS = 0
|
||
BEGIN
|
||
--PRINT 'Deleting scenario ' + CAST(@ScenarioId AS VARCHAR(50));
|
||
EXEC sp_DeleteScenario @ScenarioId
|
||
FETCH NEXT FROM ScenariosToDelete INTO @ScenarioId;
|
||
END;
|
||
|
||
CLOSE ScenariosToDelete;
|
||
DEALLOCATE ScenariosToDelete;
|
||
--PRINT 'All scenarios deleted';
|
||
|
||
--PRINT 'Clearing tables fro project ' + CAST(@Id AS VARCHAR(50));
|
||
--PRINT 'Deleting from [StrategicGoal2Project]';
|
||
delete from StrategicGoal2Project where ProjectId =@Id
|
||
--PRINT 'Deleting from [Team2Project]';
|
||
delete from Team2Project where ProjectId =@Id
|
||
--PRINT 'Deleting from [Contact2Project]';
|
||
delete from Contact2Project where ShowId =@Id
|
||
--PRINT 'Deleting from [ProjectAccess]';
|
||
delete from ProjectAccess where ProjectId =@Id
|
||
--PRINT 'Deleting from [Attachments]';
|
||
delete from Attachments where ParentId = @id
|
||
--PRINT 'Deleting from [Project]';
|
||
delete from Project where id = @id
|
||
END
|
||
|