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
END