辅助表实现时间维度表存储过程

辅助表创建代码如下：

CREATE FUNCTION [dbo].[GetNums](@low AS BIGINT,@high AS BIGINT) RETURNS TABLE
AS
RETURN
	WITH
		L0 AS (SELECT c FROM(VALUES(1),(1)) AS D(c)),
		L1 AS (SELECT 1 AS c FROM L0 AS A CROSS JOIN L0 AS B),
		L2 AS (SELECT 1 AS c FROM L1 AS A CROSS JOIN L1 AS B),
		L3 AS (SELECT 1 AS c FROM L2 AS A CROSS JOIN L2 AS B),
		L4 AS (SELECT 1 AS c FROM L3 AS A CROSS JOIN L3 AS B),
		L5 AS (SELECT 1 AS c FROM L4 AS A CROSS JOIN L4 AS B),
		Nums AS (SELECT ROW_NUMBER() OVER(ORDER BY(SELECT NULL)) AS rownum FROM L5)
	SELECT @low + Nums.rownum-1 AS n
	FROM Nums
	ORDER BY Nums.rownum
	OFFSET 0 ROWS FETCH FIRST @high-@low + 1 ROWS ONLY;
GO

存储过程代码如下：

USE [Test]
GO
/****** Object:  StoredProcedure [dbo].[Usp_Aux_Dim_Date]    Script Date: 2022/10/12 11:53:31 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROC [dbo].[Usp_Aux_Dim_Date]
    @StartDate DATE,
    @EndDate DATE
AS
BEGIN
    SET NOCOUNT ON;
    BEGIN TRY
		IF (@StartDate is null)
		BEGIN
			SET @StartDate = DATEFROMPARTS ( year(getdate()), 1, 1 )
		END

		IF (@EndDate IS NULL)
		BEGIN
			SET @EndDate = DATEFROMPARTS ( year(getdate()), 12, 31 )
		END

		INSERT INTO dbo.Auxiliary_Dim_Date
		(
		    日期,
		    DateKey,
		    年份名称,
		    年份序号,
		    季度名称,
		    季度序号,
		    年季度名称,
		    年季度序号,
		    月份名称,
		    月份序号,
		    年月名称,
		    年月序号,
		    InsertDatetime,
		    UpdateDatetime
		)
		SELECT
			 DATEADD(DAY,n,@StartDate),
			 CONVERT(VARCHAR(100), DATEADD(DAY,n,@StartDate), 112),
			 'FY' + RIGHT(CONVERT(VARCHAR(100), DATEPART(yyyy, DATEADD(DAY,n,@StartDate))), 2),
			 DATEPART(yyyy, DATEADD(DAY,n,@StartDate)),
			 'Q' + CONVERT(VARCHAR(100), DATEPART(q, DATEADD(DAY,n,@StartDate))),
			 DATEPART(q, DATEADD(DAY,n,@StartDate)),
			 'Y' + CONVERT(VARCHAR(100), DATEPART(yyyy, DATEADD(DAY,n,@StartDate))) + 'Q' + CONVERT(VARCHAR(100), DATEPART(q, DATEADD(DAY,n,@StartDate))),
			 CONVERT(VARCHAR(100), DATEPART(yyyy, DATEADD(DAY,n,@StartDate))) + '0'+ CONVERT(VARCHAR(100), DATEPART(qq, DATEADD(DAY,n,@StartDate))),
			 SUBSTRING(DATENAME(MONTH, DATEADD(DAY,n,@StartDate)), 1, 3), DATEPART(m, DATEADD(DAY,n,@StartDate)),
			 'Y' + CONVERT(VARCHAR(100), DATEPART(yyyy, DATEADD(DAY,n,@StartDate))) + 'M'+ CONVERT(VARCHAR(100), DATEPART(m, DATEADD(DAY,n,@StartDate))),
			 CONVERT(VARCHAR(100), DATEPART(yyyy, DATEADD(DAY,n,@StartDate))) + CONVERT(VARCHAR(100), DATENAME(mm, DATEADD(DAY,n,@StartDate))),
			 GETDATE(),
			 GETDATE()

		FROM dbo.GetNums(0,DATEDIFF(DAY,@StartDate,@EndDate))
		WHERE
			NOT EXISTS (SELECT * FROM dbo.Auxiliary_Dim_Date  WHERE 日期 = DATEADD(DAY,n,@StartDate))
		
    END TRY
    BEGIN CATCH
        IF @@TRANCOUNT > 0
        BEGIN
            ROLLBACK TRANSACTION;
        END;
    END CATCH;
END;

效果如下：