Stored procedure to add 30 days using DATEDIFF within while loop condition in Date Dimension table

Question

I want to add 30 consecutive days of data in my Date Dimension table using DATEDIFF() but I am getting blank result. Can you please help me correct the code below to get the desired result?

CREATE TABLE dbo.dateDimension (
    DateKey INT NOT NULL
    ,DateValue DATE NOT NULL
    ,CYear SMALLINT NOT NULL
    ,CMonth TINYINT NOT NULL
    ,CONSTRAINT PK_DimDate PRIMARY KEY ( DateKey )
);
GO

CREATE PROC dbo.dateTest 
@StartDate DATETIME
AS
WHILE (DATEDIFF(day, @StartDate, GETDATE()) <=30)
BEGIN
    INSERT into dbo.dateDimension
    SELECT CAST( YEAR(@StartDate) * 10000 + MONTH(@StartDate) * 100 + DAY(@StartDate) AS INT)
    ,@StartDate
    ,YEAR(@StartDate)
    ,MONTH(@StartDate)
    SET @StartDate = DATEADD(d,1,@StartDate)
END;
GO

EXECUTE dbo.dateTest '2010-01-01'
SELECT * FROM dbo.dateDimension

Answer 1

The issue is that this logic:

DATEDIFF(day, @StartDate, GETDATE())

gives 3739 days with your current start date, so its never less than 30. Personally I would simply count it as follows:

DECLARE @StartDate DATETIME = '2010-01-01', @Count INT = 0;

WHILE @Count <= 30 BEGIN
    INSERT into dbo.dateDimension
    SELECT CAST( YEAR(@StartDate) * 10000 + MONTH(@StartDate) * 100 + DAY(@StartDate) AS INT)
        , @StartDate
        , YEAR(@StartDate)
        , MONTH(@StartDate);
    SET @StartDate = DATEADD(d,1,@StartDate);
    set @Count = @Count + 1;
END;

SELECT *
FROM dbo.dateDimension;