在 SQL Server 游标中获取多个值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4974981/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Get Multiple Values in SQL Server Cursor
提问by kingrichard2005
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.
帮助总是受到赞赏。
回答by LukLed
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;
回答by BazSTR
Do not use @@fetch_status - this will return status from the last cursor in the current connection. Use the example below:
不要使用@@fetch_status - 这将从当前连接中的最后一个游标返回状态。使用下面的例子：
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
