Does a cursor re-execute the select statement everytime it loops?

Does anyone know if a cursor re-executes the select statement averytime it
loops to the next record? The behavior I have seen indicates that this is
happening...
here is my code... (trying to update a work order bit field at the end of
that work orders records - the recordset contains several rows per work orde
r)
CREATE Procedure MC_WO_Task_Fail As
DECLARE
@.WOPK Int,
@.WOReason VarChar(2000),
@.Priority VarChar(25),
@.PriorityDesc VarChar(50),
@.WOAPK Int, -- WO ASSET PK
@.WOAID VarChar(100), -- WO ASSET ID
@.WOANAME VarChar(150), -- WO ASSET NAME
@.UDFB5 VarChar(1),
@.WOTAPK Int, -- WO TASK PK
@.Fail VarChar(1),
@.TaskNo Int,
@.TA VarChar(7000), -- TASK ACTION
@.Reason VarChar(2000),
@.TCom VarChar(7000), -- TASK COMMENTS
@.RCPK Int, -- REPAIR CENTER PK
@.RCID VarChar(25), -- REPAIR CENTER ID
@.RCName VarChar(50), -- REPAIR CENTER NAME
@.PREVWOPK Int
DECLARE WOFAILTASK CURSOR FAST_FORWARD FOR
SELECT wo.WOPK, wo.Reason, wo.Priority, wo.PriorityDesc, wo.AssetPK,
wo.AssetID, wo.AssetName,
wo.UDFBit5, wo.RepairCenterPK, wo.RepairCenterID, wo.RepairCenterName,
wot.AssetPK, wot.Fail,
wot.TaskNo, wot.TaskAction, wot.Comments
FROM WO wo
LEFT JOIN WOTask AS wot ON wot.WOPK = wo.WOPK
WHERE IsOpen = 0
AND Fail = 1
AND (UDFBit5 = 0 OR UDFBit5 IS NULL)
ORDER BY wo.WOPK
FOR READ ONLY
OPEN WOFAILTASK
FETCH NEXT FROM WOFAILTASK INTO @.WOPK, @.WOReason, @.Priority, @.PriorityDesc,
@.WOAPK, @.WOAID, @.WOANAME, @.UDFB5, @.RCPK, @.RCID, @.RCName, @.WOTAPK, @.Fail,
@.TaskNo, @.TA, @.TCom
WHILE @.@.FETCH_STATUS = 0
BEGIN
IF (@.TCom Is Null) OR (@.TCom = '') -- Task Comments
SELECT @.TCom = 'None'
SELECT @.Reason =
'WO Task Failure on WO #' + CAST(@.WOPK AS VarChar) + ' (' + @.WOReason + ' -
' + @.WOANAME + ')' + char(13) + char(10) +
'Task #' + CAST(@.TaskNo AS VarChar) + ' - Task Action: ' + LEFT(@.TA,50) + '
Comments: ' + RTRIM(@.TCom)
IF @.WOTAPK IS NULL
BEGIN
INSERT INTO WO (Reason, Status, StatusDesc, AuthStatus, AuthStatusDesc,
AssetPK, AssetID, AssetName, TargetDate, Priority, PriorityDesc, Type,
TypeDesc, RepairCenterPK, RepairCenterID, RepairCenterName, FollowupFromWOPK
)
VALUES (@.Reason, 'REQUESTED', 'Requested', 'NOTREQUIRED','(Not
Required)', @.WOAPK, @.WOAID, @.WOANAME, GetDate(), @.Priority, @.PriorityDesc,
'FU', 'Follow-up', @.RCPK, @.RCID, @.RCName, @.WOPK)
--UPDATE WO SET UDFBit5 = 1 WHERE WOPK = @.WOPK
END
ELSE
BEGIN
DECLARE @.WOTAID VarChar(100), @.WOTANAME VarChar(150)
SELECT @.WOTAID = AssetID FROM Asset WHERE AssetPK = @.WOTAPK
SELECT @.WOTANAME = AssetName FROM Asset WHERE AssetPK = @.WOTAPK
INSERT INTO WO (Reason, Status, StatusDesc, AuthStatus, AuthStatusDesc,
AssetPK, AssetID, AssetName, TargetDate, Priority, PriorityDesc, Type,
TypeDesc, RepairCenterPK, RepairCenterID, RepairCenterName, FollowupFromWOPK
)
VALUES (@.Reason, 'REQUESTED', 'Requested', 'NOTREQUIRED','(Not
Required)', @.WOtAPK, @.WOtAID, @.WOtANAME, GetDate(), @.Priority, @.PriorityDesc
,
'FU', 'Follow-up', @.RCPK, @.RCID, @.RCName, @.WOPK)
--UPDATE WO SET UDFBit5 = 1 WHERE WOPK = @.WOPK
END
SELECT @.PREVWOPK = @.WOPK
FETCH NEXT FROM WOFAILTASK INTO @.WOPK, @.WOReason, @.Priority,
@.PriorityDesc, @.WOAPK, @.WOAID, @.WOANAME, @.UDFB5, @.RCPK, @.RCID, @.RCName,
@.WOTAPK, @.Fail, @.TaskNo, @.TA, @.TCom
IF @.PREVWOPK <> @.WOPK
UPDATE WO SET UDFBIT5 = 1 WHERE WOPK = @.PREVWOPK
END
CLOSE WOFAILTASK
DEALLOCATE WOFAILTASK
GOI don't believe so. I know cursors are terribly inefficient compared to set
operations, but why do you think it is executing the select every time it
loops through?
"Randy" <Randy@.discussions.microsoft.com> wrote in message
news:8996A83E-4648-45A7-986E-88F2C5211CC5@.microsoft.com...
> Does anyone know if a cursor re-executes the select statement averytime it
> loops to the next record? The behavior I have seen indicates that this is
> happening...
> here is my code... (trying to update a work order bit field at the end of
> that work orders records - the recordset contains several rows per work
order)
> CREATE Procedure MC_WO_Task_Fail As
> DECLARE
> @.WOPK Int,
> @.WOReason VarChar(2000),
> @.Priority VarChar(25),
> @.PriorityDesc VarChar(50),
> @.WOAPK Int, -- WO ASSET PK
> @.WOAID VarChar(100), -- WO ASSET ID
> @.WOANAME VarChar(150), -- WO ASSET NAME
> @.UDFB5 VarChar(1),
> @.WOTAPK Int, -- WO TASK PK
> @.Fail VarChar(1),
> @.TaskNo Int,
> @.TA VarChar(7000), -- TASK ACTION
> @.Reason VarChar(2000),
> @.TCom VarChar(7000), -- TASK COMMENTS
> @.RCPK Int, -- REPAIR CENTER PK
> @.RCID VarChar(25), -- REPAIR CENTER ID
> @.RCName VarChar(50), -- REPAIR CENTER NAME
> @.PREVWOPK Int
> DECLARE WOFAILTASK CURSOR FAST_FORWARD FOR
> SELECT wo.WOPK, wo.Reason, wo.Priority, wo.PriorityDesc, wo.AssetPK,
> wo.AssetID, wo.AssetName,
> wo.UDFBit5, wo.RepairCenterPK, wo.RepairCenterID, wo.RepairCenterName,
> wot.AssetPK, wot.Fail,
> wot.TaskNo, wot.TaskAction, wot.Comments
> FROM WO wo
> LEFT JOIN WOTask AS wot ON wot.WOPK = wo.WOPK
> WHERE IsOpen = 0
> AND Fail = 1
> AND (UDFBit5 = 0 OR UDFBit5 IS NULL)
> ORDER BY wo.WOPK
> FOR READ ONLY
> OPEN WOFAILTASK
> FETCH NEXT FROM WOFAILTASK INTO @.WOPK, @.WOReason, @.Priority,
@.PriorityDesc,
> @.WOAPK, @.WOAID, @.WOANAME, @.UDFB5, @.RCPK, @.RCID, @.RCName, @.WOTAPK, @.Fail,
> @.TaskNo, @.TA, @.TCom
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> IF (@.TCom Is Null) OR (@.TCom = '') -- Task Comments
> SELECT @.TCom = 'None'
> SELECT @.Reason =
> 'WO Task Failure on WO #' + CAST(@.WOPK AS VarChar) + ' (' + @.WOReason +
' -
> ' + @.WOANAME + ')' + char(13) + char(10) +
> 'Task #' + CAST(@.TaskNo AS VarChar) + ' - Task Action: ' + LEFT(@.TA,50) +
'
> Comments: ' + RTRIM(@.TCom)
> IF @.WOTAPK IS NULL
> BEGIN
> INSERT INTO WO (Reason, Status, StatusDesc, AuthStatus, AuthStatusDesc,
> AssetPK, AssetID, AssetName, TargetDate, Priority, PriorityDesc, Type,
> TypeDesc, RepairCenterPK, RepairCenterID, RepairCenterName,
FollowupFromWOPK)
> VALUES (@.Reason, 'REQUESTED', 'Requested', 'NOTREQUIRED','(Not
> Required)', @.WOAPK, @.WOAID, @.WOANAME, GetDate(), @.Priority, @.PriorityDesc,
> 'FU', 'Follow-up', @.RCPK, @.RCID, @.RCName, @.WOPK)
> --UPDATE WO SET UDFBit5 = 1 WHERE WOPK = @.WOPK
> END
> ELSE
> BEGIN
> DECLARE @.WOTAID VarChar(100), @.WOTANAME VarChar(150)
> SELECT @.WOTAID = AssetID FROM Asset WHERE AssetPK = @.WOTAPK
> SELECT @.WOTANAME = AssetName FROM Asset WHERE AssetPK = @.WOTAPK
> INSERT INTO WO (Reason, Status, StatusDesc, AuthStatus, AuthStatusDesc,
> AssetPK, AssetID, AssetName, TargetDate, Priority, PriorityDesc, Type,
> TypeDesc, RepairCenterPK, RepairCenterID, RepairCenterName,
FollowupFromWOPK)
> VALUES (@.Reason, 'REQUESTED', 'Requested', 'NOTREQUIRED','(Not
> Required)', @.WOtAPK, @.WOtAID, @.WOtANAME, GetDate(), @.Priority,
@.PriorityDesc,
> 'FU', 'Follow-up', @.RCPK, @.RCID, @.RCName, @.WOPK)
> --UPDATE WO SET UDFBit5 = 1 WHERE WOPK = @.WOPK
> END
> SELECT @.PREVWOPK = @.WOPK
> FETCH NEXT FROM WOFAILTASK INTO @.WOPK, @.WOReason, @.Priority,
> @.PriorityDesc, @.WOAPK, @.WOAID, @.WOANAME, @.UDFB5, @.RCPK, @.RCID, @.RCName,
> @.WOTAPK, @.Fail, @.TaskNo, @.TA, @.TCom
> IF @.PREVWOPK <> @.WOPK
> UPDATE WO SET UDFBIT5 = 1 WHERE WOPK = @.PREVWOPK
> END
> CLOSE WOFAILTASK
> DEALLOCATE WOFAILTASK
> GO|||Hi Randy,
Curosrs are by default dynamic (actually KEYSET which means that updates
will be seen but not inserts) unless specified otherwise.
How it does is, it copies the key (to identify the row) data into the tempdb
and fetches from the base tables as you loop through. So the actual read fro
m
the tables happens in the loop. But what to read and where to read from woul
d
have already been stored.
If you don't want the updates to be reflected then use STATIC cursors. Then
the result set is stored in tempdb
And if you need the newly inserted rows which satisfies the where condition
to be included use DYNAMIC cursors.
Hope this helps.|||Thanks Guys. I think Omnibuzz got me to what I needed...
"Randy" wrote:

