SQL Server loop - how do I loop through a set of records

SQL Server loop - how do I loop through a set of records

By using T-SQL and cursors like this :

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;

 

 

https://stackoverflow.com/questions/28506747/sql-loop-through-each-row-in-a-table

Based on the caption of your question. This is the way I loop through each row of a table using a variable of type TABLE:

DECLARE
    @counter    INT = 1,
    @max        INT = 0

-- Declare a variable of type TABLE. It will be used as a temporary table.
DECLARE @myTable TABLE (
    [Id]        int identity,
    [Column1]   nvarchar(max),
    [Column2]   nvarchar(100)
)

-- Insert your required data in the variable of type TABLE
INSERT INTO @myTable
SELECT Column1, Column2
FROM [dbo].[YOUR_DATABASE_TABLE]

-- Initialize the @max variable. We'll use thie variable in the next WHILE loop.
SELECT @max = COUNT(ID) FROM @myTable

-- Loop 
WHILE @counter <= @max
BEGIN

    -- Do whatever you want with each row in your table variable filtering by the Id column
    SELECT Column1, Column2
    FROM @myTable
    WHERE Id = @counter

    SET @counter = @counter + 1
END

实例

DECLARE @TempTable TABLE
    (
        RowNumber INT ,
        MemberId INT ,
        Birthday DATETIME
    );
INSERT INTO @TempTable ( RowNumber ,
                         MemberId ,
                         Birthday )
            SELECT ROW_NUMBER() OVER ( ORDER BY MemberID ASC ) ,
                   MemberID ,
                   Birthday
            FROM   dbo.vie_mem_16
            WHERE  Birthday IS NULL;
SELECT *
FROM   @TempTable;

DECLARE @CurrentDate DATETIME = '20180604';
DECLARE @CurrentRowNumber INT = 1;
DECLARE @MaxRowNumber INT;
DECLARE @TempMemberId INT;
SELECT @MaxRowNumber = MAX(RowNumber)
FROM   @TempTable;

WHILE ( @CurrentRowNumber <= @MaxRowNumber )
    BEGIN
        SELECT @TempMemberId = MemberId
        FROM   @TempTable
        WHERE  RowNumber = @CurrentRowNumber;

        UPDATE dbo.tbm_mem_Member_Beneficiary
        SET    Birthday = @CurrentDate
        WHERE  MemberID = @TempMemberId;

        SET @CurrentRowNumber = @CurrentRowNumber + 1;
        SET @CurrentDate = DATEADD(DAY, 1, @CurrentDate);
    END;

 

作者:Chuck Lu    GitHub