question

I have a single table that contains data about relationships. The when I run a query to trace the relationships I get caught in loops because some of the relationships are incorrectly nested because logical loops exist in the data. I want to build a query that would identify these invalid looped relationships so they can be called out to be fixed.

Table has about a million rows. Each row is a one to one relationship. Query worked fine on a limited amount of test data but failed to complete on the full table.

Here is revised code without the prints that seem to have held it up the first time. Hopefully the revised presentation below will read better.

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
Declare @current varchar(1000)
declare @Currentrow int
--
declare @numberofrows int
declare @sql nvarchar (500)
declare @totalrows int
declare @domainacct table( acct varchar(128), rownum [int] IDENTITY(1,1) NOT NULL)
insert into @domainacct (acct)
select distinct (rtrim(DomainADAccount) + '\' + ADAccount)
	from dbo.ADMemberOF join adgroups ON admemberof.adaccount = adgroups.lansama and admemberof.domainadaccount = adgroups.domain 
	where admemberof.rowtype = 'G' and admemberof.DomainADAccount = 'UP'
			and cast(adgroups.grouptype as numeric) < 1
	order by (rtrim(DomainADAccount) + '\' + ADAccount)

set @Currentrow = 1
set @totalrows = (select count(*) from @domainacct)
select @totalrows
while (@Currentrow) < @totalrows
begin
     set @current = (select acct from @domainacct where rownum = @Currentrow)
	SET NOCOUNT ON
	DECLARE @lvl int, @line varchar(1000)
	declare @usertree table (founditem varchar(1000), foundlvl int)			         declare @stack TABLE(item varchar(1000), lvl int) --Create a tempory stack.
	INSERT INTO @stack VALUES (@current, 1) --Insert current node to the stack.
	SELECT @lvl = 1 
		WHILE @lvl > 0 --From the top level going down.
		BEGIN
		IF EXISTS (SELECT * FROM @stack WHERE lvl = @lvl)
		BEGIN
	SELECT @current = item --Find the first node that matches current node's name.
	FROM @stack
	WHERE lvl = @lvl
	SELECT @line = space(@lvl - 1) + @current --@lvl - 1 s spaces before the node name.
	insert into @usertree (founditem, foundlvl)
	values(@current, @lvl)
	DELETE FROM @stack
	WHERE lvl = @lvl
	AND item = @current --Remove the current node from the stack.
	INSERT @stack --Insert the childnodes of the current node into the stack.
	SELECT distinct DomainMemberOf + '\' + ADMemberOf, @lvl + 1
	FROM dbo.ADMemberOF 
	WHERE DomainADAccount + '\' + ADAccount = @current
	and DomainMemberOf + '\' + ADMemberOf not in (select founditem from @usertree)
	-- THIS IS THE TABLE THAT WILL CONTAIN ACCOUNTS THAT HAVE A PROBLEM
	INSERT invalid --Insert the childnodes of the current node into the stack.
	SELECT distinct DomainADAccount + '\' + ADAccount, DomainMemberOf + '\' + ADMemberOf, @lvl + 1
	FROM dbo.ADMemberOF --dbo.INV_AEX_DIS_Memberof_AD
	WHERE DomainADAccount + '\' + ADAccount = @current
	and DomainMemberOf + '\' + ADMemberOf in (select founditem from @usertree) 
	IF @@ROWCOUNT > 0 --If the previous statement added one or more nodes, go down for its first child.
	SELECT @lvl = @lvl + 1 --If no nodes are added, check its brother nodes.
	END
	   ELSE
	SELECT @lvl = @lvl - 1 --Back to the level immediately above.
	   END --While
	set @Currentrow = @Currentrow + 1
	end
	select * from invalid