How to use WHILE Loop with BREAK and CONTINUE Keywords in SQL Server

WHILE Loop to Insert Dynamic Date Values into the Table in SQL Server

WHILE Loop statement is used to loop through the body to execute till the Condition evaluates to False.
Since the WHILE condition is evaluated before entering the loop, it is possible that the loop may not execute even once.

The following WHILE Loop example inserts the Dates, Month, Quarter, Year values till '12/31/2020' into the table.

USE TAMATAM

GO

CREATE TABLE Tbl_Dates ([Date_] [Date] NOT NULL PRIMARY KEY CLUSTERED,
      [Week_Number] [Int]  NULL,
      [Week_Day_Name] [Varchar](50) NULL,
      [Month_Number] [Varchar](50) NULL,
      [Month_Name] [Varchar](50) NULL,
      [Qtr_Name] [Varchar](50) NULL,
      [FY_Name] [Varchar](50) NULL,
      [Unique_Id] UniqueIdentifier);

-------------------------------------------------
WHILE Loop :
-------------------------------------------------

DECLARE @Val DATE
SET @Val=GETDATE()

PRINT(@Val)

Truncate Table Tbl_Dates

--WHILE Loop Begins here

WHILE @Val <= '12/31/2020'

BEGIN  
INSERT INTO Tbl_Dates ( [Date_], [Week_Number], [Week_Day_Name], [Month_Number],
       [Month_Name],[Qtr_Name],[FY_Name],[Unique_Id])
       VALUES ( @Val,Left(DateName(WW,@Val),3 ),Left(DateName(W,@Val),3 ),
    MONTH(@Val),Left(DateName(MM,@Val),3 ),'Q' + DateName(QQ,@Val),
    'FY' + DateName(YY,@Val),Newid())  
SELECT @Val=DATEADD(DD,1,@Val)
END

--WHILE Loop Ends here
GO

-------------------------------------------------
Output :

SELECT*FROM Tbl_Dates Order By 1 Desc

SELECT Distinct [FY_Name] FROM Tbl_Dates Order By 1 Desc

FY_Name
FY2017
FY2018
FY2019
FY2020

-------------------------------------------------

WHILE Loop with BREAK :
-------------------------------------------------

The BREAK keyword is used to Break the Loop when a specific Condition is True, explained below :

--WHILE Loop Begins here

WHILE @Val <= '12/31/2020'

BEGIN  
INSERT INTO Tbl_Dates ( [Date_], [Week_Number], [Week_Day_Name], [Month_Number],
       [Month_Name],[Qtr_Name],[FY_Name],[Unique_Id])
       VALUES ( @Val,Left(DateName(WW,@Val),3 ),Left(DateName(W,@Val),3 ),
       MONTH(@Val),Left(DateName(MM,@Val),3 ),'Q' + DateName(QQ,@Val),
       'FY' + DateName(YY,@Val),Newid())  
   
SELECT @Val=DATEADD(DD,1,@Val)

  IF YEAR(@Val)='2018'
   BREAK; ----WHILE Loop Exits/Stops here when Year=2018
END

--WHILE Loop Ends here

 SELECT Distinct [FY_Name] FROM Tbl_Dates Order By 1 
GO

-------------------------------------------------
Output :
SELECT Distinct [FY_Name] FROM Tbl_Dates Order By 1
FY_Name
FY2017
-------------------------------------------------

WHILE Loop with CONTINUE :
-------------------------------------------------

The CONTINUE keyword will stop the Loop to execute Next Lines of Code after it, and make the loop to go to beginning again.

--WHILE Loop Begins here

WHILE @Val <= '12/31/2020'

BEGIN  
INSERT INTO Tbl_Dates ( [Date_], [Week_Number], [Week_Day_Name], [Month_Number],
       [Month_Name],[Qtr_Name],[FY_Name],[Unique_Id])
       VALUES ( @Val,Left(DateName(WW,@Val),3 ),Left(DateName(W,@Val),3 ),
       MONTH(@Val),Left(DateName(MM,@Val),3 ),'Q' + DateName(QQ,@Val),
       'FY' + DateName(YY,@Val),Newid()) 

    SELECT @Val=DATEADD(DD,1,@Val)

CONTINUE --The Loop will go to beginning again, and the next lines(The IF condition section) of Code below in the Loop will not execute.

            IF YEAR(@Val)='2018' --This line will not execute
            BREAK; --This line will not execute
END

--WHILE Loop Ends here

 SELECT Distinct [FY_Name] FROM Tbl_Dates Order By 1 
GO
-------------------------------------------------

Output :
SELECT Distinct [FY_Name] FROM Tbl_Dates Order By 1 Desc
FY_Name
FY2017
FY2018

FY2019
FY2020

--------------------------------------------------------------------------------------------------------

Thanks, TAMATAM ; Business Intelligence & Analytics Professional

--------------------------------------------------------------------------------------------------------