# How to group all linked objects in a undirected cyclic graph

#1

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``````

#2

You can increase the recursion level allowed. Check out query hint "OPTION (MAXRECURSION nnn)".