SQL Server 2017

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE ConstructorEmployee 
(
    EID int PRIMARY KEY
);

CREATE TABLE Project 
(
    PID int PRIMARY KEY
);

CREATE TABLE ProjectConstructorEmployee 
(
    PID int, 
    EID int, 
    CONSTRAINT PK_PCE PRIMARY KEY(PID, EID),
    CONSTRAINT FK_P FOREIGN KEY (PID) REFERENCES Project (PID),
    CONSTRAINT FK_CE FOREIGN KEY (EID) REFERENCES ConstructorEmployee (EID)
);
INSERT INTO ConstructorEmployee VALUES
(1),
(2),
(3);
3 rows affected

INSERT INTO Project VALUES
(1),
(2),
(3);
3 rows affected

-- EID = 1 worked in PID 1 only
INSERT INTO ProjectConstructorEmployee VALUES
(1, 1),
(1, 2),
(2, 2),
(2, 3),
(3, 2),
(3, 3);
6 rows affected

-- Employees working on PID=1 that didn't work in any other project
SELECT EID
FROM   ProjectConstructorEmployee
WHERE  EID IN (SELECT EID FROM ProjectConstructorEmployee WHERE PID=1)
GROUP BY EID
HAVING COUNT(*) = 1


EID


1


CREATE PROCEDURE DeleteProject(@PID int)
AS
BEGIN
    BEGIN TRY
    
        DECLARE @employee TABLE (EID int);
        
        BEGIN TRANSACTION;
    
        -- save a list of employees that only worked in this proejct
        INSERT INTO @employee
        SELECT EID
        FROM   ProjectConstructorEmployee
        WHERE  EID IN (SELECT EID 
                       FROM ProjectConstructorEmployee 
                       WHERE PID = @PID)
        GROUP BY EID
        HAVING COUNT(*) = 1;                   

        DELETE FROM ProjectConstructorEmployee
        WHERE  PID = @PID;
        
        DELETE FROM Project
        WHERE  PID = @PID;

        DELETE FROM ConstructorEmployee
        WHERE EID IN (SELECT EID FROM @employee);
                     
        IF @@TRANCOUNT > 0
            COMMIT TRANSACTION;
            
    END TRY
    BEGIN CATCH
    
        IF @@TRANCOUNT > 0
            ROLLBACK TRANSACTION;
            
        -- Your error handler
        
        THROW;
    
    END CATCH
END
EXEC DeleteProject @PID = 1;
EXEC DeleteProject @PID = 2;

SELECT * FROM ConstructorEmployee;
SELECT * FROM Project;
SELECT * FROM ProjectConstructorEmployee;


EID


2

3




PID


3




PID
EID


3
2

3
3