Get Multiple Values in SQL Server Cursor
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
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;