question

SimonThompson avatar image
SimonThompson asked

T-SQL MAXRECURSION with INSERT Statement Error

Thank you for looking at this. I have two CTE tables that generate duty times, the calculations are convoluted so wont bother you with them. Here is the SQL (Simplified) DECLARE @StartDate Datetime -- Start Date for the Duties to be generated DECLARE @PatternStartPoint int --Start Point in Pattern eg 3 - DECLARE @PatternSize int --The length of the shift pattern DECLARE @User int SET @User = 1111; SET @PatternSize = 3; SET @DaysRequired=16; --Testing assume 16 SET @StartDate = DATEADD(dd, 0, DATEDIFF(dd, 0, GETDATE())); --for testing use today SET @PatternStartPoint =3; --Get Job Duty Pattern WITH Pattern (P_DayNumber, P_DayType, P_Start, P_End) AS ( Select D_DayNumber,D_Type,D_Start,D_End from dbo.Tbl_Jobs as J join dbo.Tbl_Patterns as P on J.J_PatternID = P.Pattern_ID join dbo.Tbl_PatternDetails as D on P.Pattern_ID=d.D_Pattern_ID ) , --Get DateList Datelist (MyDate, DayNumber) AS ( SELECT @StartDate AS MyDate, @PatternStartPoint as DayNumber UNION ALL Select MyDate + 1, (DayNumber+1) - (CAST((Daynumber+1)/(@PatternSize+.01) as int)*@PatternSize) --% (@PatternSize) FROM Datelist WHERE MyDate < (@Startdate + @DaysRequired)-1 ) INSERT INTO [IDAHO].[dbo].[Appointments] ([Subject] ,[Description] ,[Start] ,[End] ,[RoomID] ,[UserID] ) ( SELECT 'Standard Work Pattern' ,'IDAHO Generated on ' + CONVERT(nvarchar(20),getdate()) ,mydate + p.P_Start ,mydate + p.P_End ,1 ,@User as A_User FROM Datelist as d join Pattern as p on p.P_DayNumber=d.DayNumber Where mydate + p.P_Start is not null OPTION (MAXRECURSION 0) ) I am getting an error with the MAXRECURSION line, if I remove the line and try to generate more than 100 'Duties' it errors (being the default value). If I Change the last statement to a simple select it seems to work??? as shown below DECLARE @StartDate Datetime -- Start Date for the Duties to be generated DECLARE @PatternStartPoint int --Start Point in Pattern eg 3 - DECLARE @PatternSize int --The length of the shift pattern DECLARE @DaysRequired int --The length of the shift pattern DECLARE @User int SET @User=1111; SET @PatternSize = 3; SET @DaysRequired=160; --Testing assume 16 SET @StartDate = DATEADD(dd, 0, DATEDIFF(dd, 0, GETDATE())); --for testing use today SET @PatternStartPoint =3; --Get Job Duty Pattern WITH Pattern (P_DayNumber, P_DayType, P_Start, P_End) AS ( Select D_DayNumber,D_Type,D_Start,D_End from dbo.Tbl_Jobs as J join dbo.Tbl_Patterns as P on J.J_PatternID = P.Pattern_ID join dbo.Tbl_PatternDetails as D on P.Pattern_ID=d.D_Pattern_ID ) , --Get DateList Datelist (MyDate, DayNumber) AS ( SELECT @StartDate AS MyDate, @PatternStartPoint as DayNumber UNION ALL Select MyDate + 1, (DayNumber+1) - (CAST((Daynumber+1)/(@PatternSize+.01) as int)*@PatternSize) --% (@PatternSize) FROM Datelist WHERE MyDate < (@Startdate + @DaysRequired)-1 ) SELECT 'Standard Work Pattern' ,'IDAHO Generated on ' + CONVERT(nvarchar(20),getdate()) ,mydate + p.P_Start ,mydate + p.P_End ,1 ,@User as A_User FROM Datelist as d join Pattern as p on p.P_DayNumber=d.DayNumber Where mydate + p.P_Start is not null OPTION (MAXRECURSION 0) Have I put the command in the wrong place? Why does it work with select but not insert? Thank you
sqlinsertcterecursion
3 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

what is the error message?
0 Likes 0 ·
Incorrect syntax near the keyword 'OPTION'. Expecting ')', UNION or EXCEPT
0 Likes 0 ·
This site runs off of votes. Please mark all helpful answers by clicking on the thumbs up next to those answers. If any one answer lead to a solution, please indicate that by clicking on the check mark next to that answer.
0 Likes 0 ·
Kev Riley avatar image
Kev Riley answered
Looks like the error comes from the fact you have 2 CTEs in the final select, and are trying to limit the recursion, but one of the CTEs isn't recursive - maybe the query hint is then trying to be applied to the insert? I'd suggest either re-writing the query to not need the recursion limit lifted, or separately process the recursive data into a temporary structure, and then join onto that - you don't need to use a CTE for `Pattern`
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

SimonThompson avatar image
SimonThompson answered
I have tried but not sure how to generate a table of dates, similar to the Datelist CTE, I get the error 'Invalid Object Datelist' with this code INSERT INTO [IDAHO].[dbo].[Appointments] ([Subject] ,[Description] ,[Start] ,[End] ,[RoomID] ,[UserID] ) select * from ( SELECT 'Standard Work Pattern' [Subject] ,'IDAHO Generated on ' + CONVERT(nvarchar(20),getdate()) [Description] ,mydate + p.P_Start [Start] ,mydate + p.P_End [End] ,1 [RoomID] ,@User [UserID] FROM ( SELECT @StartDate AS MyDate, @PatternStartPoint as DayNumber UNION ALL Select MyDate + 1, (DayNumber+1) - (CAST((Daynumber+1)/(@PatternSize+.01) as int)*@PatternSize) --% (@PatternSize) FROM Datelist WHERE MyDate < (@Startdate + @DaysRequired)-1 ) as d join ( Select D_DayNumber,D_Type,D_Start,D_End from dbo.Tbl_Jobs as J join dbo.Tbl_Patterns as P on J.J_PatternID = P.Pattern_ID join dbo.Tbl_PatternDetails as D on P.Pattern_ID=d.D_Pattern_ID ) as p on p.P_DayNumber=d.DayNumber Where mydate + p.P_Start is not null ) x; Could you help me with an alternative to this piece of code SELECT @StartDate AS MyDate, @PatternStartPoint as DayNumber UNION ALL Select MyDate + 1, (DayNumber+1) - (CAST((Daynumber+1)/(@PatternSize+.01) as int)*@PatternSize) FROM Datelist WHERE MyDate < (@Startdate + @DaysRequired)-1
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Can you provide some base data for the Jobs and patterns table - it's difficult without knowing what the data looks like.
0 Likes 0 ·
SimonThompson avatar image
SimonThompson answered
I have solved this by not using a CTE for the Datelist, I used a temp table and now all is good. Thank you for your time and effort
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.