Post reply

The real effect of FOR UPDATE

  • December 22, 2011 at 4:59 pm

    #265345

    Hi,

    In an attempt to understand how can I update using CURSORS, I've tried the following code example:

    USE [AdventureWorks2]

    GO

    DROP PROCEDURE HumanResources.UpdateAllEmployeeHireDateInefficiently

    GO

    CREATE PROCEDURE HumanResources.UpdateAllEmployeeHireDateInefficiently

    AS

    BEGIN TRY

    SET XACT_ABORT ON

    DECLARE curemployee CURSOR FOR SELECT TOP 10

    EmployeeID FROM HumanResources.Employee FOR UPDATE

    OPEN curemployee

    FETCH FROM curemployee

    WHILE @@FETCH_STATUS = 0

    BEGIN

    UPDATE HumanResources.Employee

    SET HireDate = GETDATE()

    WHERE CURRENT OF curemployee

    FETCH FROM curemployee -- to avoid the output row, store it into variable

    END

    END TRY

    BEGIN CATCH

    ROLLBACK TRANSACTION

    PRINT 'An error occured, transaction rolled back'

    END CATCH

    COMMIT;

    GO

    EXEC HumanResources.UpdateAllEmployeeHireDateInefficiently

    The code runs fine whether the "FOR UPDATE" keyword is used or not.

    what is the difference between when the "FOR UPDATE" keyword is used and when it isn't?

    Thanks

  • December 23, 2011 at 6:56 am

    #1425153

    Per BOL:

    FOR UPDATE [OF column_name [,...n]]

    Defines updatable columns within the cursor. If OF column_name [,...n] is supplied, only the columns listed allow modifications. If UPDATE is specified without a column list, all columns can be updated, unless the READ_ONLY concurrency option was specified.

  • December 27, 2011 at 7:41 pm

    #1425916

    I got it.. thanks.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply