I have a table that has customers and its joint customers. e.g Customer 1 has a joint customer 2. Customer 1 is also a joint customer to Customer 3.
I am trying to group all customers that are linked and assign same GroupCustNo to them. In below table 1-2 are linked, 3-1 are linked. So 2-3 are also linked. All customers from 1 to 8 in below table are thus linked to each other and will have same GroupCustNo.
tbl_GroupCustomers: CustNo JtCustNo GroupCustNo --- ------- ------ 1 2 null 2 null null 3 1 null 4 1 null 4 5 null 5 6 null 5 7 null 6 null null 7 null null 8 5 null
The recursive stored procedure I wrote is below. I am calling this in a while loop for each CustNo:
exec usp_UpdateGroupCustomerNo 1, 1 The stored procedure ran successfully for most of the customers but threw up
Recursion Limit of 32 reached error for some customers. These are customers that have many joint customers and are also Joint Customers to other customers.
It seems recursion wont work here and I am at a loss on how to proceed. Please let me know if there are any alternate methods to address this problem.
CREATE PROCEDURE [dbo].[usp_UpdateGroupCustomerNo] @MainCustNo int, @GrpNo int AS declare @JtCustNo int; declare @MainCustNo2 int; if exists(select 1 from tbl_GroupCustomer where CustNo = @MainCustNo and groupcustomernumber is null) begin update tbl_GroupCustomer set groupcustomernumber = @grpno where CustNo = @MainCustNo and groupcustomernumber is null DECLARE db_cursor CURSOR LOCAL FOR select JtCustNo from tbl_GroupCustomer where CustNo = @MainCustNo and JtCustNo is not null OPEN db_cursor FETCH NEXT FROM db_cursor INTO @JtCustNo WHILE @@FETCH_STATUS = 0 BEGIN select @JtCustNo as JtCustNo exec usp_UpdateGroupCustomerNo @JtCustNo, @GrpNo DECLARE db_cursor2 CURSOR LOCAL FOR select CustNo from tbl_GroupCustomer where JtCustNo = @JtCustNo and groupcustomernumber is null OPEN db_cursor2 FETCH NEXT FROM db_cursor2 INTO @MainCustNo2 WHILE @@FETCH_STATUS = 0 BEGIN if exists(select 1 from tbl_GroupCustomer where CustNo = @MainCustNo2 and groupcustomernumber is null) begin exec usp_UpdateGroupCustomerNo @MainCustNo2, @GrpNo end FETCH NEXT FROM db_cursor INTO @MainCustNo2 END CLOSE db_cursor2 DEALLOCATE db_cursor2 FETCH NEXT FROM db_cursor INTO @JtCustNo END CLOSE db_cursor DEALLOCATE db_cursor