Syntax of for-loop in SQL Server
What is the syntax of a for loop in TSQL?
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
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.
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.
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.
DECLARE @X INT=1; WAY: --> Here the DO statement PRINT @X; SET @X += 1; IF @X<=10 GOTO WAY;
DECLARE @X INT = 1; WAY: -- Here the REPEAT statement PRINT @X; SET @X += 1; IFNOT(@X > 10) GOTO WAY;
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.
DECLARE @a INT = 10 WHILE @a <= 20 BEGIN PRINT @a SET @a = @a + 1 END
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;