Syntax of for-loop in SQL Server

What is the syntax of a for loop in TSQL?

Answers


T-SQL doesn't have a FOR loop, it has a WHILE loop WHILE (Transact-SQL)

WHILE Boolean_expression
BEGIN

END

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

DECLARE @intFlag INT
SET @intFlag = 1
WHILE (@intFlag <=5) 
BEGIN
    PRINT @intFlag
    SET @intFlag = @intFlag + 1
END
GO

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 @count=  Count(*) FROM TABLE

WHILE @i <= @count
BEGIN

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

    SET @i = @i + 1;

END

How about this:

BEGIN
   Do Something
END
GO 10

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


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


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.


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;  

Need Your Help

The simplest and actual example code of LMAX Disruptor

java disruptor-pattern

I wish I can get the simplest possible example code, which will show how to use LMAX disruptor(http://code.google.com/p/disruptor/).