For Loop in SQL Server Stored Procedure

by

In this SQL Server tutorial, you will understand the FOR loop in SQL Server stored procedure and learn how to use it.

As a database developer, if you are familiar with SQL Server, then you already know that SQL Server doesn’t have a FOR loop.

So, I will explain how you can simulate the FOR loop in the SQL Server stored procedure.

For Loop in SQL Server Stored Procedure

First, I want to say that SQL Server doesn’t have ‘FOR LOOP’, but you can achieve similar iterative processing using the ‘WHILE’ loop.

So, instead of ‘FOR LOOP’, you will use the ‘WHILE’ loop in the SQL Server stored procedure.

Let’s see the syntax of the WHILE loop, which is structured to repeatedly execute a block of statements as long as a specified condition is true.

WHILE [condition]
BEGIN
	-- SQL statements to execute
END

Where,

  • WHILE: This is the beginning of the loop, and the loop continues as long as the condition specified next to ‘WHILE’ evaluates to ‘True’.
  • condition: This is the boolean expression. If it is true, then the statement or logic within the loop is executed. If it becomes false, the loop ends, and control is passed to the next statement following the ‘END’ keyword.
  • BEGIN…END: This is where all the statements that must be executed repeatedly are kept.

Let’s take an example of how to use the WHILE loop.

DECLARE @Count INT = 1;

WHILE @Count <= 5 
BEGIN
	PRINT 'Count is: ' + CAST(@Count AS VARCHAR); 
	SET @Count = @Count + 1; 
END
WHILE loop in SQL Server Stored Procedure

From the above output, you can see that the ‘WHILE’ loop prints the value of ‘@count’ and increments it by 1 in each iteration. The loop continues if ‘@Count’ is less than or equal to 5.

Let me explain what happens in detail; first, initialize the @Count variable of type INT with value 1.

Then, WHILE loop begins with condition @Count < 5, which means the loop runs as long as the value @count variable is less than 5.

Then, within the BEGIN and END block, the first statement PRINT ‘Count is: ‘ + CAST(@Count AS VARCHAR) prints the current value of counter variable @Count. After that, the next statement, SET @Count = @Count + 1, increases the current value of @Count by 1 at each iteration.

In each iteration, it prints the incremented value of the @Count variable that you can see in the output.

Now that you know how the WHILE loop works, it’s time to learn how to use it in the SQL Server stored procedure.

Before proceeding, if you don’t know how to create a stored procedure, then visit this tutorial Create Stored Procedure in SQL Server

Use the below command to create the uspCounter stored procedure, which prints the counter’s value five times, as shown below.

CREATE PROCEDURE uspCounter
AS
BEGIN
	DECLARE @Count INT = 1;

	WHILE @Count <= 5 
	BEGIN
		PRINT 'Count is: ' + CAST(@Count AS VARCHAR); 
		SET @Count = @Count + 1; 
	END;
END;
FOR loop in SQL Server Stored Procedure

The ‘WHILE’ loop is used in the stored procedure uspCounter; this is the same one you learned. All you have to do is to place the while loop within the BEGIN and END blocks of the stored procedure.

This is how to use the WHILE loop in a stored procedure; as the FOR loop is not supported, you can use the WHILE loop to achieve the same task and functionality.

In the stored procedure, you can insert the records using the WHILE loop.

Conclusion

In this SQL Server tutorial, you learned how to use the FOR loop in the SQL Server stored procedure; in reality, you have used the WHILE loop instead of the FOR loop, which behaves similarly to the FOR loop.

You may like to read: