little help - 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)

little help

Author

Topic

Ex
Posting Yak Master

166 Posts

Posted - 2005-01-10 : 19:39:36

DECLARE entDecCursor CURSOR FAST_FORWARD
FOR SELECT EntityId, DeclarationOrder FROM dbo.ENTITY
WHERE MajorBuildNo = 0 AND MinorBuildNo = 0
OPEN entDecCursor
DECLARE
@EntityId int, @ParentDeclarationOrder int
FETCH NEXT FROM entDecCursor INTO @entityID, @ParentDeclarationOrder
WHILE @@fetch_status = 0
BEGIN

UPDATE dbo.ENTITY SET DeclarationOrder = @ParentDeclarationOrder + 1
WHERE EntityId in ( SELECT ChildId FROM ENTITY_DEPENDENCY
WHERE ParentId = @EntityId AND MajorBuildNo = 0 AND MinorBuildNo = 0)
AND MajorBuildNo = 0 AND MinorBuildNo = 0 AND DeclarationOrder < @ParentDeclarationOrder

FETCH NEXT FROM entDecCursor INTO @entityID, @ParentDeclarationOrder
END
CLOSE entDecCursor
DEALLOCATE entDecCursor

hey all just wondering if i can get some help with this cursor
i go fine with it all until i get to this line DeclarationOrder < @ParentDeclarationOrder

FK constrains
ENTITY_DEPENDENCY(PARENTID, MAJORBUILDNO, MINORBUILDNO) REFERENCES ENTITY (ENTITYID, MAJORBUILDNO, MINORBUILDNO),

ENTITY_DEPENDENCY(CHILDID, MAJORBUILDNO, MINORBUILDNO) REFERENCES ENTITY (ENTITYID, MAJORBUILDNO, MINORBUILDNO)

PK
ENTITY (ENTITYID, MAJORBUILDNO, MINORBUILDNO)

any ideas?

nr
SQLTeam MVY

12543 Posts

Posted - 2005-01-11 : 01:37:00

What goes wrong.
And why use a cursor?

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

sreemace
Starting Member

9 Posts

Posted - 2005-01-11 : 04:27:43

Can u pls send the error u getting while executing the cursor... I couldnt find any prob in this cursor????

Thanks & Regards,
Sreejith G

Ex
Posting Yak Master

166 Posts

Posted - 2005-01-11 : 17:08:40

its this line

DeclarationOrder < @ParentDeclarationOrder

if i put the cursor into the update statment then it would become

DeclarationOrder < DeclarationOrder

would it not?

or would it be a good idea to have

update entity set ....
from entity e1, entity e2

etc like this? i think that would work am i right?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-01-11 : 17:11:03

You would do something like this:

UPDATE t1
SET Column1 = t2.Column1
FROM Table1 t1
INNER JOIN Table2 t2
ON t1.ColumnA = t2.ColumnA AND t1.ColumnB = t2.ColumnB

You should be able to use the above to change your cursor into a set-based solution using UPDATE with a JOIN.

Tara

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources