Add the Stored Procedures to the database for the Project object. Create the stored procedures for retrieving/manipulating relations with USER for the Contacts property in the Project object.

Procedures:

  1. Create the procedure for retrieving the contacts relations

    CopySQL
    Create PROCEDURE dbo.sp_GetProjectContacts
    ( 
        @projectID int
    )
    AS
    
    IF EXISTS (SELECT * FROM Table_EntitiesBase
                WHERE ID = @projectID and ID_EntityType = 8)
        BEGIN
            SELECT        U.*
            FROM            Table_ProjectsContacts_Mapping AS PCM INNER JOIN
                                     Table_Users AS U ON PCM.ID_Contact = U.ID
        END
    ELSE
        BEGIN
            Raiserror(N'Invalid Project ID %d', 16, 1, @projectID)
        END
  2. Add the procedure for inserting a new contact relation

    CopySQL
    Create PROCEDURE dbo.sp_AddProjectContact
    (     
        @projectID int,
        @contactID int
    )
    AS
    
    if not exists(SELECT * FROM Table_EntitiesBase
                WHERE ID = @projectID and ID_EntityType = 8)
            Raiserror(N'Invalid Project ID %d', 16, 1, @projectID)
    
    if not exists(SELECT * FROM Table_Users
                WHERE ID = @contactID)
            Raiserror(N'Invalid User ID %d', 16, 1, @contactID)        
    
    if exists(SELECT * FROM Table_ProjectsContacts_Mapping where ID_Project = @projectID and ID_Contact 
        = @contactID)
            Raiserror(N'Mapping between Project %d and Contact %d already exists', 16, 1, 
                @projectID, @contactID)    
    
    
    INSERT INTO Table_ProjectsContacts_Mapping
     (ID_Project, ID_Contact)
    VALUES        (@projectID,@contactID)
  3. 3. Add a procedure to remove an existing contact relation

    CopySQL
    CREATE PROCEDURE dbo.sp_RemoveProjectContact
    (         
        @projectID int,
        @contactID int
    )
    AS
    
    DELETE FROM Table_ProjectsContacts_Mapping
    WHERE        (ID_Project = @projectID) AND (ID_Contact = @contactID)