通过使用T-SQL和像这样的游标:

DECLARE @MyCursor CURSOR;
DECLARE @MyField YourFieldDataType;
BEGIN
    SET @MyCursor = CURSOR FOR
    select top 1000 YourField from dbo.table
        where StatusID = 7      

    OPEN @MyCursor 
    FETCH NEXT FROM @MyCursor 
    INTO @MyField

    WHILE @@FETCH_STATUS = 0
    BEGIN
      /*
         YOUR ALGORITHM GOES HERE   
      */
      FETCH NEXT FROM @MyCursor 
      INTO @MyField 
    END; 

    CLOSE @MyCursor ;
    DEALLOCATE @MyCursor;
END;

通过使用T-SQL和像这样的游标:

DECLARE @MyCursor CURSOR;
DECLARE @MyField YourFieldDataType;
BEGIN
    SET @MyCursor = CURSOR FOR
    select top 1000 YourField from dbo.table
        where StatusID = 7      

    OPEN @MyCursor 
    FETCH NEXT FROM @MyCursor 
    INTO @MyField

    WHILE @@FETCH_STATUS = 0
    BEGIN
      /*
         YOUR ALGORITHM GOES HERE   
      */
      FETCH NEXT FROM @MyCursor 
      INTO @MyField 
    END; 

    CLOSE @MyCursor ;
    DEALLOCATE @MyCursor;
END;

通过使用游标，您可以轻松地逐个遍历记录，并单独打印记录或将记录打印为包含所有记录的单个消息。

DECLARE @CustomerID as INT;
declare @msg varchar(max)
DECLARE @BusinessCursor as CURSOR;

SET @BusinessCursor = CURSOR FOR
SELECT CustomerID FROM Customer WHERE CustomerID IN ('3908745','3911122','3911128','3911421')

OPEN @BusinessCursor;
    FETCH NEXT FROM @BusinessCursor INTO @CustomerID;
    WHILE @@FETCH_STATUS = 0
        BEGIN
            SET @msg = '{
              "CustomerID": "'+CONVERT(varchar(10), @CustomerID)+'",
              "Customer": {
                "LastName": "LastName-'+CONVERT(varchar(10), @CustomerID) +'",
                "FirstName": "FirstName-'+CONVERT(varchar(10), @CustomerID)+'",    
              }
            }|'
        print @msg
    FETCH NEXT FROM @BusinessCursor INTO @CustomerID;
END

这就是我一直在做的，如果你需要做一些迭代…但首先寻找集合操作是明智的。另外，不要这样做，因为您不想学习游标。

select top 1000 TableID
into #ControlTable 
from dbo.table
where StatusID = 7

declare @TableID int

while exists (select * from #ControlTable)
begin

    select top 1 @TableID = TableID
    from #ControlTable
    order by TableID asc

    -- Do something with your TableID

    delete #ControlTable
    where TableID = @TableID

end

drop table #ControlTable

我认为这是迭代item的简单方法。

declare @cateid int
select CateID into [#TempTable] from Category where GroupID = 'STOCKLIST'

while (select count(*) from #TempTable) > 0
begin
    select top 1 @cateid = CateID from #TempTable
    print(@cateid)

    --DO SOMETHING HERE

    delete #TempTable where CateID = @cateid
end

drop table #TempTable

这样我们就可以迭代到表数据。

DECLARE @_MinJobID INT
DECLARE @_MaxJobID INT
CREATE  TABLE #Temp (JobID INT)

INSERT INTO #Temp SELECT * FROM DBO.STRINGTOTABLE(@JobID,',')
SELECT @_MinJID = MIN(JobID),@_MaxJID = MAX(JobID)  FROM #Temp

    WHILE @_MinJID <= @_MaxJID
    BEGIN

        INSERT INTO Mytable        
        (        
            JobID,        
        )        

        VALUES        
        (        
            @_MinJobID,        
        ) 

        SET @_MinJID = @_MinJID + 1;
    END

DROP TABLE #Temp

STRINGTOTABLE是用户定义函数，它将解析逗号分隔的数据并返回表。谢谢