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

Author  Topic 

Ex
Posting Yak Master

166 Posts

Posted - 2004-12-19 : 22:24:21
Hey all,

this cursor is a beast and i am sure it can be changed into set based
at the moment it lives in a instead of update trigger and i know it wont work with mutliple updates if anyone can help turn this into set based i would fly to your country and buy you a beer :)

PRIMARY KEY ENTITY_ITEM_VIEW PK = (ENTITYITEMID, MAJORBUILDNO, MINORBUILDNO)
DECLARE entityCursor CURSOR FAST_FORWARD
FOR
SELECT EntityItemId FROM dbo.dimension
WHERE defineid = (SELECT defineid FROM inserted) AND MajorBuildNo = 0 AND MinorBuildNo = 0
OPEN entitycursor
DECLARE
@entityID int, @NewEvaluated int,@OldEvaluated int

FETCH NEXT FROM entityCursor INTO @entityID
WHILE @@fetch_status = 0
BEGIN
IF ((SELECT ValueIsInt FROM inserted) = -1)
BEGIN
SET @OldEvaluated = (SELECT Evaluated FROM deleted)
SET @NewEvaluated = (SELECT Evaluated FROM inserted)
END
ELSE
BEGIN
SET @OldEvaluated = (SELECT Evaluated FROM deleted)
SET @NewEvaluated = 1
END
update dbo.ENTITY_ITEM_VIEW
set TotalDimElements = TotalDimElements / @OldEvaluated * @NewEvaluated
from dbo.ENTITY_ITEM_VIEW e JOIN inserted i on e.ENTITYITEMID = i.ENTITYITEMID AND e.MajorBuildNo = i.MajorBuildNo AND e.MinorBuildNo = i.MinorBuildNo
JOIN deleted d on e.ENTITYITEMID = d.ENTITYITEMID AND e.MajorBuildNo = d.MajorBuildNo AND e.MinorBuildNo = d.MinorBuildNo
where entityitemID= @entityID AND i.MajorBuildNo = 0 AND i.MinorBuildNo = 0 AND d.evaluated <> i.evaluated AND e.MajorBuildNo = 0 AND e.MinorBuildNo = 0/*last 2 would be redundant?*******************/

FETCH NEXT FROM entityCursor INTO @entityID
END
CLOSE entityCursor
DEALLOCATE entityCursor

nr
SQLTeam MVY

12543 Posts

Posted - 2004-12-19 : 23:21:29
What's it trying to do?
It only deals with single row updates - is that ok.
The setting of @OldEvaluated and @NewEvaluated should be outside the cursor as it doesn't depend on the row.

I'm also a bit surprised this works as
where entityitemID= @entityID
doesn't have a table qualifier for
entityitemID
unless it's case sensitive.


DECLARE @NewEvaluated int,@OldEvaluated int

IF ((SELECT ValueIsInt FROM inserted) = -1)
BEGIN
SET @OldEvaluated = (SELECT Evaluated FROM deleted)
SET @NewEvaluated = (SELECT Evaluated FROM inserted)
END
ELSE
BEGIN
SET @OldEvaluated = (SELECT Evaluated FROM deleted)
SET @NewEvaluated = 1
END


update dbo.ENTITY_ITEM_VIEW
set TotalDimElements = TotalDimElements / @OldEvaluated * @NewEvaluated
from dbo.ENTITY_ITEM_VIEW e
JOIN inserted i on e.ENTITYITEMID = i.ENTITYITEMID
AND e.MajorBuildNo = i.MajorBuildNo AND e.MinorBuildNo = i.MinorBuildNo
JOIN deleted d on e.ENTITYITEMID = d.ENTITYITEMID
AND e.MajorBuildNo = d.MajorBuildNo AND e.MinorBuildNo = d.MinorBuildNo
where entityitemID in (SELECT EntityItemId FROM dbo.dimension
WHERE defineid = (SELECT defineid FROM inserted) AND MajorBuildNo = 0 AND MinorBuildNo = 0)
AND i.MajorBuildNo = 0 AND i.MinorBuildNo = 0
AND d.evaluated <> i.evaluated AND e.MajorBuildNo = 0 AND e.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.
Go to Top of Page

Ex
Posting Yak Master

166 Posts

Posted - 2004-12-19 : 23:35:43
hey thank again

but still got a little problem the trigger is in an update trigger as such there could be multiple rows

so

IF ((SELECT ValueIsInt FROM inserted) = -1)
BEGIN
SET @OldEvaluated = (SELECT Evaluated FROM deleted)
SET @NewEvaluated = (SELECT Evaluated FROM inserted)
END
ELSE
BEGIN
SET @OldEvaluated = (SELECT Evaluated FROM deleted)
SET @NewEvaluated = 1
END

that part is not going to work :(

so where i am trying to get it so somewhere within the update statment but i cant really see a way to do this unless i use a cursor as it is kind of dependent on the deleted row
Go to Top of Page

nr
SQLTeam MVY

12543 Posts

Posted - 2004-12-20 : 07:33:54
update dbo.ENTITY_ITEM_VIEW
set TotalDimElements = TotalDimElements / d.Evaluated * case when i.ValueIsInt = -1 then i.Evaluated else 1 end



==========================================
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.
Go to Top of Page
   

- Advertisement -