EA7_spCreateUserLink

CREATE PROCEDURE dbo.EA7_spCreateUserLink(@BBNCId integer, @EEId integer) AS
BEGIN
    /* 10.07.2010  EA7_spDeleteUserLink used to delete the record from BackOfficeSystemPeople as well as BackOfficeSystemUsers. 
       However, the new BreakLink button on the Users page uses spBreakBackOfficeSystemUsersLink, which only deletes 
       from BackOfficeSystemUsers. (And EA7_spDeleteUserLink has been updated to call that sproc also.)
       This means the record from BackOfficeSystemPeople might already exist, so we need to check for that here and 
       only insert when necessary.
    */
    BEGIN transaction;
        DECLARE @newPeopleId integer;

        select @newPeopleId = ID
        from dbo.BackOfficeSystemPeople
        where BackOfficeSystemID = 1
            and BackofficeRecordID = @EEId;

        if (IsNull(@newPeopleId, 0) = 0)
        begin
            INSERT INTO dbo.BackOfficeSystemPeople (BackOfficeSystemID, BackofficeRecordID) 
                VALUES (1, @EEId);
            SET @newPeopleId = (SELECT @@identity);
        end

        if not exists(
            select *
            from dbo.BackOfficeSystemUsers
            where BackofficePeopleID = @newPeopleId
                and ClientUsersID = @BBNCId
                and [Current] = 1)
        begin
            INSERT INTO dbo.BackOfficeSystemUsers (BackofficePeopleID, ClientUsersID, [Current]) 
                VALUES (@newPeopleId, @BBNCId, 1);
        end
    COMMIT transaction;
END