nested loop for a stored procedure in sql not working-CodePudding

Hi I am trying to loop a stored procedure that has a month and year inputs.

I am trying to loop it 24 months back from the current year, but the problem is I am only getting just the months and years for the current year, it does not give me the full 24 months

this is the code that I have

CREATE OR ALTER PROCEDURE GetCallReport
    @YearParam nvarchar(4)
    @MonthParam nvarchar(2)
AS
--BEGIN
SET NOCOUNT ON
    DECLARE @CurrentYear INT
    DECLARE @CurrentMonth INT
    SET @CurrentYear = CAST(@YearParam AS INT)
    SET @CurrentMonth = 1

    WHILE (@CurrentYear >= (CAST(@YearParam AS INT) -1))
    BEGIN

            WHILE (@CurrentMonth <= 12)
            BEGIN

                EXEC GetPrincipalReport @MonthParam = @CurrentMonth ,@YearParam = @CurrentYear

                Set @CurrentMonth = @CurrentMonth   1
            END 
            Set @CurrentYear = @CurrentYear - 1
    END 

--END

--EXEC PROCEDURE
EXEC GetCallReport @YearParam = '2021'

with this I only get the 12 months of 2021 but not the 12 of 2020.

Thanks for the help

CodePudding user response：

Everything is good in your code except a slight issue.

You are not resetting the value of @CurrentMonth back to 1 so that inner loop can run again (12 times)

    .............
    Set @CurrentMonth = @CurrentMonth   1
            END 
            Set @CurrentYear = @CurrentYear - 1
            Set @CurrentMonth = 1 //resetting back to first month

CodePudding user response：

The issue is the current_month, does not get reset after the first current_year loop runs.

A much cleaner way would be to use date arithmetic in my opinion.

ALTER PROCEDURE dbo.GetCallReport
    @YearParam nvarchar(4)
    ,@MonthParam nvarchar(2)
AS
--BEGIN
    SET NOCOUNT ON
    DECLARE @i int
    DECLARE @report_date date
    SET @i = 1
    SET @report_date = CAST(CONCAT(@YearParam,'-',@MonthParam,'-01') as DATE)
    
    WHILE (@i<=24)
    BEGIN       
        EXEC GetPrincipalReport @MonthParam = DATEPART(MONTH,DATEADD(MONTH,-@i,@report_date))
                               ,@YearParam = DATEPART(MONTH,DATEADD(MONTH,-@i,@report_date))
        SET @i = @i 1
        
    END