When I call this with these parameters(@MediaSellerID = 138, @StartDate = N'9/1/2020', @EndDate = N'10/7/2020') it returns data successfully.
But when I passed these parameters(@MediaSellerID = 213,@StartDate = '8/4/2020',@EndDate = N'11/25/2020')
this error occurs.
Msg 530, Level 16, State 1, Procedure GetAvailableDatesforCampaign, Line 81 [Batch Start Line 2] The statement terminated. The maximum recursion 100 has been exhausted before statement completion.
If anyone has this solution (OPTION(MAXRECURSION 0)) then please also mention this statement place. thanks
ALTER Procedure [dbo].[GetAvailableDatesforCampaign]
-- exec GetAvailableDatesforCampaign 2,getdate(),getdate()
@MediaSellerID int,
@StartDate datetime,
@EndDate datetime
AS
BEGIN
SET FMTONLY OFF
Declare @Frequency as varchar(200)
Declare @Weeks as varchar(200)
Declare @Days as varchar(200)
Declare @CopyDeadlineDays as int
Declare @DayItem as varchar(20)
Declare @WeekItem as varchar(200)
Declare @DayNumber as int
Delete from tblCampaignDates
select
@Frequency=MediaFrequency,
@Weeks=BroadcastDays,
@Days=BroadcastDaysWeeks,
@CopyDeadlineDays=Copydeadline
from
tblMediaSeller where MediaSeller_ID=@MediaSellerID
CREATE TABLE
#TempAvailableDates
(
AvailableDate datetime default GetDate()
)
CREATE TABLE
#TempAllDates
(
AllDate datetime,
DayNames varchar(20),
Weeknumber int
)
IF(@Frequency='Bi-Weekly' or @Frequency='Weekly' or @Frequency='Monthly')
BEGIN
DECLARE MY_CURSOR2 CURSOR
LOCAL STATIC READ_ONLY FORWARD_ONLY
FOR
SELECT DISTINCT
Item
FROM
dbo.SplitString(@Days)
OPTION (MAXRECURSION 0)
OPEN MY_CURSOR2
FETCH NEXT FROM MY_CURSOR2 INTO @DayItem
WHILE @@FETCH_STATUS = 0
BEGIN
-- Select All dates of those days in tblMediaSeller (Adv Publishing day) from start date to End dates
Set @DayNumber= CASE WHEN @DayItem='Monday' THEN 1
WHEN @DayItem='Tuesday' THEN 2
WHEN @DayItem='Wednesday' THEN 3
WHEN @DayItem='Thursday' THEN 4
WHEN @DayItem='Friday' THEN 5
WHEN @DayItem='Saturday' THEN 6
WHEN @DayItem='Sunday' THEN 7
ELSE 0 END
IF(@DayNumber<>0)
BEGIN
SET DATEFIRST @DayNumber; -- First day of the week is set to monday
WITH CTE(dt)
AS
(
SELECT @StartDate
UNION ALL
SELECT DATEADD(d, 1, dt)
FROM CTE
WHERE dt <@EndDate
)
INSERT INTO
#TempAllDates
SELECT
CONVERT(date,dt),
datename(dw, dt),
DATEDIFF(WEEK, DATEADD(MONTH, DATEDIFF(MONTH, 0, dt), 0), dt)
FROM
CTE
WHERE
datepart ("dw", dt) = 1;
END
FETCH NEXT FROM MY_CURSOR2 INTO @DayItem
END
CLOSE MY_CURSOR2
DEALLOCATE MY_CURSOR2
Set @WeekItem= REPLACE(@Weeks, 'Week', '');
DECLARE @ActualStartDate as Datetime
Set @ActualStartDate = (Select top 1 AllDate from #TempAllDates)
IF(@CopyDeadlineDays is null)
BEGIN
IF(@ActualStartDate>= DATEADD(day,5,getdate()))
BEGIN
Insert into
#TempAvailableDates
Select
AllDate
From
#TempAllDates
WHERE
Weeknumber in (select cast(item as int) from dbo.SplitString(@WeekItem))
END
ELSE
BEGIN
Insert into
#TempAvailableDates
Select
AllDate
From
#TempAllDates
WHERE
Weeknumber in (select cast(item as int) from dbo.SplitString(@WeekItem))
AND AllDate>=DATEADD(day,5,getdate())
END
END
ELSE
BEGIN
IF(@ActualStartDate>= DATEADD(day,@CopyDeadlineDays,getdate()))
BEGIN
Insert into
#TempAvailableDates
Select
AllDate
From
#TempAllDates
WHERE
Weeknumber in (select cast(item as int) from dbo.SplitString(@WeekItem))
END
ELSE
BEGIN
Insert into
#TempAvailableDates
Select
AllDate
From
#TempAllDates
WHERE
Weeknumber in (select cast(item as int) from dbo.SplitString(@WeekItem))
AND AllDate>=DATEADD(day,@CopyDeadlineDays,getdate())
END
END
END
ELSE
BEGIN
IF(@CopyDeadlineDays is null)
begin
IF(@StartDate>= DATEADD(day,5,getdate()))
BEGIN
Insert into
#TempAvailableDates
SELECT TOP
(DATEDIFF(DAY, @StartDate, @EndDate) + 1)
Date = DATEADD(DAY, ROW_NUMBER() OVER(ORDER BY a.object_id) - 1, @StartDate)
FROM
sys.all_objects a
CROSS JOIN
sys.all_objects b;
END
ELSE
BEGIN
SET @StartDate=@StartDate+5
Insert into
#TempAvailableDates
SELECT TOP
(DATEDIFF(DAY, @StartDate, @EndDate) + 1)
Date = DATEADD(DAY, ROW_NUMBER() OVER(ORDER BY a.object_id) - 1, @StartDate)
FROM
sys.all_objects a
CROSS JOIN
sys.all_objects b;
END
END
ELSE
BEGIN
IF(@StartDate>= DATEADD(day,@CopyDeadlineDays,getdate()))
BEGIN
Insert into
#TempAvailableDates
SELECT TOP
(DATEDIFF(DAY, @StartDate, @EndDate) + 1)
Date = DATEADD(DAY, ROW_NUMBER() OVER(ORDER BY a.object_id) - 1, @StartDate)
FROM
sys.all_objects a
CROSS JOIN
sys.all_objects b;
END
ELSE
BEGIN
SET @StartDate=@StartDate+@CopyDeadlineDays
Insert into
#TempAvailableDates
SELECT TOP
(DATEDIFF(DAY, @StartDate, @EndDate) + 1)
Date = DATEADD(DAY, ROW_NUMBER() OVER(ORDER BY a.object_id) - 1, @StartDate)
FROM
sys.all_objects a
CROSS JOIN
sys.all_objects b;
END
END
END
Insert into
tblCampaignDates
Select
AvailableDate
from #TempAvailableDates
Select AvailableDate from tblCampaignDates
END
This should work:
WITH CTE(dt) AS (
SELECT @StartDate
UNION ALL
SELECT DATEADD(d, 1, dt)
FROM CTE
WHERE dt <@EndDate
)
INSERT INTO #TempAllDates
SELECT CONVERT(date,dt), datename(dw, dt), DATEDIFF(WEEK, DATEADD(MONTH, DATEDIFF(MONTH, 0, dt), 0), dt)
FROM CTE
WHERE datepart(dw, dt) = 1
OPTION (MAXRECURSION 0);
Here is a db<>fiddle with a much simpler example.