这将在SQL SERVER 2012版本中工作。

declare @Rowcount int 
select @Rowcount=count(*) from AddressTable;

while( @Rowcount>0)
  begin 
 select @Rowcount=@Rowcount-1;
 SELECT * FROM AddressTable order by AddressId desc OFFSET @Rowcount ROWS FETCH NEXT 1 ROWS ONLY;
end 

下面是我的解决方案，它使用了一个无限循环、BREAK语句和@@ROWCOUNT函数。没有游标或临时表是必要的，我只需要写一个查询，以获得下一行在@databases表:

declare @databases table
(
    DatabaseID    int,
    [Name]        varchar(15),   
    [Server]      varchar(15)
);


-- Populate the [@databases] table with test data.
insert into @databases (DatabaseID, [Name], [Server])
select X.DatabaseID, X.[Name], X.[Server]
from (values 
    (1, 'Roger', 'ServerA'),
    (5, 'Suzy', 'ServerB'),
    (8675309, 'Jenny', 'TommyTutone')
) X (DatabaseID, [Name], [Server])


-- Create an infinite loop & ensure that a break condition is reached in the loop code.
declare @databaseId int;

while (1=1)
begin
    -- Get the next database ID.
    select top(1) @databaseId = DatabaseId 
    from @databases 
    where DatabaseId > isnull(@databaseId, 0);

    -- If no rows were found by the preceding SQL query, you're done; exit the WHILE loop.
    if (@@ROWCOUNT = 0) break;

    -- Otherwise, do whatever you need to do with the current [@databases] table row here.
    print 'Processing @databaseId #' + cast(@databaseId as varchar(50));
end

我同意之前的帖子，基于集的操作通常会执行得更好，但如果你确实需要遍历行，下面是我将采取的方法:

Add a new field to your table variable (Data Type Bit, default 0) Insert your data Select the Top 1 Row where fUsed = 0 (Note: fUsed is the name of the field in step 1) Perform whatever processing you need to do Update the record in your table variable by setting fUsed = 1 for the record Select the next unused record from the table and repeat the process DECLARE @databases TABLE ( DatabaseID int, Name varchar(15), Server varchar(15), fUsed BIT DEFAULT 0 ) -- insert a bunch rows into @databases DECLARE @DBID INT SELECT TOP 1 @DBID = DatabaseID from @databases where fUsed = 0 WHILE @@ROWCOUNT <> 0 and @DBID IS NOT NULL BEGIN -- Perform your processing here --Update the record to "used" UPDATE @databases SET fUsed = 1 WHERE DatabaseID = @DBID --Get the next record SELECT TOP 1 @DBID = DatabaseID from @databases where fUsed = 0 END

如果您别无选择，只能逐行创建FAST_FORWARD游标。它和构建while循环一样快，而且更容易长期维护。

FAST_FORWARD 指定一个启用性能优化的FORWARD_ONLY、READ_ONLY游标。如果还指定了SCROLL或FOR_UPDATE，则FAST_FORWARD不能指定。

我将提供基于集合的解决方案。

insert  @databases (DatabaseID, Name, Server)
select DatabaseID, Name, Server 
From ... (Use whatever query you would have used in the loop or cursor)

这比任何循环技术都要快得多，而且更容易编写和维护。

像这样定义临时表-

declare @databases table
(
    RowID int not null identity(1,1) primary key,
    DatabaseID    int,
    Name        varchar(15),   
    Server      varchar(15)
)

-- insert a bunch rows into @databases

然后这样做——

declare @i int
select @i = min(RowID) from @databases
declare @max int
select @max = max(RowID) from @databases

while @i <= @max begin
    select DatabaseID, Name, Server from @database where RowID = @i --do some stuff
    set @i = @i + 1
end