0

I use the code below to gather data to populate a tree control in my software. Each company can have an 'owned by' reference added which helps me link one to another. If someone selects the wrong 'owned by' company and chooses one already listed then I get an error with the following message The maximum recursion 100 has been exhausted before statement completion which is fine. I am trying to stop this message without having to save the offending record and run the code below, then reverse the save by removing the incorrect 'owned by' value if the error occurs. I would like to run the code below but add in the values being updated to it and then run it. Is that even possible? any other advice would be gratefully received.

    ;with PathUp as (
               select Company_ID, Owned_Company_ID as Parent_ID, 0 as Level
               from Company c 
               where c.Company_ID = 16336
               union all
               select c.Company_ID, c.Owned_Company_ID, PathUp.Level - 1 as Level
               from Company c join PathUp on c.Company_ID = PathUp.Parent_ID
               where c.Company_ID > 0 and c.Company_id <> c.Owned_Company_ID 
)

select * from PathUp

Simple data sample

Co    Owner
1     2
2     3
3     4
4     1

So record 4 cannot own record 1 as it would be a loop. I want to test row 4 before saving it to the record.

Derek Jee
  • 147
  • 1
  • 14
  • Why not just add the `MAXRECURSION` setting to your `OPTION` clause in the query? Or do you intentionally not want to return results when there are 100 (or more) possible iterations? – Thom A Aug 02 '21 at 12:46
  • Also, pet peeve, the semi colon (`;`) is a statement terminator; it goes at the *end* of **all** your statements, not the beginning of statements that require the **previous** statement to be properly terminated. – Thom A Aug 02 '21 at 12:47
  • Yes, I do not want to return the results are it will be an infinite loop in this scenario.. and yes the ; was from the previous statement which I copied out from in my code. – Derek Jee Aug 02 '21 at 12:50
  • "I would like to run the code below but add in the values being updated to it and then run it." <--- that sentence is not clear to me. It will be clearer if you give example data with the cycle and desired results for that data – Martin Smith Aug 02 '21 at 12:54
  • Perhaps the problem, therefore, is you have circular references, and thus need to either fix the data or the query to avoid said circular references. – Thom A Aug 02 '21 at 12:55
  • Hi I hope this is clearer.. Currently if I run that code I get no error. If I save changes to a record then run the code I get the error. I want to introduce the new values to the code without having to save the data to the record first. So as far as the data is concerned I will add a simple data table above shortly.. – Derek Jee Aug 02 '21 at 13:01
  • When you update the `Company` table are you doing this a row at a time? i.e. the "what if" is only applicable for a single row? Or are you updating multiple rows? – Martin Smith Aug 02 '21 at 13:11
  • Just a single row at a time. – Derek Jee Aug 02 '21 at 13:13
  • This, as I mentioned, looks like a problem with your data. If `1` cannot own `4`, why does your data say it does? Presumably the value of `Owner` for `Co` `4` should be `NULL` not `1`. – Thom A Aug 02 '21 at 13:25
  • Pardon the confusion.. I want to STOP row 4 from being saved. I want to test the values that row before saving it.. – Derek Jee Aug 02 '21 at 13:28

1 Answers1

0

You can throw an exception in your INSERT procedure if you find a circular reference, like this:

Assume you have @CompanyId and @OwnedCompanyID as input paramaters

declare @recursiveId int;

with PathUp as (
    select
        c.Company_ID, c.Owned_Company_ID
      from Company c 
      where c.Company_ID = @OwnedCompanyID
    union all
    select c.Company_ID, c.Owned_Company_ID
      from Company c
      join PathUp p on c.Company_ID = p.Owned_Company_ID
)
select top (1)
  @recursiveId = Company_ID
from PathUp
where Owned_Company_ID = @CompanyId;

if (@recursiveId is not null)
begin
    declare @message nvarchar(500) = CONCAT(N'Recursion found in ID ', @recursiveId);
    throw 50000, @message, 0;
end;
Charlieface
  • 52,284
  • 6
  • 19
  • 43
  • Just @Company_ID is a parameter.. but I see where you are going with this. – Derek Jee Aug 02 '21 at 15:45
  • Thank you but I still will have had to save the record before running this to check the circular ref.. And that is what I have decided to do anyways.. I will leave open in case someone has any ideas as I would like to know if it can be done some how. – Derek Jee Aug 02 '21 at 16:38
  • No, you do not. The point of this is to place it before the `insert` as a check. You just run it using the parameters – Charlieface Aug 02 '21 at 17:47
  • Oh, I need to read better :), thank you I just ran your code and it works exactly as I need for a number of records linked by OwnerCompanyID and CompanyID where a circular loop is made. When running the same code when linking one record to another where neither of them are linked to any other record I get the max recursion error. To clarify on my simple table above I am linking row 1 to row 2 with no other links. Thank you for your help.. very much appreciated. – Derek Jee Aug 03 '21 at 07:35
  • Of course if you are expecting this I can use this to process the save . – Derek Jee Aug 03 '21 at 07:47
  • No that should nor happen. Are there more than 100 links in the chain? Are you running this code before the insert or after? Is there already a loop before you insert? – Charlieface Aug 03 '21 at 11:36
  • No, I have set up a 2 record scenario which link to nothing. I try to link one to the other (record 1 to owner company id record 2) but test before save and I get the max recursion error but save it, I then link them the other way round (record 2 to owner company id 1) and your error 50000 is triggered as expected. – Derek Jee Aug 03 '21 at 12:26