SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 cursor help
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Ex
Posting Yak Master

Australia
166 Posts

Posted - 12/19/2004 :  22:24:21  Show Profile  Reply with Quote
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

United Kingdom
12543 Posts

Posted - 12/19/2004 :  23:21:29  Show Profile  Visit nr's Homepage  Reply with Quote
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

Australia
166 Posts

Posted - 12/19/2004 :  23:35:43  Show Profile  Reply with Quote
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

United Kingdom
12543 Posts

Posted - 12/20/2004 :  07:33:54  Show Profile  Visit nr's Homepage  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000