This tutorial is about the loops in SQL Server stored procedure. You will learn how loops work in stored procedures. You will see a number of examples of loops in stored procedures in SQL Server.
- SQL Server stored procedure for loop
- While loop in SQL Server stored procedure
- Loop through table SQL Server stored procedure
- SQL Server stored procedure loop through result set
- SQL Server stored procedure for loop insert
- SQL Server stored procedure for loop select
- SQL Server stored procedure for next loop
- Foreach in SQL Server stored procedure
- Nested while loop in SQL Server stored procedure
- Do while loop in SQL Server stored procedure
SQL Server stored procedure for loop
SQL Server does not support FOR loop. However, you can use the WHILE loop to perform the same task. In this section, you will learn how you can implement the FOR loop functionality with the WHILE loops with the help of an example.
- Generally, when you use a FOR loop in any programming language, you perform a task a particular number of times. For example, you can write a PRINT statement 10 times with a single FOR loop.
- In most of the programming languages, there is an inintial value, a condition and an increment value in a For loop.
- You can define these things in the WHILE loop also and implement a similar mechanism.
Let us understand with an example.
- We have created a simple stored procedure that will take a number and print the integers from 1 to the number passed to the input parameter.
USE master
GO
CREATE PROCEDURE dbo.PrintNumbers @Number int
AS
BEGIN
DECLARE
@Counter int= 1
WHILE @Counter< =@Number
BEGIN
PRINT(@Counter)
SET @Counter= @Counter + 1
END
END
- Now we will execute the stored procedure.
USE master
GO
EXEC dbo.PrintNumbers 13

Thus, you might have understood how you can use the WHILE loop to implement the FOR loop mechanism in SQL Server.
Read: SQL Server stored procedure modified date
While loop in SQL Server stored procedure
In this section, you will learn about the use of the While loop in SQL Server. We have created an example where we have used the While loop in a SQL Server stored procedure.
One of the use cases of the While loop is to find the N terms of the Fibonacci Series. We have created a stored procedure that will take a number N and print the N number of terms of the Fibonacci Series.
USE master
GO
CREATE PROCEDURE dbo.Fibonacci @Num int
AS
BEGIN
DECLARE
@Num1 int,
@Num2 int,
@Num3 int,
@Counter int
SET @Num1= 0
SET @Num2= 1
SET @Counter= 0
SET @Num3=0
PRINT('Fibonacci Series')
PRINT(@Num1)
PRINT(@Num2)
WHILE @Counter < @Num - 2
BEGIN
SET @Num3= @Num1 + @Num2
PRINT(@Num3)
SET @Counter = @Counter + 1
SET @Num1 = @Num2
SET @Num2 = @Num3
END
END
- Now let us try to find 7 terms of Fibonacci Series.
USE master
GO
EXEC dbo.Fibonacci 7

Thus, you might have learned how you can use a While loop in a SQL Server stored procedure.
Read: Stored procedure for search functionality in SQL Server
Loop through table SQL Server stored procedure
In this section, you will learn how you can loop through a table in a SQL Server stored procedure.
There are different ways to loop through a table in SQL Server. In the above section, we discussed how to loop through a table using cursors. Therefore, this time we will not use cursors and use an alternative method.
- We have created a table named SalesTable.
- We will add an Identity Column to this table and use this column to loop through the table.
- We can add the identity column with the ALTER TABLE statement.
USE master
GO
ALTER TABLE dbo.SalesTable
ADD ID int IDENTITY(1,1)
- After adding the Identity column, the SalesTable looked like:

- Now we can use the While loop to iterate over the identity column and retrieve the results one by one.
- We have created the following stored procedure to implement this logic:
USE master
GO
ALTER PROCEDURE dbo.ProcedureSales
AS
BEGIN
DECLARE
@Init int= 1,
@NumRows int
SELECT @NumRows= COUNT(*) FROM dbo.SalesTable
WHILE @Init<= @NumRows
BEGIN
SELECT * FROM dbo.SalesTable WHERE ID= @Init
SET @Init= @Init + 1
END
END
- We have simply displayed the result on the output. You can also use these values wherever you want. For example, inserting these values into another table.
- Execeuting the stored procedure should give us the desired results.
USE master
GO
EXEC dbo.ProcedureSales

- As you can see in the output, we used a loop to access the table rows separately.
Thus, you might have learned how you can loop through a table using the SQL Server stored procedures.
Read: SQL Server stored procedure parameters
SQL Server stored procedure loop through result set
There are multiple ways for looping through table rows. In this section, you will learn how you can use the SQL Server Cursors to loop through the records.
We have created the following cursor inside a stored procedure that will fetch the employee details from the Employees table:

