CIRCULAR DEPENDENCY - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

CIRCULAR DEPENDENCY

Author

Topic

Ex
Posting Yak Master

166 Posts

Posted - 2005-02-23 : 22:27:41

Hi all,

this is my stored procedure i am wondering a way to get rid of the cursor

CREATE PROCEDURE CHECKFORCIRCULARDEPENDENCY (
@NewParentId int,
@NewChildId int)
AS

/* Starting with the about to be inserted child find all the structures that */
/* it is a parent to and then for each of these check their children until the*/
/* search is exhausted or we find a match between the about to be inserted */
/* record's parent and a child of its child. If found a circular dependency */
/* is imminent. */
DECLARE ChildIDCursor CURSOR LOCAL FAST_FORWARD
FOR
SELECT DISTINCT ChildID FROM ENTITY_DEPENDENCY
WHERE ParentID = @NewChildId AND MajorBuildNo = 0 AND MinorBuildNo = 0
OPEN ChildIDCursor
DECLARE
@SiblingId int
FETCH NEXT FROM ChildIDCursor INTO @SiblingId
WHILE @@fetch_status = 0
BEGIN

IF (@SiblingId = @NewParentId)
BEGIN
return -1
END
ELSE
BEGIN
EXEC CheckForCircularDependency @NewParentId, @SiblingId
END

FETCH NEXT FROM ChildIDCursor INTO @SiblingId
END
CLOSE ChildIDCursor
DEALLOCATE ChildIDCursor

The table consits of

PK(EntityItemID,MajorBuildNo,MinorBuildNo)

EntityItemID MajorBuildNo MinorBuildNo PartentID ChildID
1 0 0 26 25

and so on around 5k records so far

so if i ran the procedure CHECKFORCIRCULARDEPENDENCY 25 26 would throw error

however i want to test for circulare dependency on childid's as well
so there is no circle of dependency created upon insert

anyone know a way to do this withouth using a cursor? i am stumped

or is a cursor the only way?

Cheers

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-02-24 : 07:53:52

do an (sqlteam site) search on "tree". There are several good articles on trees, recursion, and hierarchies that should help you out.

Be One with the Optimizer
TG

Ex
Posting Yak Master

166 Posts

Posted - 2005-02-24 : 19:54:39

had a look through the tree articles they seem to work well when you know the depth of dependency:) i unforutnaly dont :(

is there no better way to do this any way :)

tried a temp table and while loop doesn't seems very similar in speed

any suggestions i could play with would be brilliant

robvolk
Most Valuable Yak

15732 Posts

Posted - 2005-02-24 : 20:03:47

Try this one (again):

http://www.sqlteam.com/item.asp?ItemID=8866

Once you construct the lineage, the depth becomes irrelevant. You can check for circular references like this:

SELECT * FROM Tree T
WHERE EXISTS(SELECT * FROM Tree
WHERE Lineage LIKE '%/' + Cast(T.Node as varchar) + '/%/' + Cast(T.ParentNode as varchar) + '/%')

Don't know how long that will take to run, but it should do the trick. I'm sure there's a faster way to do it too.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources