turning cursor into set based operation

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)

turning cursor into set based operation

Author

Topic

Ex
Posting Yak Master

166 Posts

Posted - 2004-12-19 : 20:05:54

Hello all,

i know this is prob the easy example i could find but could anyone help me to remove this cursor

dbo.ENTITY_ITEM PK = (ENTITYITEMID,MAJORBUILDNO,MINORBUILDNO)
dbo.ENTITYID PK = (ENTITYID,MAJORBUILDNO,MINORBUILDNO)

DECLARE entCursor CURSOR FAST_FORWARD
FOR SELECT EntityId FROM dbo.ENTITY
WHERE EntityTypeId = 0 AND MajorBuildNo = 0 AND MinorBuildNo = 0
OPEN entCursor

FETCH NEXT FROM entCursor INTO @EntityID
WHILE @@fetch_status = 0
BEGIN

UPDATE dbo.ENTITY_ITEM SET SizeUpdated = 1
WHERE TypeId = @EntityID AND MajorBuildNo = 0 AND MinorBuildNo = 0

UPDATE dbo.ENTITY SET SizeUpdated = 1
WHERE EntityId = @EntityID AND MajorBuildNo = 0 AND MinorBuildNo = 0

UPDATE dbo.ENTITY SET NewSizeOfEntity = SizeOfEntity
WHERE EntityId = @EntityID AND MajorBuildNo = 0 AND MinorBuildNo = 0

FETCH NEXT FROM entCursor INTO @EntityID
END
CLOSE entCursor
DEALLOCATE entCursor

thanks

nr
SQLTeam MVY

12543 Posts

Posted - 2004-12-19 : 20:20:07

Could be done as a single update but simpler as two.

update ENTITY_ITEM
SET SizeUpdated = 1
where TypeId in (SELECT EntityId FROM dbo.ENTITY WHERE EntityTypeId = 0 AND MajorBuildNo = 0 AND MinorBuildNo = 0)
and MajorBuildNo = 0 AND MinorBuildNo = 0

update ENTITY
SET SizeUpdated = 1 ,
NewSizeOfEntity = SizeOfEntity
WHERE EntityTypeId = 0 AND MajorBuildNo = 0 AND MinorBuildNo = 0

==========================================
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.

Ex
Posting Yak Master

166 Posts

Posted - 2004-12-19 : 20:25:51

thanks nr your a champ :)

Ex
Posting Yak Master

166 Posts

Posted - 2004-12-19 : 20:35:49

sorry nr but wouldn't the last update look like this

update ENTITY
SET SizeUpdated = 1 ,
NewSizeOfEntity = SizeOfEntity
WHERE EntityId in (SELECT EntityId FROM dbo.ENTITY WHERE EntityTypeId = 0 AND MajorBuildNo = 0 AND MinorBuildNo = 0)
AND MajorBuildNo = 0 AND MinorBuildNo = 0

? like the first update statment?

Ex
Posting Yak Master

166 Posts

Posted - 2004-12-19 : 20:43:31

Also got another harder one that i cant get out

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

IF (@CurrDeclarationOrder < @ParentDeclarationOrder)
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
END

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

nr
SQLTeam MVY

12543 Posts

Posted - 2004-12-19 : 21:10:23

quote:
Originally posted by Ex

sorry nr but wouldn't the last update look like this

update ENTITY
SET SizeUpdated = 1 ,
NewSizeOfEntity = SizeOfEntity
WHERE EntityId in (SELECT EntityId FROM dbo.ENTITY WHERE EntityTypeId = 0 AND MajorBuildNo = 0 AND MinorBuildNo = 0)
AND MajorBuildNo = 0 AND MinorBuildNo = 0

? like the first update statment?

pk is ENTITYID,MAJORBUILDNO,MINORBUILDNO
so MajorBuildNo = 0 AND MinorBuildNo = 0 will give unique ENTITYID's so there's no need fr the in clause.

==========================================
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.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources