SQLTeam.com | Weblogs | Forums

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)".