Right i have a recursive procedure which basically works its way down out tree of connections and spits out every component connected to it.
The problem is we are still working the database and there is some loops in it, im trying to get the script to list the component it is looping at before failing, anyone know how i can do this?
The problem is we are still working the database and there is some loops in it, im trying to get the script to list the component it is looping at before failing, anyone know how i can do this?
Code:
@inputID bigint
AS
BEGIN
--
Declare @termid as int
Declare @nodeid as int
DECLARE nodeCursor CURSOR FAST_FORWARD FOR
WITH org_name AS
(SELECT c1.incID AS parent_id,
n1.description AS parent_name,
c1.outID AS child_id,
n2.description AS child_name
FROM components n1 RIGHT JOIN connections c1
ON n1.ID = c1.incID and isInterconnect=0 RIGHT JOIN components n2
ON n2.ID = c1.outID)
, jn AS
(SELECT parent_id, parent_name,
child_id, child_name
FROM org_name
WHERE parent_id = @inputID
UNION ALL
SELECT c.parent_id, c.parent_name,
c.child_id, c.child_name
FROM jn AS p JOIN org_name AS c
ON c.parent_id = p.child_id)
SELECT j1.child_id AS termNode
FROM jn AS j1 LEFT JOIN jn AS j2
ON j1.child_id = j2.parent_id
WHERE j2.child_id is null
OPEN nodeCursor
FETCH NEXT FROM nodeCursor
INTO @termid
WHILE @@FETCH_STATUS = 0
BEGIN
print 'Terminal ID: ' + CAST(@termid AS varchar(5))
DECLARE nodeCursorUp CURSOR FAST_FORWARD FOR
WITH org_name AS
(SELECT c1.incID AS parent_id,
n1.description AS parent_name,
c1.outID AS child_id,
n2.description AS child_name
FROM components n1 RIGHT JOIN connections c1
ON n1.ID = c1.incID RIGHT JOIN components n2
ON n2.ID = c1.outID)
, jn AS
(SELECT parent_id, parent_name,
child_id, child_name
FROM org_name
WHERE child_id = @termid
UNION ALL
SELECT c.parent_id, c.parent_name,
c.child_id, c.child_name
FROM jn AS p JOIN org_name AS c
ON c.child_id = p.parent_id)
SELECT parent_id
FROM jn
order by 1 desc
OPEN nodeCursorUp
FETCH NEXT FROM nodeCursorUp
INTO @nodeid
WHILE @@FETCH_STATUS = 0
BEGIN
print 'Node ID to surface: ' + CAST(@nodeid AS varchar(5))
FETCH NEXT FROM nodeCursorUp
INTO @nodeid
END
CLOSE nodeCursorUp
DEALLOCATE nodeCursorUp
FETCH NEXT FROM nodeCursor
INTO @termid
END
CLOSE nodeCursor
DEALLOCATE nodeCursor