Get Multiple Values in SQL Server Cursor

Question

I have a cursor containing several columns from the row it brings back that I would like to process at once. I notice most of the examples I've seeing on how to use cursors show them assigning a particular column from the cursor to a scalar value one at a time, then moving to the next row,

e.g.

OPEN db_cursor   FETCH NEXT FROM db_cursor INTO @name    WHILE @@FETCH_STATUS = 0   BEGIN          --Do Stuff with @name scalar value, then get next row from cursor         FETCH NEXT FROM db_cursor INTO @name   END 

What I want to know is if it's possible to do something like the following:

    OPEN db_cursor       FETCH NEXT FROM db_cursor;       WHILE @@FETCH_STATUS = 0       BEGIN              SET @myName = db_cursor.name;            SET @myAge = db_cursor.age;            SET @myFavoriteColor = db_cursor.favoriteColor;            --Do stuff with scalar values             FETCH NEXT FROM db_cursor;      END 

Help is always appreciated.

Answer 1

This should work:

DECLARE db_cursor CURSOR FOR SELECT name, age, color FROM table;  DECLARE @myName VARCHAR(256); DECLARE @myAge INT; DECLARE @myFavoriteColor VARCHAR(40); OPEN db_cursor; FETCH NEXT FROM db_cursor INTO @myName, @myAge, @myFavoriteColor; WHILE @@FETCH_STATUS = 0   BEGIN           --Do stuff with scalar values         FETCH NEXT FROM db_cursor INTO @myName, @myAge, @myFavoriteColor; END; CLOSE db_cursor; DEALLOCATE db_cursor; 

Answer 2

Do not use @@fetch_status - this will return status from the last cursor in the current connection. Use the example below:

declare @sqCur cursor; declare @data varchar(1000); declare @i int = 0, @lastNum int, @rowNum int; set @sqCur = cursor local static read_only for      select          row_number() over (order by(select null)) as RowNum         ,Data -- you fields     from YourIntTable open @cur begin try     fetch last from @cur into @lastNum, @data     fetch absolute 1 from @cur into @rowNum, @data --start from the beginning and get first value      while @i < @lastNum     begin         set @i += 1          --Do your job here         print @data          fetch next from @cur into @rowNum, @data     end end try begin catch     close @cur      --|     deallocate @cur --|-remove this 3 lines if you do not throw     ;throw          --| end catch close @cur deallocate @cur