maximum recursion error - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

maximum recursion error

Author

Topic

gangadhara.ms
Aged Yak Warrior

549 Posts

Posted - 2011-11-16 : 23:55:15

Hi Experts,

One of the stored procedure is failing with displaying below error.Till now it was runnign fine.

Pls suggest

The statement terminated. The maximum recursion 100 has been exhausted before statement completion. [SQLSTATE 42000] (Error 530).

--Ganga

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2011-11-17 : 05:18:12

What is the procedure? Do you have a heirarchy by any chance? It is hard to help without knowing the context of an error like this, especially as the quickest answer will limit your dataset.

You could try using MAXRECURSION, but I would be careful making sure the results are really what you are after.

gangadhara.ms
Aged Yak Warrior

549 Posts

Posted - 2011-11-22 : 03:31:56

this is the stored proc which is a culprit

CREATE PROCEDURE [dbo].[TotalChargeByPosition] (
@Position_ID int,
@FirstOfMonth datetime,
@AllBelow int = 1,
@Charge money OUTPUT
)

AS
BEGIN

WITH DirectReports(Employee_ID, Parent_ID, PositionName, Position_ID, Team_ID) AS
(
SELECT Employee_ID, Parent_ID, PositionName, ID Position_ID, Team_ID
FROM Position
WHERE ID = @Position_ID -- start from this position
UNION ALL
SELECT e.Employee_ID, e.Parent_ID, e.PositionName, e.ID Position_ID, e.Team_ID Team_ID
FROM Position e
INNER JOIN DirectReports d
ON e.Parent_ID = d.Position_ID
)
SELECT distinct DirectReports.Employee_ID, E.Name, Position_ID, DirectReports.PositionName
into #tmp1
FROM DirectReports
LEFT OUTER JOIN emp E on E.ID = DirectReports.Employee_ID
ORDER BY E.Name
--OPTION (MAXRECURSION 1000)

-- only own cost
IF @AllBelow = 0 BEGIN
delete #tmp1
where Position_ID <> @Position_ID
END

set @Charge = 0

if exists (select * from tr_Bill
where DatePeriodStart = @FirstOfMonth
and User_Id in (select Employee_ID from #tmp1)
) BEGIN

select @Charge = sum(Charge)
from tr_Bill
where DatePeriodStart = @FirstOfMonth
and User_Id in (select Employee_ID from #tmp1)
group by DatePeriodStart

END

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-22 : 03:45:05

how is data present in your table?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

gangadhara.ms
Aged Yak Warrior

549 Posts

Posted - 2011-11-22 : 04:49:37

The table data is around 25 lacks. but he result of CTE is 1980 records.

I have used max recursion with max value of 32200 but it didn't help me much.

Thanks,
Gangadhara MS
SQL Developer and DBA

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-22 : 05:11:22

quote:
Originally posted by gangadhara.ms

The table data is around 25 lacks. but he result of CTE is 1980 records.

I have used max recursion with max value of 32200 but it didn't help me much.

Thanks,
Gangadhara MS
SQL Developer and DBA

I dont think anybody can help you much without seeing some data. post some sample data and then we will try to help

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-11-22 : 07:42:32

You've probably got an invalid link in the hierarchy.
Try
select * from Position
where Parent_ID = ID

It might not be direct so if that doesn't find anything you'll have to look for circular links.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

gangadhara.ms
Aged Yak Warrior

549 Posts

Posted - 2011-11-23 : 04:50:56

This part of the query is having a problem now.

WITH DirectReports(Employee_ID, Parent_ID, PositionName, Position_ID, Team_ID) AS
(
SELECT Employee_ID, Parent_ID, PositionName, ID Position_ID, Team_ID
FROM Position
--WHERE ID = @Position_ID -- start from this position
UNION ALL
SELECT e.Employee_ID, e.Parent_ID, e.PositionName, e.ID Position_ID, e.Team_ID Team_ID
FROM Position e
INNER JOIN DirectReports d
ON e.Parent_ID = d.Position_ID
)
SELECT distinct DirectReports.Employee_ID, E.Name, Position_ID, DirectReports.PositionName
--into #tmp1
FROM DirectReports
LEFT OUTER JOIN emp E on E.ID = DirectReports.Employee_ID
ORDER BY E.Name

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2011-11-23 : 04:54:06

Yes, that was fairly obvious. Without data though, there is no way we can tell what is causing the issue as it will be data related, not code related.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources