spDelete_EmailProject

CREATE  PROCEDURE [dbo].[spDelete_EmailProject]
@PKID int,
@CurrentUsersID int

as

DECLARE @NewName nvarchar(50)
DECLARE @Name nvarchar(50)
DECLARE @GUID uniqueidentifier

IF dbo.fnIsProjectComponentLinked(@PKID,0,0,0) = 1
    BEGIN
        BEGIN TRANSACTION
            SELECT @Name = [Name] FROM EmailProject WHERE [ID] = @PKID

            exec spGetUniqueName @Name, @NewName OUTPUT

            UPDATE EmailProject
            SET Deleted = 1, [Name] = @NewName
            WHERE [ID] = @PKID

            update dbo.[EmailProjectAppeal]
            set deleted = 1
            from dbo.[EmailProjectAppeal] EPA
            inner join dbo.[EmailProject] EP on EP.ID = EPA.EMAILPROJECTID
            where EP.ID = @PKID

            update dbo.[EmailProjectAppeal_EmailList]
            set deleted = 1
            from dbo.[EmailProjectAppeal_EmailList] EPA_EL
            inner join dbo.[EmailProjectAppeal] EPA on EPA.ID = EPA_EL.EMAILPROJECTAPPEALID
            inner join dbo.[EmailProject] EP on EP.ID = EPA.EMAILPROJECTID
            where EP.ID = @PKID

            update dbo.[EmailProjectAppeal_ListEmail]
            set deleted = 1
            from dbo.[EmailProjectAppeal_ListEmail] EPA_LE
            inner join dbo.[EmailProjectAppeal_EmailList] EPA_EL on EPA_EL.ID = EPA_LE.EmailProjectAppealListID
            inner join dbo.[EmailProjectAppeal] EPA on EPA.ID = EPA_EL.EMAILPROJECTAPPEALID
            inner join dbo.[EmailProject] EP on EP.ID = EPA.EMAILPROJECTID
            where EP.ID = @PKID

            update dbo.[Email]
            set deleted = 1
            from dbo.[EMAIL] E
            inner join dbo.[EmailProjectAppeal_ListEmail] EPA_LE on EPA_LE.ID =  E.ProjectAppealListEmailID
            inner join dbo.[EmailProjectAppeal_EmailList] EPA_EL on EPA_EL.ID = EPA_LE.EmailProjectAppealListID
            inner join dbo.[EmailProjectAppeal] EPA on EPA.ID = EPA_EL.EMAILPROJECTAPPEALID
            inner join dbo.[EmailProject] EP on EP.ID = EPA.EMAILPROJECTID
            where EP.ID = @PKID

            SELECT @Guid=Guid FROM [dbo].EmailProject WHERE id = @PKID

            EXEC spAuditThis @CurrentUsersID, 3, @Guid, 33
        COMMIT TRANSACTION

    END
ELSE
    BEGIN
        DELETE EmailProject WHERE [ID] = @PKID
    END