Can I do this calculation on SQL server and just return the value? Yes, do the recursion from the leaf node, do the calculation as you go and get the max value in the main query. with C as ( select T.Id, T.Parent, cast(T.multiplier * @x + T.Const as...
sql-server,sql-server-2005,sql-cte
Like my comment, your columns were incorrect in join. Use getallparent p on p.ParentID = c.ChildID. with getallparent as ( select ChildID,ParentID from ExampleTable where ChildID = 5 union all select C.ChildID,C.ParentID from ExampleTable c inner join getallparent p on p.ParentID = c.ChildID ) select * from getallparent; Fiddle...