The maximum recursion 100 has been exhausted before statement completion.
ALTER procedure [dbo].[sp_AgentComsn] -- sp_AgentComsn 2
@Agent_Id int
as
begin
with tbparent as
(
select Agent_Id,Agent_ManagerId from Agent where Agent_Id=@Agent_Id
union all
select Agent.Agent_Id,Agent.Agent_ManagerId from Agent join tbparent on Agent.Agent_Id=tbparent.Agent_ManagerId
)
select Agent_Id from tbparent
end
Please Help me to resolve this one
Nitin SontakkePosted Jul 22, 2016, 4:44 AM
You are using CTE to make recursive calls.
Your datais such that it has cyclical relationship. For example, employee1 has manager set as employee 2 and employee 2 has manager set as employee 1. This kind of data will make recursion keep on running ininfinite loop.