How to solve a recursive situation in SQL where back and forth going between 2 tables is needed?

Question

I have 2 tables.

1. GeneralContractors
2. Partners

Each GeneralContractor has its partners in partners table and each partner is associated to general contractor. Both table has IsBlackList against each record which means if I blackList a single general contractor in GC then all its partners will be blackListed too and when a partner is blacklisted then again for each partner it's general contractor would be blacklisted too and if that geenral contractor has it's own parnters then it will blacklist that partner too. So it's a recursive kinda situation going back and forth between partners and general contractors table. I have tried my best and achieved somehow but not completely. Please help me on it.

Code:

ALTER PROC  [dbo].[UpdateContractorsBlackList]

    @GContractorID varchar(20),
    @BlackList bit

AS
BEGIN

   Begin Try
        Begin Transaction 

              Update GeneralContractors 
              set IsBlackList= @BlackList
              Where GeneralContractorID= @GContractorID

              Update Partners
              Set IsBlackList= @BlackList
              Where GeneralContractor_ID= @GContractorID

        Commit Transaction

   End Try

This the code I could and I did but I can't figure out what to do more.

GC table:

Partners table:

In a nutshell:

In a nutshell, If I blacklist a GC then it blacklists all associated partners and for each partner it checks it has associated record in GC table then it blacklists that too and then for each record in GC it blacklists the associated partners.

Update:

a bit of more code from partners perspective.

 Update Partners
              Set IsBlackList= @BlackList
              Where CNIC= @CNIC

              Update gc 
              Set gc.IsBlackList= @BlackList
              From GeneralContractors gc
              Inner Join Partners p
              ON p.GeneralContractor_ID= gc.GeneralContractorID
              Where p.CNIC= @CNIC

Answer 1

A while loop?

-- Change the following to start with a partnerID

update GeneralContractors 
set isblacklist = 1
where GeneralContractorID = @ContractorID

-- End of change block

WHILE
(select count(*) 
from from partners p1
inner join GeneralContractors g2
  on p1.PartnerID = g2.PartnerID
where  (g2.IsBlackList = 1 and p1.isBlacklist = 0)
or (p1.IsBlackList =1 and g2.IsBlackList = 0)) > 0
BEGIN

Update p1
set p1.IsBlackList = 1
from partners p1
inner join GeneralContractors g2
  on p1.PartnerID = g2.PartnerID
where  g2.IsBlackList = 1

Update g2
set g2.IsBlackList = 1
from GeneralContractors g2
inner join partners p1
  on p1.PartnerID = g2.PartnerID
where  p1.IsBlackList = 1

END

EDIT

To update pbased on a partnerID, change the first update to:

update Partners
set isblacklist = 1
where Partnerid = @PartnerID

Answer 2

I would advice you to revise your recursion by adding something like a stop-condition or level of recursion. Otherwise, depending of contractor/partner link factor, you could end up black listing all records in the database.

Answer 3

1. create a procedure with three input parameters

   @GContractorID --- Id of the general contractor

   @CNIC --- Id to identify a partner

   @BlackList --- blacklisting option

2. When you want to blacklist a GC then leave @CNIC as NULL

   When you want to blacklist a partner then leave @GContractorID as NULL

With that I hope the below works! :)

CREATE PROC  [dbo].[UpdateContractorsBlackList]

    @GContractorID varchar(20), --pass 'NULL' when you want to pass only PartnerID
    @CNIC bigint, --pass 'NULL' when you want to pass only GCID
    @BlackList bit

AS
BEGIN

DECLARE @GC_NUM int

     Begin Transaction 

            if (@CNIC is null)
            begin
              Update GeneralContractors 
              set IsBlackList= @BlackList
              Where GeneralContractorID= @GContractorID

              Update Partners
              Set IsBlackList= @BlackList
              Where GeneralContractor_ID= @GContractorID
            end

            if (@GContractorID is null)
            begin
              Update Partners
              Set IsBlackList= @BlackList
              Where CNIC= @CNIC

              SET @GC_NUM=(select GeneralContractorID from Partners where CNIC= @CNIC)

              Update GeneralContractors 
              set IsBlackList= @BlackList
              Where GeneralContractorID= @GContractorID

              Update Partners
              Set IsBlackList= @BlackList
              Where GeneralContractor_ID= @GC_NUM

            end 
       Commit Transaction

END