Create date dimension in Azure synapse analytics

Question

I use 2 stored procedures to create a date_dimension in Synapse. First proc to create the details of a year and the second proc to iterate over it to create a range of years via a copy activity.

The second proc has 2 import parameters as YearStart=2000 and YearEnd=2030. However when I execute the copy activity it only creates the date table only for one year (2000) and it seems the loop doesn't work!

Here is the second proc.

CREATE OR ALTER PROCEDURE [PopulateDateDimensionForYearRange] @YearStart [int], @YearEnd [int] 
AS

BEGIN
    DECLARE @CurrentYear INT
    SET @CurrentYear = @YearStart
    WHILE @CurrentYear IS NOT NULL AND @YearStart <= @YearEnd AND @CurrentYear <= @YearEnd
    BEGIN
        EXEC PopulateDateDimensionForYear @Year = @CurrentYear;
        SET @CurrentYear  = @CurrentYear  + 1   
    END;
END;

As you can see the first proc name is PopulateDateDimensionForYear which I call it in the second proc and it works perfectly. Since the details of that only one year is correct.

CREATE OR ALTER PROCEDURE [PopulateDateDimensionForYear] @Year [int] AS

BEGIN
    IF OBJECT_ID('tempdb..#month', 'U') IS NOT NULL
        DROP TABLE #month
    CREATE TABLE #month (
        monthnum int,
        numofdays int
    )
    INSERT INTO #month
        SELECT 1, 31 UNION SELECT 2, CASE WHEN (@YEAR % 4 = 0 AND @YEAR % 100 <> 0) OR @YEAR % 400 = 0 THEN 29 ELSE 28 END UNION SELECT 3,31 UNION SELECT 4,30 UNION SELECT 5,31 UNION SELECT 6,30 UNION SELECT 7,31 UNION SELECT 8,31 UNION SELECT 9,30 UNION SELECT 10,31 UNION SELECT 11,30 UNION SELECT 12,31

    IF OBJECT_ID('tempdb..#days', 'U') IS NOT NULL
        DROP TABLE #days
    CREATE TABLE #days (days int)

    INSERT INTO #days
        SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 UNION SELECT 10 UNION SELECT 11 UNION SELECT 12 UNION SELECT 13 UNION SELECT 14 UNION SELECT 15 UNION SELECT 16 UNION SELECT 17 UNION SELECT 18 UNION SELECT 19 UNION SELECT 20    UNION SELECT 21 UNION SELECT 22 UNION SELECT 23 UNION SELECT 24 UNION SELECT 25 UNION SELECT 26 UNION SELECT 27 UNION SELECT 28 UNION SELECT 29 UNION SELECT 30 UNION SELECT 31

    SELECT
        CAST(CAST(monthnum AS VARCHAR(2)) + '/' + CAST([days] AS VARCHAR(3)) + '/' + CAST(@year AS CHAR(4)) AS DATE) AS [Date]
        ,DAY(CAST(CAST(monthnum AS VARCHAR(2)) + '/' + CAST([days] AS VARCHAR(3)) + '/' + CAST(@year AS CHAR(4)) AS DATE)) AS [Day]
        ,CAST(DATENAME(month, CAST(CAST(monthnum AS VARCHAR(2)) + '/' + CAST([days] AS VARCHAR(3)) + '/' + CAST(@year as char(4)) AS DATE)) AS nvarchar(10)) AS [MonthName]
        ,MONTH(CAST(CAST(monthnum as varchar(2)) + '/' + CAST([days] as varchar(3)) + '/' + CAST(@year as char(4)) AS DATE)) AS [MonthNumber]
        ,CAST(N'CY' + CAST(YEAR(CAST(CAST(monthnum as varchar(2)) + '/' + CAST([days] as varchar(3)) + '/' + CAST(@year as char(4)) AS DATE)) AS nvarchar(4)) + N'-' + SUBSTRING(DATENAME(month, CAST(CAST(monthnum as varchar(2)) + '/' + CAST([days] as varchar(3)) + '/' + CAST(@year as char(4)) AS DATE)), 1, 3) AS nvarchar(10)) AS [CalendarMonthLabel]
        ,YEAR(CAST(CAST(monthnum as varchar(2)) + '/' + CAST([days] as varchar(3)) + '/' + CAST(@year as char(4)) AS DATE)) AS [Year]
        , DATEPART(ISO_WEEK, CAST(CAST(monthnum as varchar(2)) + '/' + CAST([days] as varchar(3)) + '/' + CAST(@year as char(4)) AS DATE)) AS [ISOWeekNumber]
FROM #month m
    CROSS JOIN #days d
WHERE d.days <= m.numofdays

DROP table #month;
DROP table #days;
END;

Does someone know how to make it happen for all the years in the range?

Answer 1

At the end I came up with an idea from @wBob's answer. I put it below.

Basically what I needed was that to modify the second proc as follow:

CREATE OR ALTER PROCEDURE [PopulateDateDimensionForYearRange] @YearStart [int], @YearEnd [int] AS

BEGIN
    IF OBJECT_ID('tempdb..#date_dimension', 'U') IS NOT NULL
        DROP TABLE #date_dimension
    CREATE TABLE #date_dimension (
        Date Date,
        Day int,
        MonthName VARCHAR(30),
        MonthNumber int,
        CalendarMonthLabel VARCHAR(30),
        Year int,
        ISOWeekNumber int
    )

    DECLARE @CurrentYear INT
    SET @CurrentYear = @YearStart
    WHILE @CurrentYear IS NOT NULL AND @YearStart <= @YearEnd AND @CurrentYear <= @YearEnd
    BEGIN
        INSERT INTO #date_dimension EXEC PopulateDateDimensionForYear @Year = @CurrentYear;
        SET @CurrentYear  = @CurrentYear  + 1   
    END;
    SELECT * FROM #date_dimension 
    DROP TABLE #date_dimension
END;

The rest is completely the same as before, in my copy activity!

Answer 2

Your second proc returns multiple resultsets so will not work with the Copy activity. You have a few options: 1) change your second proc to SELECT INTO a temp table, and then INSERT into your main date dimension and call it with a Stored Proc activity:

    SELECT
        ...
INTO #tmp
FROM #month m
    CROSS JOIN #days d
WHERE d.days <= m.numofdays


INSERT yourDateDim
SELECT * FROM #tmp

OR change your process to use a For Each loop with the 'range' function to generate your years:

@range(2000,30)

Azure Synapse does not support INSERT ... EXEC but there is a workaround described here.