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
Hello there,
I wanted to thank you for all the help you provide in here.
In regards to this while loop with ‘continue’ and ‘break’, if you are trying to avoid printing the ‘4’, wouldn’t it be more accurate to code it like this?
DECLARE @intFlag INT
SET @intFlag = 1
PRINT @intFlag
WHILE (@intFlag <=5) BEGIN
SET @intFlag = @intFlag + 1
IF @intFlag = 4 — This will never executed
CONTINUE;
ELSE
PRINT @intFlag
END
GO
thanks. it helps. :)
Thanks for your help….
Realy it helped me on time for while command in sql
Thanks.
thanks
Hi,
if i want user enter the number and then while loop run for that many times like i wrote small program for the table
declare @i int
declare @j int
print ‘enter number’ @j
set @i=1
while (@i<=5)
begin
print @i
set @j=@i*@j
print @j
set @i=@i+1
end
go
it shows incorrect syntax error near ‘@j’
Would you please tell me the correct way of entering number(@j) from the user.
It is the job of front end to prompt for the user input
You can’t do it from sql
in third line there is a error i think because u need to u concatination symbol ie + u have use then it will work fine
U can follow below the code, u did not Get any Error.
Please Let me Know, If u need any further information in this regard.
declare @i int
declare @j int
print ‘enter number’+ Cast(@j as varchar)
set @i=1
while (@i<=5)
begin
print @i
set @j=@i*@j
print @j
set @i=@i+1
end
go
hi i am really stuck in my assingnment……can u help me please?
Hai friend
In the above code is there one mistake that is @j=@i*@j it is not possible that at that place you have to put the @j=@i*1 …………………ok
Hi, @j needs an initial value before going to the loop: Set @j= 1 (for example or some thing like that) so the statement below can be executed properly:
ser @j=@i*@j
@EdwardJD
I didn’t understand the question.
In the 3rd line ur trying to print @j but no value is assigned to @j .. 1st assign the value and print
you need to use parameter for that.and u need to use cast function in print statement to convert int to varchar….
create proc d88(@j int)
as
declare @i int
–declare @j int
print ‘enter number for @j’ +cast(@j as varchar(10))
set @i=1
while (@i<=5)
begin
print @i
set @j=@i*@j
print @j
set @i=@i+1
end
exec d88 2
create proc d88(@j int)
as
declare @i int
–declare @j int
print ‘enter number for @j’ +cast(@j as varchar(10))
set @i=1
while (@i<=5)
begin
print @i
set @j=@i*@j
print @j
set @i=@i+1
end
exec d88 2
Thank you..helped me a lot..
HI
Could you please explain the cursor vs while loop ..
Is both are performance killer … how the while differ from cursor
Both are ineffecient for large set of data
However it depends
There is a chance that your cursor hit a table only once and some manipulations are done on the data. Whereas while loop may hit a table for each loop.
Hi, Please help me in dis,
i want to update the like first i need to check the value whether it is null or blank.. if yes den update the Null record to 7777(1st value), for the next Value updation it suppose to add 7777+1,next 7777+1+1, it shd go like dis… n store error message ‘Blank record or Null Record.’
Refer this quirky update technique
Thank you ………….
Help me a lot…………………….
sir,
i want function in that function i have to pass table name, column name and its data type. and when i execute that function at that time column should add in table. table name and column name will be same as parameter of function.
You cannot do this from a function. Use a stored procedure for this becuase you need to write dynamic sql
good knowledge.. TQ
nice1
Thanks man..
It really saved my ass.
:->
Thanks,
It had given me a good Idea to solve errors in my project.
I want to use while loop inside the Common Table Expression.
can U tell me how to use it /
You can use union or union all inside a CTE
Nice tutorial, But I coudn’t find what I wanted.
How can I update many row using sql loop query ???
Post some sample data with expected result
Very GOOD Example
Keep it up
Thank you very much for your help on this example.
i want a function for factorial
tx
declare @n int, @factorial int
select @n=5, @factorial=1
select @factorial=@factorial*number from master..spt_values
where type=’p’ and number between 1 and @n
select @factorial as factorial
Thanks
Cheers Dude
Thankyou Dave, it helped me a lot….
Hi!
What is difference between navigating records in while loop and cursor ?
It depends on what you want to do
Can you give us more informations on what you are trying to do?
thanks………