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
how to use loop inside my procedure for all month dates..
my procedure is:
alter procedure dateWiseAttnd
as
begin
select Employee,
max(case when day(att_date)=’01’ then Attendance_Status else ” end) ‘day1′,
max(case when day(att_date)=’02’ then Attendance_Status else ” end) ‘day2′,
max(case when day(att_date)=’03’ then Attendance_Status else ” end) ‘day3′,
max(case when day(att_date)=’04’ then Attendance_Status else ” end) ‘day4′,
max(case when day(att_date)=’05’ then Attendance_Status else ” end) ‘day5′,
max(case when day(att_date)=’06’ then Attendance_Status else ” end) ‘day6′,
max(case when day(att_date)=’07’ then Attendance_Status else ” end) ‘day7′,
max(case when day(att_date)=’08’ then Attendance_Status else ” end) ‘day8′
from
(select e.EMP_FIRST_NAME+’ ‘+e.EMP_LAST_NAME Employee,a.Attendance_Status,a.att_date
from attendance_report a,EMPLOYEE_MASTER e
where MONTH(a.att_date)=’07’
and YEAR(a.att_date)=’2011′
and a.emp_id=e.ID
)tbl
group by Employee
end
Pinal Dave thanks a lot for the example..
Fernando
from Bolivia
Hi Pinal,
I got a task to do.I need to insert 400,000 rows into Adventure Works DB table inorder to see how Proactive caching works in different scenarios.
My question is how to insert 400,000 rows at a time in a table.
Can you let me know the script to generate it.
Please do help me out.
Thanks in Advance.
Hello Sir,
I have Temp table with the column(rec_seq,acc_id,tax_id,zip,party_name,addr_line1,addr_line2) and I want to display table with different columns in each row like :
First row : rec_seq,acc_id,tax_id,zip (wehre rec_seq =1)
Second row : party_name,addr_line1,addr_line2 (where rec_seq=2)
and store this result into another table
How can I do that..
please do help me out.
Thanks in advance.
Hello Sir,
I have one table named “Account” and fields are(rec_seq,acc_id,security_key,party_name,party_addr_line1) I want to display different coulmns in each row :
output should be :
First row :
rec_seq,acc_id,security_key (where rec_seq=1 i.e. display selected column when rec_seq =1 )
Second row:
rec_seq,party_name,party_addr_line1,party_addr_line2 (where rec_seq=2i.e. display selected column when rec_seq =2)
Right now Account table data look like below:
rec_seq acc_id security_key party_name party_addr_line1
1 10001 1 NULL NULL
1 10002 2 NULL NULL
2 10002 NULL b ab
1 10002 3 NULL NULL
2 10002 NULL b bc
2 10002 NULL jk lm
I want to fetch data from this table and display output like :
(rec_seq=1 so display rec_seq,acc_id & security_id)
(rec_seq=2 display party_name and party_addr_line1)
1 10001 1
1 10002 2
2 10002 b ab
1 10002 3
2 10002 b bc
2 10002 jk lm
Please help me out..
Thanks in advance!!
thank you very much
thank you very much, you save me live.
–Select name FROM sys.databases order by name
–Create DataBase And Drop Database
declare @i int
declare @sSQL nvarchar(50)
–Create DataBase
set @i=1
while (@i<=10)
begin
print @i
set @sSQL='Create database abc'+ cast(@i as nvarchar(50))
EXEC(@sSQL)
set @i=@i+1
end
–Drop DataBase
set @i=1
while (@i<=10)
begin
print @i
set @sSQL='Drop database abc'+ cast(@i as nvarchar(50))
EXEC(@sSQL)
set @i=@i+1
end
i want program in for..loop ..give some example
I have one query below is the details,
Tab:
col1 Key col2 startdate enddate
1 10 100 20111215 29991231
1 10 100 20111220 20111225
1 10 100 20111215 29991231
1 10 100 20111215 29991231
I want output as
col1 Key col2 startdate enddate
1 10 100 20111215 20111219
1 10 100 20111220 20111225
1 10 100 20111215 20111224
1 10 100 20111225 29991231
i think need to write a loop which will update the endate properly.
Please help…
Hi everyone,
I have a table which holds data regarding ‘TicketNo’,its ‘Mkrdt’,’Loginid’,’Mkrid’ etc.. I want to list the ‘Startdate’ of each ticket , coz each ticket processing is done many days , and i want to also list ‘Days Taken’ for processing that ticket no.. I had a solution, but i get parcial results..I WANT TO LIST STATDATE N DATSTAKEN IN FRONT OF EACH ENTRY OF A PERTICULAR TICKET. .. I had a query related to this as ..
SELECT a.ticketno, a.mkrdt
FROM Tbl_Newtagg1 a, (SELECT TicketNo, min(mkrdt) AS StartDate FROM Tbl_Newtagg1 GROUP BY TicketNo) b
WHERE a.TicketNo = b.TicketNo AND a.mkrdt =b.startdate
–but it lists every ticket n its startdate only once.. plz help.. Thanks in advance
try using UNION function with above query and other query which will give only start date
you just copy all your sample in msdn you – oink oink.
Great Examples!
I would love to share this on my blog.
Please let me know if you would like to keep this posting content within your blog only.
Thank you
Enma
Nice way
Genial !! muchas gracias.
The best way is use a cursor:
example To Delete All Products
and if 1 product record has dependencies
For each Product record, there is a record also in Product Inventory table
DECLARE
cursor c1 is
SELECT
ID AS i_PRODUCTID,
FROM PRODUCT
ORDER BY ID ASC;
BEGIN
FOR DELETEPRODUCTRECORD in c1
LOOP
DELETE FROM PRODUCT_INVENTORY WHERE PRODUCTID = DELETEPRODUCTRECORD .i_PRODUCTID;
DELETE FROM PRODUCT WHERE ID = DELETEPRODUCTRECORD .i_PRODUCTID;
END LOOP;
END;
==
In terms of performance and simplicity, this is the best method i have seen
hi i am shunmuga sundari,
i have one doubt that ” is it possible execute a ‘insert into’ query into the while or for loop?”
how to use vb function to sql query function
ex:
function emp()
str=”select * from employ”
set rs=conn.execute(str)
do while rs.eof=false
for i= 1 to 6
if sex=’m’ then
update status=’male’ where empcode=” & rs!emocode & ”
else
update status=’female’ where empcode=” & rs!emocode & ”
next
loop
end function
Can we replace column values of a table with a series of values using While statement?
hi i have a string like
HHHHHHsuhhhsusu each character represents a date starting from 28/07/2012 and im trying to select it or create a table
create table #calender (string as char(5)COLLATE SQL_Latin1_General_CP1_CI_AS, dat datetime)
declare @counter as int
set @counter = 3
while (@counter < 367)
begin
–print @counter
–insert into #calender (string,dat)
select distinct substring (CCAL_Available_Days
,@counter,1), dateadd (day,@counter,'2012-07-28 00:00:00.000') date from dbo.CCALCalend
where CCAL_Year = '2012'
set @counter = @counter+1
–select * from #calender
end
drop table #calender
i am getting the error
The column "string" cannot be modified because it is either a computed column or is the result of a UNION operator.
can you please help