WHILE loop is the looping construct supported by Sql Server. Sql server doesn’t have for…loop, do…while loop etc, but with WHILE loop we can simulate these missing looping constructs behaviour.
This article will cover the following:
- Introduction to WHILE loop
- BREAK Statement
- CONTINUE Statement
- DO WHILE loop
- Looping through table records
A while loop will check the condition first and then executes the block of Sql Statements within it as along as the condition evaluates to true.
Example: Basic while loop example. The below while loop executes the statements within it 4 times.
DECLARE @LoopCounter INT = 1 WHILE ( @LoopCounter <= 4) BEGIN PRINT @LoopCounter SET @LoopCounter = @LoopCounter + 1 END
If a BREAK statement is executed within a WHILE loop, then it causes the control to go out of the while loop and start executing the first statement immediately after the while loop.
Example: WHILE loop with BREAK statement
DECLARE @LoopCounter INT = 1 WHILE ( @LoopCounter <= 4) BEGIN PRINT @LoopCounter IF(@LoopCounter = 2) BREAK SET @LoopCounter = @LoopCounter + 1 END PRINT 'Statement after while loop'
If a CONTINUE statement is executed within a WHILE loop, then it skips executing the statements following it and transfers control to the beginning of while loop to start the execution of the next iteration.
Example: WHILE loop with CONTINUE statement
DECLARE @LoopCounter INT = 0 WHILE ( @LoopCounter <= 3) BEGIN SET @LoopCounter = @LoopCounter + 1 IF(@LoopCounter = 2) CONTINUE PRINT @LoopCounter END PRINT 'Statement after while loop'
DO…WHILE Loop in Sql Server
Sql Server doesn’t have a DO…WHILE loop construct, but same behaviour can be achieved using a WHILE loop as shown by the below example.
DECLARE @LoopCounter INT = 1 WHILE(1=1) BEGIN PRINT @LoopCounter SET @LoopCounter = @LoopCounter + 1 IF(@LoopCounter > 4) BREAK; END
Looping through table records one row at a time
Article Looping through table records in Sql Server lists out the various approaches of looping through table records row by row.
[ALSO READ] PRINT/SELECT Statement messages within WHILE LOOP or BATCH of statement is not displayed immediately after it’s execution- Sql Server
9 thoughts on “WHILE loop in Sql Server”
That was a nice explanation!Thanks
Thanks Clara… Appreciate your comments…
I want to know if it is possible to have a while loop in a select statement:
— temp table fill with data
Select tTwo.column1, tTwo.column2, tOne.column3,
— need to loop in temp table to get additional column values
From table t1 as tOne
Join t2 as tTwo on tTwo.Id = tOne.Id
where tTwo.column1 = ‘x’
will this work in the above statement
While condition >0
select columnTemp from temp
set condition = contition – 1
Thanks Good Explanation
Thanks Akshay… Appreciate your comments…
Please help me to convert vba to t-sql
Function ImpliedVolatility(CallOrPut, S, K, y, OptionValue)
Dim epsilon As Double, dVol As Double, vol_1 As Double
Dim i As Integer, maxIter As Integer, Value_1 As Double, vol_2 As Double
Dim Value_2 As Double, dx As Double, q As Double, r As Double, T As Double
q = 0
r = 0.1
T = y / 365
dVol = 0.00001
epsilon = 0.00001
maxIter = 100
vol_1 = 0.7
i = 1
Value_1 = EuropeanOption(CallOrPut, S, K, vol_1, y)
vol_2 = vol_1 – dVol
Value_2 = EuropeanOption(CallOrPut, S, K, vol_2, y)
dx = (Value_2 – Value_1) / dVol
If Abs(dx) < epsilon Or i = maxIter Then Exit Do
vol_1 = vol_1 – (OptionValue – Value_1) / dx
i = i + 1
ImpliedVolatility = vol_1