USE master
GO
CREATE PROCEDURE dbo.CursorEmployees
AS
BEGIN
DECLARE
@ID int,
@Name nchar(20),
@Department int
DECLARE cursor_employees CURSOR FOR
SELECT EmpID, EmpName, DepID FROM dbo.Employees
OPEN cursor_employees
FETCH NEXT FROM cursor_employees INTO @ID, @Name, @Department
PRINT('Employee ID ' + 'Employee Name ' + 'Department ')
WHILE @@FETCH_STATUS=0
BEGIN
PRINT(STR(@ID)+' '+ @Name + STR(@Department))
FETCH NEXT FROM cursor_employees INTO @ID, @Name, @Department
END
CLOSE cursor_employees
DEALLOCATE cursor_employees
END
- Firstly, we have created three variables to store the record data, fetched from the table.
- Secondly, we have created a cursor that will retrieve all the data from the table.
- Then we opened the cursor and started fetching the rows one by one and store the values into the variables and display them in the output.
- The @@FETCH_STATUS is a global variable. Its value is set to 0 if there there are some records to be traversed. Its value becomes 1 once all the records are traversed.
- Once you have fetched all the rows throuh cursor, you need to close the cursor.
- You should also deallocate the cursor when you do not need it. It releases the system resources.
- Now let us execute it and see the results.
USE master
GO
EXEC dbo.CursorEmployees

Thus, with this basic example, you might have learned how you can use the cursors to loop through the table rows.
Read: SQL Server stored procedure vs function
SQL Server stored procedure for loop insert
In this section, you will learn how you can insert records into a table using a loop in the SQL Server stored procedure.
- Consider the following Products table.

- We will use a loop inside a stored procedure that will check the rating of the product and insert that row into the NewProducts table if the rating is good i.e. greater than 8 in our case.
- Initially the NewProducts table is empty.
- We have created the stored procedure as:
USE master
GO
CREATE PROCEDURE dbo.InsertNewProducts
AS
BEGIN
DECLARE
@ID int,
@Name nchar(30),
@Price real,
@Rating int
DECLARE cursor_products CURSOR FOR
SELECT * FROM dbo.Products
OPEN cursor_products
FETCH NEXT FROM cursor_products INTO @ID, @Name, @Price, @Rating
WHILE @@FETCH_STATUS=0
BEGIN
IF @Rating > 8
BEGIN
INSERT INTO dbo.NewProducts( [Product ID], Name, Price, Rating)
VALUES(@ID, @Name, @Price, @Rating)
END
FETCH NEXT FROM cursor_products INTO @ID, @Name, @Price, @Rating
END
CLOSE cursor_products
DEALLOCATE cursor_products
END
- Firstly, the cursor in the stored procedure will retrieve all the records from the Products table.
- After that, when the cursor is opened, we are fetching the rows one by one.
- Then every record is compared with the condition and the product having rating greater than 8 will be inserted into the NewProducts table.
- Let us execute this stored procedure now.
USE master
GO
EXEC dbo.InsertNewProducts
SELECT * FROM dbo.NewProducts

- As you can see the new table is populated with the products having the rating greater than 8.
Thus, you might have learned how you can use the While loop in a stored procedure to insert data into a table.
Read: How to Create a Database in SQL
SQL Server stored procedure for loop select
Earlier, we discussed that SQL Server does not support For loop. We can use the While loop to simulate the use of For loop. In this section, we will use a loop to iterate over the resultset returned by the SQL Server.
- We have a table named Persons.
- We will use a Select statement to retrieve data from this table and fetch this data row by row within a loop.
- We created the stored procedure as:
USE master
GO
ALTER PROCEDURE dbo.CursorPersons
AS
BEGIN
DECLARE
@Fname nchar(20),
@Lname nchar(20),
@Phone nchar(20)
DECLARE cursor_Persons CURSOR FOR
SELECT [First Name], [Last Name], Phone FROM dbo.Persons
OPEN cursor_Persons
FETCH NEXT FROM cursor_Persons INTO @Fname, @Lname, @Phone
PRINT('First Name ' + 'Last Name ' + 'Phone ')
WHILE @@FETCH_STATUS=0
BEGIN
PRINT(@Fname + @Lname + @Phone)
FETCH NEXT FROM cursor_Persons INTO @Fname, @Lname, @Phone
END
CLOSE cursor_Persons
DEALLOCATE cursor_Persons
END
- Executing the stored procedure will give us the desired result.
USE master
GO
EXEC dbo.CursorPersons

