SQL error Maximum stored procedure, function, trigger, or view nesting level exceeded

Question

I have an error

Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32).

when I call

select dbo.CheckProjectValidity(11, 9)

with this SQL function.

ALTER function [dbo].[CheckProjectValidity](@IdParent int, @IdStructure int)
RETURNS bit
AS
BEGIN
declare @Result bit;
if @IdParent=0
    set @Result = 0
    else
    if @IdParent=@IdStructure
        set @Result = 1
    else
        begin
            set @IdParent = (select st.IdParent from Structure st where st.IdParent=@IdParent);
            set @Result = dbo.CheckProjectValidity(@IdParent, @IdStructure)
        end
return @Result
END

The idea that I want to get the list of the Project from the tree.

Answer 1

Your procedure is recursing infinitely because of this statement:

set @IdParent = (select st.IdParent from Structure st where st.IdParent=@IdParent)

which is effectively setting @IdParent to its existing value. You probably wanted to use something like:

set @IdParent = (select st.IdParent from Structure st where st.Id=@IdParent)

(I'm guessing that IdParent points to the Id value in another row)