FOR LOOP in SQL Server Database

by
I had a requirement to insert continuous numbers into continuous rows of SQL Database table for testing purpose. After gone through the web, I found there is no FOR LOOP statement in T-SQL, but we can achieve it easily through While Loop statement. While Loop sets a condition for the repeated execution of an SQL statement or statement block. The statements are executed repeatedly as long as the specified condition is true. The execution of statements in the While Loop can also be controlled from inside the loop with the BREAK and CONTINUE keywords.

While LOOP for FOR LOOP Replacement

You can easily replace the FOR LOOP by While Loop with while condition and increment statement inside the While Loop.

USE [MorganDB];
GO
DECLARE @i int = 0
While (@i<100)
BEGIN
Set @i=@i+1
--You can do your work here
INSERT INTO Books Values(@i,'Book'+cast(@i as varchar(20)))
END

While LOOP with Break and Continue

Along with While LOOP condition you can also exit the Loop by BREAK statement.

USE [MorganDB];
GO
DECLARE @i int = 0
While (@i<100)
BEGIN
Set @i=@i+1
--You can do your work here
INSERT INTO Books Values(@i,'Book'+cast(@i as varchar(20)))
   
IF ((SELECT Count(*) From Books)>=50)
BREAK
ELSE
CONTINUE
END

Thanks,
Moragn
Software Developer

Related Posts

Advertisement

Leave a Comment