CODEWITHSUNDEEP
sqlrecursionsql-server-2014recursive-querysql-function
Vikas
Vikas

Reputation: 7175

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

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.

Upvotes: 0

Views: 1265

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270401

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;

Upvotes: 1

Related Questions