- All the rows are printed on by one with the PRINT statement inside the loop.
Thus, you might have learned how you can loop through a Select statement in a SQL Server stored procedure.
Read: How to create functions in SQL Server Management Studio
SQL Server stored procedure for next loop
SQL Server does not support the For loop. Instead, you can use the WHILE Loop to implement the same functionality. This whole article is about the While loop in SQL Server. However, if you want a related example, you can refer to the next section below.
Foreach in SQL Server stored procedure
In this section, you will see an example where we will use the While loop for simulating the use of For each loop. In most programming languages, we use the For-Each loop to access any array or list.
In SQL Server, if you want to access the rows in a way as you do with the For-Each loop in other programming languages, you have to use the While loop. Below is an example for the demo:
- You need a unique column in the table over which the loop will iterate.
- If you do not have such a column, you can simply add an Identity column to the table, then iterate over that column values.
- We have a table Persons.
- We will add an identity column to this table.
USE master
GO
ALTER TABLE dbo.Persons
ADD ID int IDENTITY(1,1)
- Now we will create a stored procedure that will iterate over each row in the table.
USE master
GO
ALTER PROCEDURE dbo.ForEach
AS
BEGIN
DECLARE
@counter int= 1,
@maxcount int
SELECT @maxcount= COUNT(*) FROM dbo.Persons
WHILE @maxcount >= @counter
BEGIN
SELECT [First name], [Last Name] FROM dbo.Persons WHERE ID= @counter
SET @counter= @counter + 1
END
END
- Let us execute the stored procedure:
USE master
GO
EXEC dbo.ForEach

You can use this code block to implement the For-Each loop functionality as we are iterating over each row of the table. Thus, you might have learned how you can simulate the For-Each loop functionality with the While loop in SQL Server.
Read: SQL Server Convert Function
Nested while loop in SQL Server stored procedure
In this section, you will learn about the nested while loops in SQL Server stored procedure. A nested loop means using a loop inside the other loop. In SQL Server, you can use the nested While loops. We have created an example of a stored procedure where we have used the concept of nested loops.
- The following stored procedure prints the multiplication table of @Num numbers.
USE master
GO
CREATE PROCEDURE dbo.MultiTable @Num int
AS
BEGIN
DECLARE
@Counter1 int= 1,
@Counter2 int= 1
WHILE @Counter1 <= @Num
BEGIN
SET @Counter2 = 1
WHILE @Counter2 <= 10
BEGIN
PRINT(@Counter1 * @Counter2)
SET @Counter2= @Counter2 + 1
END
SET @Counter1 = @Counter1 + 1
END
END
- The first While loop iterates from 1 to the @Num upto which you want to print the multiplication table.
- The second While loop iterates from 1 to 10 i.e. first 10 multiples of the number.
- Let us execute this stored procedure:
USE master
GO
EXEC dbo.MultiTable 2

- We specified 2 as the input parameter. Therefore, the stored procedure printed the multiplication table of 1 and 2.
Thus, you might have understood how you can use the nested While loops in a SQL Server stored procedure.
Do while loop in SQL Server stored procedure
The SQL Server does not support a do-while loop. In a Do-While loop, the first iteration is done before checking the condition. After checking the condition, the rest of the iterations are completed.
You have to use the While loop to simulate the use of the Do While loop. We have discussed how to use the While loop in the above sections.
And you may also like reading the following articles.
- SQL Server Substring Function
- SQL Server Substring Function [9 Examples]
- SQL Server Convert Function + Examples
- SQL Server Convert Datetime to date
- SQL Server stored procedure parameters
- Full-text search in SQL Server
In this tutorial, we have discussed Loop in SQL Server stored procedure and we have also covered the following topics.
- SQL Server stored procedure for loop
- While loop in SQL Server stored procedure
- Loop through table SQL Server stored procedure
- SQL Server stored procedure loop through result set
- SQL Server stored procedure for loop insert
- SQL Server stored procedure for loop select
- SQL Server stored procedure for next loop
- Foreach in SQL Server stored procedure
- Nested while loop in SQL Server stored procedure
- Do while loop in SQL Server stored procedure
I am Bijay having more than 15 years of experience in the Software Industry. During this time, I have worked on MariaDB and used it in a lot of projects. Most of our readers are from the United States, Canada, United Kingdom, Australia, New Zealand, etc.
Want to learn MariaDB? Check out all the articles and tutorials that I wrote on MariaDB. Also, I am a Microsoft MVP.