How should I delete branch from adjacency list in SQL Server?
No need for a procedure, just combine the CTE with a DELETE

with doomed as (
  select id, ParentNodeId
  from treenode
  where id = ... --&lt;&lt; start node of the branch to be deleted
  union all
  select c.id, c.ParentNodeId
  from treenode c
    join doomed p on c.parentnodeid = p.id
)
delete from treenode 
where id in (select id from doomed);