69 lines
2.0 KiB
Transact-SQL
69 lines
2.0 KiB
Transact-SQL
USE [EnVisage]
|
|
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 [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
|
|
|
|
GO
|
|
|
|
|