像这样定义临时表-

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

轻量级，不需要创建额外的表，如果您在表上有一个整数ID

Declare @id int = 0, @anything nvarchar(max)
WHILE(1=1) BEGIN
  Select Top 1 @anything=[Anything],@id=@id+1 FROM Table WHERE ID>@id
  if(@@ROWCOUNT=0) break;

  --Process @anything

END

像这样定义临时表-

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

以下是我的做法:

Select Identity(int, 1,1) AS PK, DatabaseID
Into   #T
From   @databases

Declare @maxPK int;Select @maxPK = MAX(PK) From #T
Declare @pk int;Set @pk = 1

While @pk <= @maxPK
Begin

    -- Get one record
    Select DatabaseID, Name, Server
    From @databases
    Where DatabaseID = (Select DatabaseID From #T Where PK = @pk)

    --Do some processing here
    -- 

    Select @pk = @pk + 1
End

[编辑]因为我可能在第一次读这个问题时跳过了“变量”这个词，这里是一个更新的回答…

declare @databases table
(
    PK            int IDENTITY(1,1), 
    DatabaseID    int,
    Name        varchar(15),   
    Server      varchar(15)
)
-- insert a bunch rows into @databases
--/*
INSERT INTO @databases (DatabaseID, Name, Server) SELECT 1,'MainDB', 'MyServer'
INSERT INTO @databases (DatabaseID, Name, Server) SELECT 1,'MyDB',   'MyServer2'
--*/

Declare @maxPK int;Select @maxPK = MAX(PK) From @databases
Declare @pk int;Set @pk = 1

While @pk <= @maxPK
Begin

    /* Get one record (you can read the values into some variables) */
    Select DatabaseID, Name, Server
    From @databases
    Where PK = @pk

    /* Do some processing here */
    /* ... */ 

    Select @pk = @pk + 1
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

这样我就不需要向表中添加字段或使用窗口函数。

另一种不需要改变模式或使用临时表的方法:

DECLARE @rowCount int = 0
  ,@currentRow int = 1
  ,@databaseID int
  ,@name varchar(15)
  ,@server varchar(15);

SELECT @rowCount = COUNT(*)
FROM @databases;

WHILE (@currentRow <= @rowCount)
BEGIN
  SELECT TOP 1
     @databaseID = rt.[DatabaseID]
    ,@name = rt.[Name]
    ,@server = rt.[Server]
  FROM (
    SELECT ROW_NUMBER() OVER (
        ORDER BY t.[DatabaseID], t.[Name], t.[Server]
       ) AS [RowNumber]
      ,t.[DatabaseID]
      ,t.[Name]
      ,t.[Server]
    FROM @databases t
  ) rt
  WHERE rt.[RowNumber] = @currentRow;

  EXEC [your_stored_procedure] @databaseID, @name, @server;

  SET @currentRow = @currentRow + 1;
END