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://www.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?