Loop in SQL Server stored procedure (with examples)

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