Inserting weekends into SQL Server table

Question

I'm trying to insert yearly weekend details such as date, dayName into a SQL Server table using the following stored procedure

alter procedure usp_AddOfficeHolidays
    @paramName NVARCHAR(max)
as
begin
    DECLARE @Year AS INT,
            @FirstDateOfYear DATETIME,
            @LastDateOfYear DATETIME

    -- You can change @year to any year you desire
    SELECT @year = 2016

    SELECT @FirstDateOfYear = DATEADD(yyyy, @Year - 1900, 0)
    SELECT @LastDateOfYear = DATEADD(yyyy, @Year - 1900 + 1, 0)

    -- Creating Query to Prepare Year Data
    --declare dayN varchar(max)
    if (select COUNT(*) from tblWeekSettings) < 1
    begin
        ;WITH cte AS 
        (
            SELECT 
                1 AS DayID,
                @FirstDateOfYear AS FromDate,
                DATENAME(dw, @FirstDateOfYear) AS Dayname
            UNION ALL
            SELECT 
                cte.DayID + 1 AS DayID,
                DATEADD(d, 1 ,cte.FromDate),
                DATENAME(dw, DATEADD(d, 1 ,cte.FromDate)) AS Dayname
            FROM cte
            WHERE DATEADD(d, 1, cte.FromDate) < @LastDateOfYear
        )
        SELECT FromDate AS Date, Dayname
        FROM CTE
        WHERE DayName IN (SELECT Param FROM dbo.fn_MVParam(@paramName,','))
        OPTION (MaxRecursion 370)
    end
    else
    begin
        Select 'Exists'
    end
end

and executing it using

exec usp_AddOfficeHolidays 'Saturday,Sunday'

which returns the following result

This works perfectly fine, BUT I have been unable to add/insert these details into the following table

I face the following error when I try to access the WEEKEND details by its alias CTE

The statement terminated. The maximum recursion 100 has been exhausted before statement completion

Although I've added the clause

 OPTION (MaxRecursion 370)

suggested by these links which I found on stack overflow

• The maximum recursion 100 has been exhausted before statement completion
• The statement terminated. The maximum recursion 100 has been exhausted before statement completion

EDIT

Basically i face the specified error when i try something like this

alter procedure usp_AddOfficeHolidays
@paramName NVARCHAR(max)
as
begin
----------------------------------------------------------
DECLARE @Year AS INT,
@FirstDateOfYear DATETIME,
@LastDateOfYear DATETIME
-- You can change @year to any year you desire
SELECT @year = 2016
SELECT @FirstDateOfYear = DATEADD(yyyy, @Year - 1900, 0)
SELECT @LastDateOfYear = DATEADD(yyyy, @Year - 1900 + 1, 0)
-- Creating Query to Prepare Year Data
--declare dayN varchar(max)
if (select COUNT(*) from tblWeekSettings) < 1

    begin
        ;WITH cte AS (
        SELECT 1 AS DayID,
        @FirstDateOfYear AS FromDate,
        DATENAME(dw, @FirstDateOfYear) AS Dayname
        UNION ALL
        SELECT cte.DayID + 1 AS DayID,
        DATEADD(d, 1 ,cte.FromDate),
        DATENAME(dw, DATEADD(d, 1 ,cte.FromDate)) AS Dayname
        FROM cte
        WHERE DATEADD(d,1,cte.FromDate) < @LastDateOfYear
        )



        SELECT FromDate AS Date, Dayname
        FROM CTE
        WHERE DayName IN(SELECT Param FROM dbo.fn_MVParam(@paramName,','))

        insert into tblWeekSettings(DayNo,WeekDayName,Dates)
        values('',Dayname,Date)

        OPTION (MaxRecursion 370)
    end

else
    begin
        Select 'Exists'
    end


--select cte
-----------------------------------------------------------
end

Any sort of help here would really be appreciated! I just need to insert the data in my the specified table!

Thank you!

Answer 1

That's the error:

    SELECT FromDate AS Date, Dayname
    FROM CTE
    WHERE DayName IN(SELECT Param FROM dbo.fn_MVParam(@paramName,','))

I split this code to make you understand what code actually is working in this case:

    insert into tblWeekSettings(DayNo,WeekDayName,Dates)
    values('',Dayname,Date)
    OPTION (MaxRecursion 370)

