Stored procedure to accept starting date and number of consecutive dates beginning from start date

Question

The parameter should consider, A starting date and the number of the consecutive dates beginning with the starting date.

The stored procedure then should populate all columns of the DateRange table according to the two provided parameters.

I created a table :

CREATE TABLE DateRange
(
    DateID INT IDENTITY,
    DateValue DATE,
    Year INT,
    Quarter INT,
    Month INT,
    DayOfWeek INT
);

Stored procedure code:

CREATE FUNCTION dbo.DateRange_sp4
    (@StartDate DATE,
     @NumberofConsecutivedays INT)
RETURNS @DateList TABLE 
                  (
                       DateID INT, 
                       DateValue DATE,
                       Year INT, 
                       Quarter INT, 
                       Month INT, 
                       DayOfWeek INT
                  )
AS 
BEGIN
    DECLARE @Counter INT = 0;

    WHILE (@Counter < @NumberofConsecutivedays)
    BEGIN
        INSERT INTO @DateList
        VALUES (@Counter + 1, 
                DATEADD(DAY, @Counter, @StartDate), 
                DATEPART(YEAR, @StartDate), 
                DATEPART(QUARTER, @StartDate),
                DATEPART(MONTH, @StartDate), 
                DatePart(WEEKDAY, @StartDate) );

        SET @Counter += 1
    END

    RETURN;
END
GO

SELECT * 
FROM dbo.DateRange_sp4('2018-07-13', 20);

My output returns the same result for year, quarter, month and dayofweek. How to split the date in different columns? Or is there any other way to do it?

Answer 1

Use a tally table... it'll be A LOT faster. Check it out for 10K days... and run your loop code for 10K days.

declare @dateparameter date = '1900-04-12'
declare @numOfDays int = 10000

;WITH
    E1(N) AS (select 1 from (values (1),(1),(1),(1),(1),(1),(1),(1),(1),(1))dt(n)),
    E2(N) AS (SELECT 1 FROM E1 a, E1 b), --10E+2 or 100 rows
    E4(N) AS (SELECT 1 FROM E2 a, E2 b), --10E+4 or 10,000 rows max
    cteTally(N) AS 
    (
        SELECT  ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E4
    )

select 
    datevalue = @dateparameter
    ,year = datepart(year,@dateparameter)
    ,quarter = datepart(quarter,@dateparameter)
    ,month = datepart(month,@dateparameter)
    ,dayofweek = datepart(weekday,@dateparameter)
union all
select 
    datevalue = dateadd(day,N,@dateparameter)
    ,year = datepart(year,dateadd(day,N,@dateparameter))
    ,quarter = datepart(quarter,dateadd(day,N,@dateparameter))
    ,month = datepart(month,dateadd(day,N,@dateparameter))
    ,dayofweek = datepart(weekday,dateadd(day,N,@dateparameter))
from cteTally
where N <= @numOfDays

But, if you are going to reference this a lot, why not make a persisted table? Aaron Bertran has a great article on this: https://www.mssqltips.com/sqlservertip/4054/creating-a-date-dimension-or-calendar-table-in-sql-server/