我真的不明白为什么你需要使用可怕的光标。 但是如果你使用的是2005/2008版本的SQL Server，还有另一种选择 使用递归

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

--; Insert records into @databases...

--; Recurse through @databases
;with DBs as (
    select * from @databases where DatabaseID = 1
    union all
    select A.* from @databases A 
        inner join DBs B on A.DatabaseID = B.DatabaseID + 1
)
select * from DBs

这种方法只需要一个变量，并且不会从@databases中删除任何行。我知道这里有很多答案，但我没有看到像这样使用MIN来获取下一个ID的答案。

DECLARE @databases TABLE
(
    DatabaseID    int,
    Name        varchar(15),   
    Server      varchar(15)
)

-- insert a bunch rows into @databases

DECLARE @CurrID INT

SELECT @CurrID = MIN(DatabaseID)
FROM @databases

WHILE @CurrID IS NOT NULL
BEGIN

    -- Do stuff for @CurrID

    SELECT @CurrID = MIN(DatabaseID)
    FROM @databases
    WHERE DatabaseID > @CurrID

END

这将在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 

Step1:下面的select语句为每条记录创建一个具有唯一行号的临时表。

select eno,ename,eaddress,mobno int,row_number() over(order by eno desc) as rno into #tmp_sri from emp 

步骤2:声明所需的变量

DECLARE @ROWNUMBER INT
DECLARE @ename varchar(100)

步骤3:从临时表中获取总行数

SELECT @ROWNUMBER = COUNT(*) FROM #tmp_sri
declare @rno int

Step4:根据在temp中创建的唯一行号循环temp表

while @rownumber>0
begin
  set @rno=@rownumber
  select @ename=ename from #tmp_sri where rno=@rno  **// You can take columns data from here as many as you want**
  set @rownumber=@rownumber-1
  print @ename **// instead of printing, you can write insert, update, delete statements**
end

我更喜欢使用偏移获取，如果你有一个唯一的ID，你可以排序你的表:

DECLARE @TableVariable (ID int, Name varchar(50));
DECLARE @RecordCount int;
SELECT @RecordCount = COUNT(*) FROM @TableVariable;

WHILE @RecordCount > 0
BEGIN
SELECT ID, Name FROM @TableVariable ORDER BY ID OFFSET @RecordCount - 1 FETCH NEXT 1 ROW;
SET @RecordCount = @RecordCount - 1;
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