Sunday, October 15, 2017

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
--------------------------------------------------------------------------------------------------------

No comments:

Post a Comment

Hi User, Thank You for visiting My Blog. Please post your genuine Feedback or comments only related to this Blog Posts. Please do not post any Spam comments or Advertising kind of comments which will be Ignored.

Featured Post from this Blog

How to compare Current Snapshot Data with Previous Snapshot in Power BI

How to Dynamically compare two Snapshots Data in Power BI Scenario: Suppose, we have a sample Sales data, which is stored with Monthly Snaps...

Popular Posts from this Blog