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
Incorrect syntax near the keyword 'OPTION'. Expecting ')', UNION or EXCEPT
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.
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`
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
Can you provide some base data for the Jobs and patterns table - it's difficult without knowing what the data looks like.
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
15 People are following this question.
