While loop function union function end-CodePudding

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)

CodePudding user response：

You don't need loops, recursions or temp tables.

Just use a virtual table to do this, and CROSS APPLY your function

SELECT *
FROM (VALUES
  (2019),
  (2020),
  (2021),
  (2022)
) AS Years(Year)
CROSS APPLY getpublicholidays(Years.Year);

If you want a variable number of rows, you can use a virtual table of numbers

WITH L0 AS (
    SELECT n
    FROM (VALUES (1),(1),(1),(1),(1),(1),(1),(1) ) AS v(n)
),
L1 AS (
    SELECT n = 1
    FROM L0 AS a, L0 AS b
)
SELECT *
FROM (
    SELECT TOP (@endYear - @startYear   1)
      @startYear - 1   ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
    FROM L0
) AS Years(Year)
CROSS APPLY getpublicholidays(Years.Year);

Given that years are generally only a small number of rows, you could just hard-code a fixed number, and join that

SELECT *
FROM (VALUES
    (2010),(2011),(2012),(2013),(2014),(2015),(2016),(2017),(2018),(2019),
    (2020),(2021),(2022),(2023),(2024),(2025),(2026),(2027),(2028),(2029),
    (2030),(2031),(2032),(2033),(2034),(2035),(2036),(2037),(2038),(2039)
) AS Years(Year)
CROSS APPLY getpublicholidays(Years.Year);

CodePudding user response：

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.