sql - 如何在 SQL Server 中循环访问一组记录？

Question

如何从 select 语句中循环访问一组记录？

假设我有一些记录，我希望循环遍历并对每条记录执行一些操作。这是我的 select 语句的原始版本:

select top 1000 * from dbo.table
where StatusID = 7

Answer 1

通过使用 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;