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

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

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

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

以下是我的做法:

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

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

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

SELECT @pk = @pk + 1

会更好:

SET @pk += @pk

避免使用SELECT如果你没有引用表，只是分配值。