How to fix nesting level exceeded Error with recursive function in SQL

Question

Possible duplicate of this but didn't get any solution there. I have following functions which takes float as input and converts the number into days with some custom logic.

CREATE FUNCTION [dbo].[F_GetDurationInDays_BI] 
(
    @TimeInMinutes FLOAT
)
RETURNS FLOAT
AS
Begin
        If (@TimeInMinutes >= 0 and @TimeInMinutes < 480)
            return (@TimeInMinutes/60)/8                       
        Else If (@TimeInMinutes >= 480 and @TimeInMinutes < 1440)
            return 1
        Else If (@TimeInMinutes >= 1440 and @TimeInMinutes < 1920)
            return 1.5
        Else If (@TimeInMinutes = 1920)
            return 2
        Else If (@TimeInMinutes > 1920)
            return ( select [dbo].[F_GetDurationInDays_BI] (@TimeInMinutes - 1440) +1)

        return 0
End

When I am giving some higher values like,

select Format(dbo.F_GetDurationInHours_BI (226560), 'N1')

I'm getting nesting level exceed exception as below,

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

Is there any way to increase nesting level limit or any other possible way. Appreciate the help.

I'm not sure what your logic is, but it seems clear that you can convert this from a recursive function to a `while` loop. — Gordon Linoff, Oct 10 '18 at 11:53
Instead of subtraction, use division. If you _really_ insist on using subtraction, use a while loop instead of recursion. It will work because it doesn't use recursion, but it will be slow for big numbers. — , Oct 10 '18 at 12:14
While loop could cause performance issue and duplicate code as well. Is there any way to increase nesting level limit? — Vikas, Oct 10 '18 at 12:23
@Code_Mode Your recursion is behaving ***exactly*** like a while loop; just with ***extra overhead*** of pushing each iteration as a recursive call onto the stack. Increasing the nesting limit will get it working with _slightly_ bigger numbers but ***slower*** than a while loop. But as I said earlier, you should be using ***division*** - not loops and certainly _NOT_ recursion. — , Oct 10 '18 at 12:40

score 1 · Accepted Answer · answered Oct 10 '18 at 12:27

You can convert this to an iterative loop rather than a recursive function call. The logic is something like this:

Begin
   declare @counter int;
   select @counter = 0;

   while (true)
   begin
        If (@TimeInMinutes >= 0 and @TimeInMinutes < 480)
            return @counter + (@TimeInMinutes/60)/8  ;                     
        Else If (@TimeInMinutes >= 480 and @TimeInMinutes < 1440)
            return @counter + 1;
        Else If (@TimeInMinutes >= 1440 and @TimeInMinutes < 1920)
            return @counter + 1.5;
        Else If (@TimeInMinutes = 1920)
            return @counter + 2;
        Else If (@TimeInMinutes > 1920)
        begin
            select @TimeInMinutes = (@TimeInMinutes - 1440);
            select @counter := @counter + 1;
        end;
     end;
    return @counter;
End;

Aaargh Gordon, why? I know you know how horrible and wrong this is. It doesn't help OP learn anything. — , Oct 10 '18 at 12:44

How to fix nesting level exceeded Error with recursive function in SQL

1 Answers1