The WHILE loop in SQL Server is straightforward to understand and widely used for iterative operations. While most users grasp its functionality quickly, a few might find it slightly confusing initially. This blog explains the simple usage of the WHILE loop along with the keywords CONTINUE and BREAK, which control the flow within the loop. Let us learn about break keywords.
The BREAK keyword terminates the WHILE loop entirely, transferring control to the first statement after the loop. In contrast, the CONTINUE keyword skips the remaining statements in the current iteration and jumps back to the beginning of the loop for the next iteration.
1) Example of WHILE Loop
DECLARE @intFlag INT SET @intFlag = 1 WHILE (@intFlag <=5) BEGIN PRINT @intFlag SET @intFlag = @intFlag + 1 END GO
ResultSet:
1
2
3
4
5
2) Example of WHILE Loop with BREAK keyword
DECLARE @intFlag INT SET @intFlag = 1 WHILE (@intFlag <=5) BEGIN PRINT @intFlag SET @intFlag = @intFlag + 1 IF @intFlag = 4 BREAK; END GO
Result
Set:
1
2
3
3) Example of WHILE Loop with CONTINUE and BREAK keywords
DECLARE @intFlag INT SET @intFlag = 1 WHILE (@intFlag <=5) BEGIN PRINT @intFlag SET @intFlag = @intFlag + 1 CONTINUE; IF @intFlag = 4 -- This will never executed BREAK; END GO
Result Set:
1
2
3
4
5
I hope you find this simple blog post helpful. Let me know what you think of this blog post. Please leave your views in the comment sections. These examples illustrate how WHILE loops and control keywords function in SQL Server. Feel free to share your thoughts in the comments below.
You can follow me on YouTube.
Reference: Pinal Dave (https://blog.sqlauthority.com)
182 Comments. Leave new
is there a instruction like foreach row???
It is possible only in a cursor and not in while loop
nice article.
but how can we iterate through each record obtained from the select statement in the stored procedure?
thanks in advance
You can use a cursor. But I think you can avoid a cursor using a set based approach. Let us know what you want to do with each row
Thanks
How to give a static prefix for the number incremented
You can do it in many methods
1 If you use front end application to show data, prefix it when displaying
2 SELECT ‘prefix_’+cast(col as varchar(10)) from your_table
sir,
i had a doubt that is can we use while loop with select satement ? example i want to extract name one by one by using while loop in sql is it possible?
Why do you want to get the names one by one?
You can write a SELECT statement that brings you all the names
please reply me …
my question is….
if i have a table with a field date_time..
and i want to run a query when i found date_time matching with GETDATE() and continuously checking the date_time field.
so this is how i have to write a loop or what ever you suggest …. to continuously running a loop ( endless loop)
WHERE
date_col>=dateadd(day,datediff(day,0,getdate()),0) and
date_col<dateadd(day,datediff(day,0,getdate())+1,0)
alter PROCEDURE [dbo].[usp_AddUpdatePatientRegistration]
(
@i int=0,
@str1 nvarchar(50),
@str nvarchar(50) output
)
AS
BEGIN
set @str1=(select tree_name from user_tree where app_no=’090001′)
while(@i < @str1)
begin
set @str=@str +”+@str1
end
END
This is my produture not run
i want to @str1 select lotsof rows that rows are concanect one string in how
and return that value
select @str=coalesce(@str+”,”)+user_tree where app_no=’090001′
Thank You Very Much…
nice loop
nice helped me alot, but i am looking to iterate the loop no of rows in a table can any body help me
@Shafique,
Declare @Var int
Set @Var = 1
While @Var < = ( Select Count(*) from Table_Name)
Begin
perform action by executing scripts
Set @Var = @Var+1
End
Should work, if you have questions, please let us know.
~ IM.
hello sir,
In sqlserver 2005 i had a table containing 3 lakh rows …i need to display 50000 records each time using loop.can anybody help me….
thnks in advance.
If you want to randomly show 50000 rows, use
Select top 50000 col…. from your_table
order by newid()
Hi Teja,
Why you need to loop thru it and display 50000 rows?
Its better to write query such a way, so it gives result of 50000 rows that you want to display.
LOOP will decrease the performance.
Thanks,
Tejas
SQLYoga.com
Hello Sir,
Can we use while loop in stored procedures??
If so please explain how to use?
Thanks,
Anjaneyulu V
@Anjaneyulu
create proc USP_Example_Sp
AS
SET NOCOUNT ON
Declare @count int
Set @count = 1
Declare @table table ( id int Identity , Ename Varchar (40))
While @Count < = 10
Begin
Insert into @table values ('Imran')
Set @Count = @Count + 1
End
Select * from @table
SET NOCOUNT OFF
go
Exec USP_Example_Sp
go
Drop proc USP_Example_Sp
~ IM
willu please give me simple store
procedure programs
Thanks and Regards
Umar
Nice one
Nice article
Thanks alot !!!
Thank you. Very helpful article indeed.
Can anyone give me some inputs on this one.
Is there a way to implement a loop similar to a ‘do-while’ so that the block is executed atleast once?
Thanks in advance.
Hello Pritam,
Use a variable in while loop as below:
@declare @val bit
set @val = 1
while @val
begin
…
your code
set @val= some condition to run while loop
end
Regards,
Pinal Dave
I think
while @val
should be
while @val=1
Hi Pinal Dave,
I am doing as an asp.net developer where i have to do back end coding too. Really you are doing good . we have to be great ful for ever to you. moreover i need a help about how sql server excuting each query?.i means that just wanna know about internal process of SQL server. it would be great if could help me out.
Hi sr, i am doing a store procedure but dont work, i need to update a inventory from a invoice when this is canceled,
table products
prod_id
prod_desc
prod_invent
table invoice
invoice_id
invoice_date
table invoicedet
invoicedet_id
invoice_id
prod_id
invoice_cant