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
Sir, i want to writ a query parent child relation for mlm business
Read about Expanding hierarchies in SQL Server help file
Pinal-
I am not sure if this is related to this post or not but it appears you may be able to help me…
I have a table that looks like this:
COL1 COL2
11112 1
22221 1
33331 2
12312 3
82828 3
76767 3
and so on.
As you can see, I have a unique value in COL1 but the value in COL2 has duplicates within the table. I only need to keep 1 row of each distinct COL2 value and no longer care about the remaining rows. I dont even care which one I keep, but I have to keep just 1 of each.
Can you help me write a delete statement that will delete all of the surplus rows without deleting the last one?
I have run a statement to identify the rows where the condition occurs, and have done so successfully. Out of 4901 rows the condition occurs on 200 distinct COL2 values a total of 1109 times. I need to get rid of the 909 surplus rows.
Thank you for your time.
Hi Bradon,
If you are using SQL SERVER 2005 and above, you can use this query to identify Duplicate records and can delete it.
What I understand is: You need to have distinct value for Colmun2, don’t care about value in Column1.
Solution:
with cte as(
select ROW_NUMBER() OVER(PARTITION BY Col2 ORDER BY Col1) AS RowID,
FROM Table
)
SELECT *
from cte
Where RowID>1
This will list out rows that you want to delete. Please check result once to make sure. If it is fine, then you can change “select *” to ” Delete”.
Please check result first and take backup of that table first, if you have any doubt.
For reference to this query:
Thanks,
Tejas
Thank you Tejas
u can follow below of the Syntax, U did not get duplicate Records. If you have any probleam Please call this Mobile Number 9866179952
with cte as(SELECT a.* from (
select ROW_NUMBER() OVER(PARTITION BY Col2 ORDER BY Col1) AS RowID,
FROM Table) as a
)
The Stored Procedure has a date parameter.
exec myProc ‘2010-01-05’
How can I run myProc in a Loop specifying the date parameter. myProc should run in a loop while date
between ‘2010-01-05’ and ‘2010-01-25’.
What are you doing inside a procedure? Instead of calling the procedure 21 times in your case, you can have another parameter in the procedure which accepts to_date and execute the code inside a procedure as many times
Hi, I need to loop through a recordset that has multiple records that have the same data in the field:
demo table:
name grade
——- ——–
jim 90
jim 80
jim 70
jim 60
tom 30
tom 80
etc…
What I need to do is loop through this data and where the name is the same as the previous name, add the data of grade together. This is just a quick sample table show what the data could look like. I actually need to concatenate a ntext field but figure for simplicity to show example with an integer to add.
Thank you Brian, that works, the only issue now is that for some reason, the text “ ” is showing where carriage breaks are in the data. See the example data below. I changed the text field of the table to varchar(max) but I don’t think that is the issue.
Example text:
STEP #1:
this is a test
STEP #2:
this is step 2
STEP #3:
this is step 3
Looks like the text I was trying to paste didn’t come through or was converted.
Let me try adding some quotes around the text. ”’ ”’
STEP #1:”’ ”’
this is a test”’ ”’
”’ ”’
STEP #2:”’ ”’
this is step 2”’ ”’
”’ ”’
STEP #3:”’ ”’
this is step 3”’ ”’
”’ ”’
Third try the charm I guess, sorry the text is like this without spaces.
: & # x 0 D ;
Brian, I was able to use a replace function to remove the text but I am not a sql / xml expert but guess that take is XML version of carriage return.
@Jeffrey
Actually, i do not know.
[didn’t see your replies until just now]
Hello sir,
I have one question.
Is it possible to update the all columns of view which has been created with multiple tables.
Suppose I have 2 tables A & B
Columns in A are:
Name
Desig
StateID
Columns in B are:
StateID
StateName
I have created view MyView with these tables:
Columns in MyView are:
Name
Desig
StateName
So is this possible to update the values of all columns in MyView.
Thanks
@Ravish Dhingra
What is the problem? Just UPDATE the underlying TABLE and the VIEW will show the new data.
select ROW_NUMBER() OVER (ORDER BY DetailID ASC) AS ROWID,* from trnInvoiceTicket
What is the equivalent of the ROW_NUMBER() here in WHILE Loop?
hi mate, I have created this procedure but it wont give me require result. Can you please tell me what is wrong in this..
ALTER PROCEDURE dbo.GetBalance
(
@Sdate as DATETIME ,
@Edate as DATETIME ,
@OBNO as INT
)
AS
WHILE @EDATE < = '01/01/2009'
BEGIN
SELECT
SUM(GSXAM) AS EXPENCE,
SUM(CPAY) AS PAYMENT,
SUM(CPAY – GSXAM) AS BALANCE FROM ACCOUNTS WHERE OBNO = @OBNO AND (EXPDT BETWEEN @SDATE AND @EDATE);
Set @Edate = @Edate – 30
END
How are you executing the procedure? Post the code you have used. Also expalin what you are trying to do with the code
Thank You.
First time in three years I needed to do a loop within my stored procedure and you example worked well for my needs.
Sir,can u help me that my query is that,i have more colums that is PCS201,PCS202,uptp212 & more values stored in this columns condition is that All the column values checked if their value <=10 if 10 define another field & also count how many greater then 10 or less then 10. plz help me.
You need post some sample data with expected result to help you
hi
i want to iterate through Columns and update tham with any random value in specified range throug query how can i do this?
It would be helpful if you post some sample data with expected result
I see keyword “return” is also working instead of “break” keyword.
Return will not execute any statements at the end of while loop
Thanks its vary useful.
sir please give an example of while statements using geany?
The SQL Server help file has explanation as well as code examples for While loop
|ATA |SUBATA
|Chapter |Chapter description
| |SUBATA |SUBATA DESC
| | SUBATA |Subata description details
assuming the *|* is table.
how to design a query like this?
anyone have idea?
still new in this. :)
I am not sure if I am going about this the right way, but I would like to take this query I created and output it as one big table opposed to multiple tables. I see why its creating multiple tables because the select is in the loop.. however, I am not sure how else to do it.
declare @YEAR int
declare @LASTYEAR int
declare @TOTAL int
declare @PUSHED int
declare @REMAINING int
SET @YEAR = (select min(distinct year(exam_scheddate)) from T_STUDY)
SET @LASTYEAR = (select max(distinct year(exam_scheddate)) from T_STUDY)
While @YEAR <= @LASTYEAR
BEGIN
SET @TOTAL = (select count(*) from T_STUDY where year(exam_scheddate) = @YEAR)
SET @PUSHED = (select count(*) from T_STUDY where year(exam_scheddate) = @YEAR and is_exported = 0)
SET @REMAINING = (select count(*) from T_STUDY where year(exam_scheddate) = @YEAR and is_exported = 1)
select @YEAR, @TOTAL, @PUSHED, @REMAINING
set @YEAR = @YEAR + 1
end