While loop function union function end

I want to loop the code shown below. I would like to use the [getpublicholidays] function for a variable number of years and merge them (union). Unfortunately I don’t know how to use the function in a loop with union. Can someone help?

select  * from [getpublicholidays](2019)
UNION
select  * from [getpublicholidays](2020)
UNION
select  * from [getpublicholidays](2021)
UNION
select  * from [getpublicholidays](2022)

Answer

If you are looping for X years, each time you loop to a new year, you won’t also need to union for other years. Rather, you need somewhere to store the results from one year to the next. Perhaps you are looking for something like this, where I am just making up columns for your function’s output since I have no idea what they really are…adjust accordingly…

--Create a temp table to match the results of the output of the function. 
IF OBJECT_ID('tempdb.dbo.#publicHolidays') IS NOT NULL
    DROP TABLE #publicHolidays

    CREATE TABLE #publicHolidays(Holiday VARCHAR(28), HolidayYear INT, HolidayDate DATETIME)

--Set a variable for how many years back to include
DECLARE @NumberOfYears INT = 4

--Loop through each year
WHILE @NumberOfYears > 0
BEGIN
    
    DECLARE @Year INT =  (SELECT YEAR(GetDate()) - @NumberOfYears +1) --Remove +1 to not include current year
    
    INSERT INTO #publicHolidays (Holiday, HolidayYear, HolidayDate)
    SELECT * FROM getpublicholidays(@Year)   

    SET @NumberOfYears -= 1
END

--Table now has results from each of the 4 years
SELECT * FROM #publicHolidays

IF OBJECT_ID('tempdb.dbo.#publicHolidays') IS NOT NULL
    DROP TABLE #publicHolidays

If that’s not getting what you need, then we indeed need more details about what you are after.