Syntax of for-loop in SQL Server

What is the syntax of a for loop in TSQL?

10 Answers

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 

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

WHILE Boolean_expression BEGIN END 

Extra Info

Just to add as no-one has posted an answer that includes how to actually iterate over 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 

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

How about this:

BEGIN Do Something END GO 10 

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

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.

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

Try it, learn it:

DECLARE @r INT = 5 DECLARE @i INT = 0 DECLARE @F varchar(max) = '' WHILE @i < @r BEGIN DECLARE @j INT = 0 DECLARE @o varchar(max) = '' WHILE @j < @r - @i - 1 BEGIN SET @o = @o + ' ' SET @j += 1 END DECLARE @k INT = 0 WHILE @k < @i + 1 BEGIN SET @o = @o + ' *' -- '*' SET @k += 1 END SET @i += 1 SET @F = @F + @o + CHAR(13) END PRINT @F 

With date:

DECLARE @d DATE = '2019-11-01' WHILE @d < GETDATE() BEGIN PRINT @d SET @d = DATEADD(DAY,1,@d) END PRINT 'n' PRINT @d 

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; 

Old thread but still coming up and I thought I would offer a "FOREACH" solution for those that need one.

DECLARE @myValue nvarchar(45); DECLARE myCursor CURSOR FOR SELECT [x] FROM (Values ('Value1'),('Value2'),('Value3'),('Value4')) as MyTable(x); OPEN myCursor; FETCH NEXT FROM myCursor INTO @myValue; While (@@FETCH_STATUS = 0) BEGIN PRINT @myValue FETCH NEXT FROM myCursor INTO @myValue; END CLOSE myCursor; DEALLOCATE myCursor; 

I should state for the record that recursion is frowned upon in the SQL world. And for good reason - it can be very detrimental to performance. Still, for maintenance/offline/bulk/ad-hoc/testing/etc operations, I use this method a lot.