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
id mbrshp_id org strt_dt OPEN_acct LOM RFND
246812 580993 01103 1973-10-01 NULL NULL Yes
246812 580993 01064 1974-11-18 NULL NULL Yes
246812 580993 01025 1975-08-25 NULL NULL Yes
246812 581554 01025 1979-09-25 NULL NULL Yes
246812 581553 01104 1983-06-01 NULL NULL Yes
246812 581553 01051 1984-11-19 NULL NULL Yes
246812 581553 01104 1985-04-01 NULL NULL Yes
246812 72007 01025 1987-05-08 NULL NULL NULL
246812 72007 01038 1997-12-01 NULL NULL NULL
246812 72007 01246 2003-07-01 Yes NULL NULL
500128 126830 03453 1989-09-05 NULL NULL NULL
500128 126830 4338 1994-07-01 Yes NULL NULL
652678 140780 01061 2000-04-04 Yes NULL NULL
I opologize for not able to remove text wrap in the above data.
For each id, I need to pull the orgs grouped by mbrsp_id, open_acct, LOM and RFND. From the above data I need three different groups for each id as follows.
The open_acct block for id 246812 will contain the following three records:
id mbrshp_id org strt_dt
246812 72007 01025 1987-05-08
246812 72007 01038 1997-12-01
246812 72007 01246 2003-07-01
The LOM block for id 246812 will not contain any records as there are no records with a yes in LOM column for that id.
The RFND block for id 246812 will contain the following seven records:
id mbrshp_id org strt_dt
246812 580993 01103 1973-10-01
246812 580993 01064 1974-11-18
246812 580993 01025 1975-08-25
246812 581554 01025 1979-09-25
246812 581553 01104 1983-06-01
246812 581553 01051 1984-11-19
246812 581553 01104 1985-04-01
The open_acct block for id 500128 will contain the following two records:
id mbrshp_id org strt_dt
500128 126830 03453 1989-09-05
500128 126830 4338 1994-07-01
The LOM block for id 500128 will not contain any records as there are no records with a yes in LOM column for that id.
The RFND block for id 500128 will not contain any records as there are no records with a yes in RFNDcolumn for that id.
The open_acct block for id 652678 will contain the following two records:
id mbrshp_id org strt_dt
652678 140780 01061 2000-04-04
The LOM block for id 652678 will not contain any records as there are no records with a yes in LOM column for that id.
The RFND block for id 652678 will not contain any records as there are no records with a yes in RFND column for that id.
Thanks for your help
I want to do bulk insert based upon one condition. How can I do with SQL Server?
Here is my query
—————————-
SELECT * FROM Firm f
WHERE not EXISTS
(SELECT * FROM SBEFirms s WHERE f.id = s.id )
————————–
So I want to insert f.id to other table called temp in above loop itself.
How can I do that?
Thank you
insert into temp(id)
SELECT id FROM Firm f
WHERE not EXISTS
(SELECT * FROM SBEFirms s WHERE f.id = s.id )
sir, i want to update a table1 using another table2 in a selected fields only if the data is existing otherwise if the data is not existing needs to create a data on that table1..can you give me some sample on that.
Thanks in advance
This is the general logic
update t1
set col1=t2.col1, col2=t2.col2…
from table1 as t1 inner join table2 as t2
on t1.keycol=t2.keycol
insert into table1(column_list)
select column_list from table2 as t2
where not exists(select * from table1 where keycol=t2.keycol)
Thanks a lot………….
thnks………..its very helpfull
welcome
hi
sir i need to a sample of a loop in after insert trigger
plz
tnx a lot
Can you explain why you want this?
Hi Sir ,
Your posts helped me not once but Several times, Keep up the good work which helps beginners Like me to grasp concepts with in no time !!
Cheers
Naveen Kasa
Help !!
I wanted to take current Year in a temp Variable and need +10 and -10 values
Example :
2001
2002
2003
2004
.
.
2011
2012
2013
.
.
2020
how to get this result ??
Please Help me
select year(getdate())-number as year from master..spt_values
where type=’p’ and number between 1 and 10
union
select year(getdate())+number from master..spt_values
where type=’p’ and number between 1 and 10
order by year
i have 2 tables t1 and t2
in first and second table i have data like this
create table temp.t1 (id int,Name varchar(200),Normalized varchar(200))
create table temp.t2 (OrgName varchar(20),ModifiedName varchar(20))
insert into temp.t1 values(111,’Tata Motors co. limited’,”)
insert into temp.t1 values(112,’Tata Motors co.’,”)
insert into temp.t1 values(112,’Tata Motors c.o.’,”)
insert into temp.t1 values(113,’Tata Motors corp.’,”)
insert into temp.t1 values(114,’Tata Motors ltd.’,”)
insert into temp.t1 values(114,’Tata Motors’,”)
insert into temp.t2 values(‘limited’,’Ltd’)
insert into temp.t2 values(‘co.’,’Co’)
insert into temp.t2 values(‘c.o.’,’Co’)
insert into temp.t2 values(‘corp.’,’Corp’)
insert into temp.t2 values(‘ltd.’,’Ltd’)
select * from temp.t1
select * from temp.t2
and result should be The results look like this: from table1
update the normalized column based on table2
111 Tata Motors co. limited Tata Motors co Ltd
112 Tata Motors co. Tata Motors Co
112 Tata Motors c.o. Tata Motors Co
113 Tata Motors corp. Tata Motors Corp
114 Tata Motors ltd. Tata Motors Ltd
114 Tata Motors Tata Motors
Notice that the first record replaced ‘limited’ and ‘co.’
BookISBN BookTitle SubjectName AuthorName PublisherName
1 java hassan abod ali
1 java hassan ahmed ali
1 java program abod ali
1 java program ahmed ali
2 C++ program laith abod
3 C## hiiii laith abod
how to show in data grid view ??
BookISBN BookTitle SubjectName AuthorName PublisherName
1 java hassan,program abod,ahmad ali
BookISBN BookTitle SubjectName AuthorName PublisherName
1———– java—— hassan——- abod———–ali
1———– java—— hassan——–ahmed——–ali
1———– java——-program——-abod———-ali
1———- java——–program——-ahmed——- ali
2———- C++——- program——-laith——— abod
3———- C##——- hiiii————- laith——— abod
how to show in data grid view ??
BookISBN BookTitle SubjectName AuthorName PublisherName
1———– java——-hassan,program–abod,ahmad–ali
??????
I need complete information
hello ,
i need to write a funtion that shows , by book name show me the author name.
could u plz help me.
no i cant help u
Hey,
I was wondering if you had a bulk insert todo could you use a while loop if the data was all ints and had a pattern.
c1 c2 c3
1—1—x
2—1—x
3—1—x
4—1—x
5—1—x
6—1—x
1—2—x
2—2—x
3—2—x
ect…
every 6 rows. Also x is a boolean where if c2 >= c1, x = true.
So can i nest an if(for x) in a while in a while?
Usually i would use a for loop.
ps. this is to populate a new empty tabel.
Jason
Can you please post, your required output format.
Its very difficult to understand.
~IM.
Hello Sir,
whenever i am stuck in sql problems i always refer your articles, it has always helped me.
thanks for sharing ur knowledge with everyone.
Great admirer,
Priya
Hello Sir,
Thank you for your post. Its very useful.
I receive a comma delimited text file from a trade partner that has three fields. The first contains a unique record ID, the second contains a series of one or more values called keycodes that are pipe delimited internally between each value within the field. Sometimes there is only one keycode value in the second field, and sometimes there are as many as a hundred. Additionally, the width of each value in the keycde field is inconsistent, ranging from 5 to 7 characters. The third field contains a number indicating how many values are in the internally pipe delimited keycode field. I need to import the data into a SQL Server table and flip the data so that I get one record for each combination of record ID and value in the second field. I’m using an ODBC Text File driver to read the data.
For example, I want the imported table that looks like this:
recordid, keycode, multiples
100001, AAA100|AAA101|AAA102, 3
100002, AAB100|AAAB101, 2
100003, AAAA100|AAB102|AAAC100|AAD100, 4
To look like:
recordid keycode
100001 AAA100
100001 AAA101
100001 AAA102
100002 AAB100
100002 AAAB101
100003 AAAA100
100003 AAB102
100003 AAAC100
100003 AAD100
Thank you in advance for any help!
DECLARE @intFlag INT
SET @intFlag = 1
WHILE (@intFlag <=5) BEGIN
print @intflag
SET @intFlag = @intFlag + 1
IF @intFlag = 4
Set @intFlag = @intFlag + 1
END
GO
This code work on the third part.
3) Example of WHILE Loop with CONTINUE and BREAK keywords
1
2
3
5
it skip the 4 :p
Thank u sir.
It helped me a lot.
Mw tanya gan caranya buat bintang-bintang dari SQL Server 2005 gmna :
Contoh :
*
* *
* * *
* * * *
* * * * *
* * * * * *
* * * * * * *
Mksh gan
Can you post your question in English language? My guess is that you want the following code
select replicate(‘*’,number) from master..spt_values
where type=’p’ and number between 1 and 7