Recursion Error – Nesting Level exceed (limit 32) | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Recursion Error – Nesting Level exceed (limit 32)

CREATE FUNCTION [dbo].[FindParent] (@no varchar(25))
RETURNS varchar(25)
AS
BEGIN DECLARE @Parent varchar(25) SELECT @Parent = CONTAINER.PREVLOT
FROM Container INNER JOIN ContainerLevel ON Container.LevelId = ContainerLevel.ContainerLevelId
WHERE [email protected] if @Parent is not null
Begin
set @Parent = dbo.FindParent(@Parent)
Return @Parent
end
else
set @[email protected] Return @Parent END I am using the above function in select statement to find the parent of the specified container. We will get the parent name when @Parent value is null. I am getting the following error:
Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32). So I need to rewrite the whole function without using recursion since the limit of SQL Server is exceeded. Any suggestions?
No need to call the function recursively, just use a WHILE loop on the inside. Use the @Parent instead of the initial @no value on the repeating query, and before repeating you copy the @Parent into @HoldParent, which is the value you will return: CREATE FUNCTION [dbo].[FindParent] (@no varchar(25))
RETURNS varchar(25)
AS
BEGIN DECLARE @Parent varchar(25), @HoldParent varchar(25) SELECT @Parent = CONTAINER.PREVLOT
FROM Container INNER JOIN ContainerLevel
ON Container.LevelId = ContainerLevel.ContainerLevelId
WHERE [email protected] WHILE @Parent IS NOT NULL
BEGIN
SET @HoldParent = @Parent SELECT @Parent = CONTAINER.PREVLOT
FROM Container INNER JOIN ContainerLevel
ON Container.LevelId = ContainerLevel.ContainerLevelId
WHERE CONTAINER.CONTAINERNAME = @Parent
END RETURN @HoldParent END

]]>