This is question is one of those question which is very simple and most of the users get it correct, however few users find it confusing for first time. I have tried to explain the usage of simple WHILE loop in first example. BREAK keyword will exit the stop the while loop and control is moved to next statement after the while loop. CONTINUE keyword skips all the statement after its execution and control is sent to first statement of while loop. Run following examples in Query Editor and see the result. This is very easy to understand example.
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
ResultSet:
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
ResultSet:
1
2
3
4
5
Reference : Pinal Dave (http://blog.SQLAuthority.com)
pinaldave
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
Realy it helped me on time for while command in sql
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.
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
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.
good knowledge.. TQ
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 /
Nice tutorial, But I coudn’t find what I wanted.
How can I update many row using sql loop query ???
Very GOOD Example
Keep it up
Thank you very much for your help on this example.
i want a function for factorial
tx
Thanks
Cheers Dude
Thankyou Dave, it helped me a lot….
Hi!
What is difference between navigating records in while loop and cursor ?
thanks………
is there a instruction like foreach row???
nice article.
but how can we iterate through each record obtained from the select statement in the stored procedure?
thanks in advance
Thanks
How to give a static prefix for the number incremented
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?
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)
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
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.
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