FOR UPDATE clause allowed only for DECLARE CURSOR

Question

Can anyone Please advice what is wrong with this part of my cursor statement:

declare @ColumnName nvarchar(30)
declare @IsUpdated tinyint
declare @UpdateCursor cursor 
select @ColumnName = ColumnName , @IsUpdated = IsUpdated from MyTable for update of IsUpdated 

I am receiving error "FOR UPDATE clause allowed only for DECLARE CURSOR ".

This is the first time I am creating cursor, but as far as I know the statement for update looks correct

Answer 1

Cursor is a database object used by applications to manipulate data in a set on a row-by-row basis, it's like recordset in ASP and Visual Basic.

Syntax

DECLARE @fName varchar(50), @lName varchar(50)

DECLARE cursorName CURSOR -- Declare cursor

LOCAL SCROLL STATIC

FOR

Select firstName, lastName FROM myTable

OPEN cursorName -- open the cursor

FETCH NEXT FROM cursorName

   INTO @fName, @lName

   PRINT @fName + ' ' + @lName -- print the name

WHILE @@FETCH_STATUS = 0

BEGIN

   FETCH NEXT FROM cursorName

   INTO @fName, @lName

   PRINT @fName + ' ' + @lName -- print the name

END

CLOSE cursorName -- close the cursor

DEALLOCATE cursorName -- Deallocate the cursor

Example

DECLARE @ColExpir datetime
DECLARE @ColFallprotec datetime
DECLARE @ColWorkid int
--------------------------------------------------------
DECLARE @MyCursor CURSOR
SET @MyCursor = CURSOR FAST_FORWARD
FOR
SELECT Table_Training_Detalis.DateExpires,Table_Training_Detalis.Worker_ID
FROM   Table_Courses 
OPEN @MyCursor
FETCH NEXT FROM @MyCursor
INTO @ColExpir,@ColWorkid
WHILE @@FETCH_STATUS = 0
BEGIN
update Table_Workers set WHIMIS= @ColExpir where Worker_ID=@ColWorkid

FETCH NEXT FROM @MyCursor
INTO @ColExpir,@ColWorkid
END
CLOSE @MyCursor
DEALLOCATE @MyCursor

Answer 2

I have never used a "for update of"-Cursor, but the documentation https://msdn.microsoft.com/library/ms180169.aspx states that you declare them like this:

declare @UpdateCursor cursor for select ColumnName, IsUpdated from MyTable for update of IsUpdated

Also, you don't use the @var=column syntax to fill variables from a cursor. You use fetch next from <cursor> into @var syntax (again, see the linked documentation).