Loop through all records in Sql server with while statement
Foreach is the common functionality in all programming language. Foreach is used for traversing items in a collection. Unlike for loop constructs which maintains a counter to loop defined number of times, foreach will essentially loop through all the items in the collection. Hence strictly speaking, there is no such foreach loop in SQL. Cursor does offer similar kind of functionality but using cursor is not the recommended approach. In this article, we will see how to implement foreach kind of functionality in SQL using While loop
Foreach using While loop in SQL server
Definition of While loop is best summarized as
WHILE Boolean_expression
{sql_statement | statement_block }
[ BREAK ]
{sql_statement | statement_block }
[ CONTINUE ]
{sql_statement | statement_block }
Below is the simplest example of while loop
DECLARE @temp INT
SET @temp = 1
WHILE (@temp <=10)
BEGIN
PRINT @temp
SET @temp = @temp + 2
END
Output
1
3
5
7
9
While loop with break statement
Similar to other programming languages, while loop also allow the use of continue and break keyword. BREAK keyword will skips all the statement within the loop and will bring the control to the immediate statement after the loop. Continue keyword on the other hand skips all the statement and bring the control to the first statement of while loop. Below is the simplest example on the working of break statement
DECLARE @temp INT
SET @temp = 1
WHILE (@temp <=10)
BEGIN
PRINT @temp
SET @temp = @temp + 2
IF @temp = 9
BREAK;
END
Output:
1
3
5
7
While loop with continue statement
As you see above, once the value of @temp variable is equal to 9, it breaks the current loop and reaches the end of the statement. Next is the simplest example of Continue statement
DECLARE @temp INT
SET @temp = 1
WHILE (@temp <=10)
BEGIN
SET @temp = @temp + 2
IF @temp = 9
CONTINUE;
PRINT @temp
END
Output:
3
5
7
11
As you see above, the value of @temp is not printed when the value of @temp is 9.
Loop through each records
If you have an identity column in a table, you can iterate through all records of such a table as
DECLARE @total INT
DECLARE @loop INT
SET @loop = 1
SELECT @total = COUNT(StudentId) FROM Student
WHILE (@loop <= @total)
BEGIN
SELECT * FROM Student WHERE StudentId = @loop
SELECT @loop = @loop + 1
END
Above query just get the count of number of records in a table and then uses a loop variable to iterate through all records
Loop through all records of the table without identity column
Using ROW_NUMBER() function of SQL, you can loop through all records of SQL table as.
SELECT RowsCount = ROW_NUMBER() OVER(ORDER BY StudentID),*
INTO #Student FROM Student
DECLARE @MaxRowsCount INT
SET @MaxRowsCount = (SELECT MAX(RowsCount) FROM #Student)
DECLARE @Iter INT
SET @Iter = (SELECT MIN(RowsCount) FROM #Student)
WHILE @Iter <= @MaxRowsCount
BEGIN
SELECT *
FROM #Student
WHERE RowsCount = @Iter
SET @Iter = @Iter + 1
END
DROP TABLE #Student
Output:
