Best Practice Analyzer doesnt like my cursor

I'm running the Best Practice Analyzer on my sql 2k server and one of many
issues is about a bunch of cursors. The issue is for this cursor in an SP.
Actually there are the same 2 issues listed for a lot of SPs they are:
This cursor is declared as updatable (either explicitly or implicitly),
however it doesn't seem to be updated.
and
This cursor does not specify explicit updatability information (FOR UPDATE
clause or READ_ONLY clause).
However, I declared the cursor as read only so I don't understand why I
still get this Non-Compliance issue. Here's the cursor:
DECLARE curs_VAPairedComp_Insert CURSOR READ_ONLY FOR
SELECT VAL_ID
FROM #Tmp
OPEN curs_VAPairedComp_Insert
FETCH NEXT FROM curs_VAPairedComp_Insert INTO @.VAL_ID
WHILE @.@.FETCH_STATUS = 0
BEGIN
INSERT INTO tbVA (
VA_VP_ID,
VA_VAL_ID,
VA_UserID,
VA_Created,
VA_LastModified
)
SELECT
@.VP_ID,
@.VAL_ID,
@.UserID,
CURRENT_TIMESTAMP,
CURRENT_TIMESTAMP
IF (@.@.ERROR <> 0)
BEGIN
RAISERROR 44444 'Could not update or insert.'
ROLLBACK TRANSACTION
RETURN
END
FETCH NEXT FROM curs_VAPairedComp_Insert INTO @.VAL_ID
END
CLOSE curs_VAPairedComp_Insert
DEALLOCATE curs_VAPairedComp_Insert
Thanks!
moondaddy@.nospam.nospamI don't like your cursor, either - no need for it :)
You should lose the cursors altogether instead of fixing them to BPA
compliance.
What possible errors could happen? FK errors? Don't know where the
transaction is, but since you're rolling back all the inserts, this
should work just fine in a set-based, not row-based, fashion.
assuming that: @.VP_ID and @.UserID are already determined elsewhere in
the code, and that the transaction has already begun. if the transaction
only includes this insert, then the rollback is unnecessary, as the
whole insert will fail.
INSERT INTO tbVA (
VA_VP_ID,
VA_VAL_ID,
VA_UserID,
VA_Created,
VA_LastModified
)
SELECT
@.VP_ID,
VAL_ID
@.UserID,
CURRENT_TIMESTAMP,
CURRENT_TIMESTAMP
from
#Tmp
IF @.@.ERROR<>0
BEGIN
RAISERROR...
ROLLBACK TRANSACTION
END
moondaddy wrote:
> I'm running the Best Practice Analyzer on my sql 2k server and one of many
> issues is about a bunch of cursors. The issue is for this cursor in an SP
.
> Actually there are the same 2 issues listed for a lot of SPs they are:
> This cursor is declared as updatable (either explicitly or implicitly),
> however it doesn't seem to be updated.
> and
> This cursor does not specify explicit updatability information (FOR UPDATE
> clause or READ_ONLY clause).
> However, I declared the cursor as read only so I don't understand why I
> still get this Non-Compliance issue. Here's the cursor:
>
> DECLARE curs_VAPairedComp_Insert CURSOR READ_ONLY FOR
> SELECT VAL_ID
> FROM #Tmp
> OPEN curs_VAPairedComp_Insert
> FETCH NEXT FROM curs_VAPairedComp_Insert INTO @.VAL_ID
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> INSERT INTO tbVA (
> VA_VP_ID,
> VA_VAL_ID,
> VA_UserID,
> VA_Created,
> VA_LastModified
> )
> SELECT
> @.VP_ID,
> @.VAL_ID,
> @.UserID,
> CURRENT_TIMESTAMP,
> CURRENT_TIMESTAMP
> IF (@.@.ERROR <> 0)
> BEGIN
> RAISERROR 44444 'Could not update or insert.'
> ROLLBACK TRANSACTION
> RETURN
> END
> FETCH NEXT FROM curs_VAPairedComp_Insert INTO @.VAL_ID
> END
> CLOSE curs_VAPairedComp_Insert
> DEALLOCATE curs_VAPairedComp_Insert
>
> Thanks!
>|||OK point made about being set-based, however, it only rolls back if there is
an error. Also, the real issue is WHY is this getting picked up in the
first place by the Analyzer? There are numerous other cursors that are
getting picked up also, but they are different from this one and are needed.
moondaddy@.nospam.nospam
"Trey Walpole" <treypole@.newsgroups.nospam> wrote in message
news:uxBN7WI7FHA.3276@.TK2MSFTNGP15.phx.gbl...
>I don't like your cursor, either - no need for it :)
> You should lose the cursors altogether instead of fixing them to BPA
> compliance.
> What possible errors could happen? FK errors? Don't know where the
> transaction is, but since you're rolling back all the inserts, this should
> work just fine in a set-based, not row-based, fashion.
> assuming that: @.VP_ID and @.UserID are already determined elsewhere in the
> code, and that the transaction has already begun. if the transaction only
> includes this insert, then the rollback is unnecessary, as the whole
> insert will fail.
> INSERT INTO tbVA (
> VA_VP_ID,
> VA_VAL_ID,
> VA_UserID,
> VA_Created,
> VA_LastModified
> )
> SELECT
> @.VP_ID,
> VAL_ID
> @.UserID,
> CURRENT_TIMESTAMP,
> CURRENT_TIMESTAMP
> from
> #Tmp
> IF @.@.ERROR<>0
> BEGIN
> RAISERROR...
> ROLLBACK TRANSACTION
> END
>
> moondaddy wrote:|||it's probably just that there insert/update/delete statement(s) inside
the cursor(s) in question [BPA does say "based on apparent usage"]
moondaddy wrote:
> OK point made about being set-based, however, it only rolls back if there
is
> an error. Also, the real issue is WHY is this getting picked up in the
> first place by the Analyzer? There are numerous other cursors that are
> getting picked up also, but they are different from this one and are neede
d.
>