How to group all linked objects in a undirected cyclic graph

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

The underlying difficulty with this problem is that a table with a self-referencing key, as a data structure, is an implementation of a vertex ( or node )-based digraph. One way of enabling bidirectional traversals in a relational database is to break out the self-referencing key as a separate table, so that the vertices of the graph can be represented by an entity table ( for instance, a Customer table, which you likely already have ) and the edges can be represented by the new relationship table ( your GroupCustomers table in this case, except the GroupCustomerNo would likely be moved to be an attribute of the Customer table ).

While the collection of edges can be leveraged in as many ways as you can imagine ( a linked list with "next" and "previous" attributes, an n-ary tree with "branch1", "branch2", ..., "branchn"; whatever it is you need your data model to do at this point ), a simple directed edge is often sufficient, as a directed edge contains enough information to represent both directions without the need for storing an additional record for the representation of the other direction - you merely have to swap the vertices.

With that in mind, in order to perform a bidirectional traversal of your tbl_GroupCustomers, simple recursion will be sufficient, though there is some preparation of the data necessary for the traversal. For this scenario, simulating the graph from the digraph with a CTE seems reasonable, then a relatively run-of-the-mill recursive CTE to perform the traversal on the simulated graph.

USE tempdb;
GO

CREATE TABLE dbo.tbl_GroupCustomer 
( 
    CustNo                      INTEGER, 
    JtCustNo                    INTEGER, 
    GroupCustNo                 INTEGER
);

INSERT  INTO dbo.tbl_GroupCustomer ( CustNo, JtCustNo, GroupCustNo )
VALUES  ( 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 ), ( 9, NULL, NULL ), ( 10, 9, NULL );
GO

CREATE PROCEDURE dbo.usp_UpdateGroupCustomerNo
     @MainCustNo                INTEGER,
     @GrpNo                     INTEGER
AS BEGIN
    SET NOCOUNT ON;

    ;WITH
    cte_Graph AS (
        -- Simulate bidirectional relationships;
        SELECT  CustNo, JtCustNo
        FROM    dbo.tbl_GroupCustomer
        WHERE   JtCustNo IS NOT NULL
        UNION           
        SELECT  JtCustNo, CustNo
        FROM    dbo.tbl_GroupCustomer
        WHERE   JtCustNo IS NOT NULL ),
    cte_Traversal AS (
        -- Regular traversal;
        SELECT  DISTINCT CustNo = NULL, JtCustNo = CustNo
        FROM    cte_Graph
        WHERE   CustNo = @MainCustNo
        UNION ALL
        SELECT  g.CustNo, g.JtCustNo
        FROM    cte_Traversal t
        INNER JOIN cte_Graph g
            ON  g.CustNo = t.JtCustNo
        WHERE ( g.JtCustNo <> t.CustNo 
            OR  t.CustNo IS NULL ) )
    SELECT  *
    --UPDATE    gc
    --  SET GroupCustNo = @GrpNo
    FROM    cte_Traversal t
    INNER JOIN dbo.tbl_GroupCustomer gc
        ON  gc.CustNo = t.JtCustNo;

    SET NOCOUNT OFF;
END;
GO

EXECUTE dbo.usp_UpdateGroupCustomerNo @MainCustNo = 1, @GrpNo = 1;
EXECUTE dbo.usp_UpdateGroupCustomerNo @MainCustNo = 10, @GrpNo = 2;
GO

DROP PROCEDURE dbo.usp_UpdateGroupCustomerNo;
DROP TABLE dbo.tbl_GroupCustomer;
GO

StackExchange Database Administrators Q#153220, answer score: 2