If you have absolutely have to use a function (a limitation of your ETL tool as you imply), you can specify
OPTION as part of a multi-statement table-valued function, eg something like this:
CREATE FUNCTION dbo.udf_MyFunction ( @StartID INT )
RETURNS @tv TABLE
WITH Episodes( xlevel, PersonID, EventID, EpisodeID, StartDT, EndDT ) AS (
-- Anchor case - the first EventID for each person.
SELECT 1 AS xlevel, PersonID, EventID, @StartID, StartDT, EndDT
WHERE EventID = @StartID
SELECT xlevel + 1, et.PersonID, et.EventID, c.EventID + 1, et.StartDT, et.EndDT
FROM Episodes c
INNER JOIN dbo.EventTable et ON c.PersonID = et.PersonID
AND et.EventID = c.EventID + 1
--WHERE c.EventID <= (@StartID + 99)
INSERT INTO @tv
OPTION ( MAXRECURSION 1000 )
This also worked for me when wrapped in a view as you suggest your ETL tools does. There is no way to alter this system-wide, but as recursion can be inefficient, this is probably a good thing. You can't specify a query hint (using
OPTION) within the body of an inline table-valued function, as in your example.
Consider altering your process to walk the hierarchy only once when you receive your Episodes and store the output in a relational table. You could use a stored proc to do this so would not run into this limitation.
I also think there might be a bug in your code: if your CTE joins on personId and recurses on eventId, the eventId 101 would present twice I think, as a duplicate. Possibly I've misinterpreted your code, let me know what you think.