WHILE loop in Sql Server

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:

WHILE LOOP

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.
Syntax:
WHILE Condition
BEGIN
Sql Statements
END

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	

RESULT:
1
2
3
4

BREAK Statement

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'

RESULT:
WHILE Loop Break Statement Sql Server

CONTINUE Statement

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'

RESULT:
WHILE Loop Continue Statement Sql Server

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

RESULT:
DO WHILE Loop in Sql Server

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

8 thoughts on “WHILE loop in Sql Server

  1. Hello,

    I want to know if it is possible to have a while loop in a select statement:
    E.g

    — 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
    begin
    select columnTemp from temp
    set condition = contition – 1
    End

  2. 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
    Do
    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
    Loop
    ImpliedVolatility = vol_1
    End Function

Leave a Reply

Your email address will not be published. Required fields are marked *