OPTION (MAX RECURSION) now belongs to single insert statement. Which is standalone, totally not related to CTE.

You actually need this, I suppose:

    ;with CTE (...)
    insert into tblWeekSettings(DayNo,WeekDayName,Dates)
    SELECT FromDate AS Date, Dayname
    FROM CTE
    WHERE DayName IN(SELECT Param FROM dbo.fn_MVParam(@paramName,','))
    OPTION (MaxRecursion 370)

but there are three columns in target table whilst your select has only two columns. So you'll have to update your select.

Some tips about INSERT-SELECT:
http://www.w3schools.com/sql/sql_insert_into_select.asp

this code:

    insert into tblWeekSettings(DayNo,WeekDayName,Dates)
    values('',Dayname,Date)

does not have any source for inserting. This is not valid code - you don't have here any Dayname,Date variables - they are not even referenced with @ as variables. It's totally not valid code.

Answer 2

For anyone who face the following issues

• Getting Weeked details i.e. DayName, Date
• Inserting into a Table

This stored procedure would do the trick.

alter procedure usp_AddOfficeHolidays
@paramName NVARCHAR(max)
as
begin
----------------------------------------------------------
DECLARE @Year AS INT,
@FirstDateOfYear DATETIME,
@LastDateOfYear DATETIME
-- You can change @year to any year you desire
SELECT @year = 2016
SELECT @FirstDateOfYear = DATEADD(yyyy, @Year - 1900, 0)
SELECT @LastDateOfYear = DATEADD(yyyy, @Year - 1900 + 1, 0)
-- Creating Query to Prepare Year Data
--declare dayN varchar(max)
if (select COUNT(*) from tblWeekSettings) < 1

    begin
        ;WITH cte AS (
        SELECT 1 AS DayID,
        @FirstDateOfYear AS FromDate,
        DATENAME(dw, @FirstDateOfYear) AS Dayname
        UNION ALL
        SELECT cte.DayID + 1 AS DayID,
        DATEADD(d, 1 ,cte.FromDate),
        DATENAME(dw, DATEADD(d, 1 ,cte.FromDate)) AS Dayname
        FROM cte
        WHERE DATEADD(d,1,cte.FromDate) < @LastDateOfYear
        )


        insert into tblWeekSettings(DayNo,Dates,WeekDayName) 
        SELECT '',FromDate AS Date, Dayname
        FROM CTE WHERE DayName IN(SELECT Param FROM dbo.fn_MVParam(@paramName,',')) 

        OPTION (MaxRecursion 30000) 




    end

else
    begin
        Select 'Exists'
    end


--select cte
-----------------------------------------------------------
end

Plus this article could really be helpful.

• http://www.w3schools.com/sql/sql_insert_into_select.asp

Answer 3

Try this, I hope this is useful for you.

alter procedure usp_AddOfficeHolidays
@paramName NVARCHAR(max)
as
begin
----------------------------------------------------------
DECLARE @Year AS INT,@DayNo as int=1,
@FirstDateOfYear DATETIME,
@LastDateOfYear DATETIME
-- You can change @year to any year you desire
SELECT @year = 2016
SELECT @FirstDateOfYear = DATEADD(yyyy, @Year - 1900, 0)
SELECT @LastDateOfYear = DATEADD(yyyy, @Year - 1900 + 1, 0)
Select getdate() DateOfYear into #tbl where 1=0
-- Creating Query to Prepare Year Data
--declare dayN varchar(max)
if (select COUNT(*) from tblWeekSettings) < 1

    begin
            while (@FirstDateOfYear< @LastDateOfYear)
            begin
                Insert Into #tbl (DayNo,DateOfYear) values (@DayNo,@FirstDateOfYear)
                set @FirstDateOfYear+=1
                set @DayNo+=1;
            End

            Insert Into tblWeekSettings (DayNo,WeekDayName,Dates)
            SELECT DayNo,DATENAME(dw, DateOfYear) Name,DateOfYear AS Date
            FROM #tbl
            WHERE DATENAME(dw, DateOfYear) IN(SELECT Param FROM dbo.fn_MVParam(@paramName,','))

    end

else
    begin
        Select 'Exists'
    end


--select cte
-----------------------------------------------------------
end