> Does anyone know if a cursor re-executes the select statement averytime it
> loops to the next record? The behavior I have seen indicates that this is
> happening...
> here is my code... (trying to update a work order bit field at the end of
> that work orders records - the recordset contains several rows per work or
der)
> CREATE Procedure MC_WO_Task_Fail As
> DECLARE
> @.WOPK Int,
> @.WOReason VarChar(2000),
> @.Priority VarChar(25),
> @.PriorityDesc VarChar(50),
> @.WOAPK Int, -- WO ASSET PK
> @.WOAID VarChar(100), -- WO ASSET ID
> @.WOANAME VarChar(150), -- WO ASSET NAME
> @.UDFB5 VarChar(1),
> @.WOTAPK Int, -- WO TASK PK
> @.Fail VarChar(1),
> @.TaskNo Int,
> @.TA VarChar(7000), -- TASK ACTION
> @.Reason VarChar(2000),
> @.TCom VarChar(7000), -- TASK COMMENTS
> @.RCPK Int, -- REPAIR CENTER PK
> @.RCID VarChar(25), -- REPAIR CENTER ID
> @.RCName VarChar(50), -- REPAIR CENTER NAME
> @.PREVWOPK Int
> DECLARE WOFAILTASK CURSOR FAST_FORWARD FOR
> SELECT wo.WOPK, wo.Reason, wo.Priority, wo.PriorityDesc, wo.AssetPK,
> wo.AssetID, wo.AssetName,
> wo.UDFBit5, wo.RepairCenterPK, wo.RepairCenterID, wo.RepairCenterName,
> wot.AssetPK, wot.Fail,
> wot.TaskNo, wot.TaskAction, wot.Comments
> FROM WO wo
> LEFT JOIN WOTask AS wot ON wot.WOPK = wo.WOPK
> WHERE IsOpen = 0
> AND Fail = 1
> AND (UDFBit5 = 0 OR UDFBit5 IS NULL)
> ORDER BY wo.WOPK
> FOR READ ONLY
> OPEN WOFAILTASK
> FETCH NEXT FROM WOFAILTASK INTO @.WOPK, @.WOReason, @.Priority, @.PriorityDesc
,
> @.WOAPK, @.WOAID, @.WOANAME, @.UDFB5, @.RCPK, @.RCID, @.RCName, @.WOTAPK, @.Fail,
> @.TaskNo, @.TA, @.TCom
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> IF (@.TCom Is Null) OR (@.TCom = '') -- Task Comments
> SELECT @.TCom = 'None'
> SELECT @.Reason =
> 'WO Task Failure on WO #' + CAST(@.WOPK AS VarChar) + ' (' + @.WOReason + '
-
> ' + @.WOANAME + ')' + char(13) + char(10) +
> 'Task #' + CAST(@.TaskNo AS VarChar) + ' - Task Action: ' + LEFT(@.TA,50) +
'
> Comments: ' + RTRIM(@.TCom)
> IF @.WOTAPK IS NULL
> BEGIN
> INSERT INTO WO (Reason, Status, StatusDesc, AuthStatus, AuthStatusDesc,
> AssetPK, AssetID, AssetName, TargetDate, Priority, PriorityDesc, Type,
> TypeDesc, RepairCenterPK, RepairCenterID, RepairCenterName, FollowupFromWO
PK)
> VALUES (@.Reason, 'REQUESTED', 'Requested', 'NOTREQUIRED','(Not
> Required)', @.WOAPK, @.WOAID, @.WOANAME, GetDate(), @.Priority, @.PriorityDesc,
> 'FU', 'Follow-up', @.RCPK, @.RCID, @.RCName, @.WOPK)
> --UPDATE WO SET UDFBit5 = 1 WHERE WOPK = @.WOPK
> END
> ELSE
> BEGIN
> DECLARE @.WOTAID VarChar(100), @.WOTANAME VarChar(150)
> SELECT @.WOTAID = AssetID FROM Asset WHERE AssetPK = @.WOTAPK
> SELECT @.WOTANAME = AssetName FROM Asset WHERE AssetPK = @.WOTAPK
> INSERT INTO WO (Reason, Status, StatusDesc, AuthStatus, AuthStatusDesc,
> AssetPK, AssetID, AssetName, TargetDate, Priority, PriorityDesc, Type,
> TypeDesc, RepairCenterPK, RepairCenterID, RepairCenterName, FollowupFromWO
PK)
> VALUES (@.Reason, 'REQUESTED', 'Requested', 'NOTREQUIRED','(Not
> Required)', @.WOtAPK, @.WOtAID, @.WOtANAME, GetDate(), @.Priority, @.PriorityDe
sc,
> 'FU', 'Follow-up', @.RCPK, @.RCID, @.RCName, @.WOPK)
> --UPDATE WO SET UDFBit5 = 1 WHERE WOPK = @.WOPK
> END
> SELECT @.PREVWOPK = @.WOPK
> FETCH NEXT FROM WOFAILTASK INTO @.WOPK, @.WOReason, @.Priority,
> @.PriorityDesc, @.WOAPK, @.WOAID, @.WOANAME, @.UDFB5, @.RCPK, @.RCID, @.RCName,
> @.WOTAPK, @.Fail, @.TaskNo, @.TA, @.TCom
> IF @.PREVWOPK <> @.WOPK
> UPDATE WO SET UDFBIT5 = 1 WHERE WOPK = @.PREVWOPK
> END
> CLOSE WOFAILTASK
> DEALLOCATE WOFAILTASK
> GO