Syntax of for-loop in SQL Server

Question

What is the syntax of a for loop in TSQL?

Answer 1

There is no for-loop, only the while-loop:

DECLARE @i int = 0

WHILE @i < 20
BEGIN
    SET @i = @i + 1
    /* do some work */
END

Answer 2

How about this:

BEGIN
   Do Something
END
GO 10

... of course you could put an incremental counter inside it if you need to count.

Answer 3

While Loop example in T-SQL which list current month's beginning to end date.

DECLARE @Today DATE= GETDATE() ,
@StartOfMonth DATE ,
@EndOfMonth DATE;

DECLARE @DateList TABLE ( DateLabel VARCHAR(10) );
SET @EndOfMonth = EOMONTH(GETDATE());
SET @StartOfMonth = DATEFROMPARTS(YEAR(@Today), MONTH(@Today), 1);

WHILE @StartOfMonth <= @EndOfMonth
BEGIN
    INSERT  INTO @DateList
    VALUES  ( @StartOfMonth );
    SET @StartOfMonth = DATEADD(DAY, 1, @StartOfMonth);
END;

SELECT  DateLabel
FROM    @DateList;  

Answer 4

Extra Info

Just to add as no-one has posted an answer that includes how to actually iterate though a dataset inside a loop, you can use the keywords OFFSET FETCH.

Usage

DECLARE @i INT = 0;
SELECT @total =  Count(*) FROM DATASET

WHILE @i < @total
BEGIN

    SELECT * FROM TABLE
    ORDER BY COLUMN
    OFFSET @i ROWS   
    FETCH NEXT 1 ROWS ONLY  

    SET @i = @i + 1;

END

Answer 5

Simple answer is NO !!.

There is no FOR in SQL, But you can use WHILE or GOTO to achieve the way how the FOR will work.

WHILE :

DECLARE @a INT = 10

WHILE @a <= 20
BEGIN
    PRINT @a
    SET @a = @a + 1
END

GOTO :

DECLARE @a INT = 10
a:
PRINT @a
SET @a = @a + 1
IF @a < = 20
BEGIN
    GOTO a
END

I always prefer WHILE over GOTO statement.

Answer 6

For loop is not officially supported yet by SQL server. Already there is answer on achieving FOR Loop's different ways. I am detailing answer on ways to achieve different types of loops in SQL server.

FOR Loop

DECLARE @cnt INT = 0;

WHILE @cnt < 10
BEGIN
   PRINT 'Inside FOR LOOP';
   SET @cnt = @cnt + 1;
END;

PRINT 'Done FOR LOOP';

If you know, you need to complete first iteration of loop anyway, then you can try DO..WHILE or REPEAT..UNTIL version of SQL server.

DO..WHILE Loop

DECLARE @X INT=1;

WAY:  --> Here the  DO statement

  PRINT @X;

  SET @X += 1;

IF @X<=10 GOTO WAY;

REPEAT..UNTIL Loop

DECLARE @X INT = 1;

WAY:  -- Here the REPEAT statement

  PRINT @X;

  SET @X += 1;

IFNOT(@X > 10) GOTO WAY;

Reference