Create a recursive function in SQL Server

Question

I'm trying to call a function from itself but it gives me the error

'CalculateHoursBetweenDates' is not a recognized built-in function name.

I know this is because I haven't created the function but I am calling it from inside the function itself.

This is my code:

create function CalculateHoursBetweenDates
     (@start varchar(50), 
      @end varchar(50), 
      @days int = 0) 
returns int
as 
begin
    declare @dayDiff int = DATEDIFF(d, @start, @end) + DATEPART(dw, @start)

    if(@dayDiff > 6) 
    begin
        set @days = @days + (DATEPART(dw, @start) -7)
        set @start = DATEADD(d, 7 - (datepart(dw, @start) - 2), @start)
        return CalculateHoursBetweenDates(@start, @end, @days)
    end 

    return @days + DATEDIFF(d, @start, @end)
end

Am I calling it the wrong way or what? Do I have to create an empty function first? I don't get it

Try prefixing it with dbo.: dbo.CalculateHoursBetweenDates — Tyler Roper
– Tyler Roper, Commented Jun 11, 2018 at 20:05
ok, that worked. Can you explain me why it needs the schema to be specified for this? — Luis felipe De jesus Munoz
– Luis felipe De jesus Munoz, Commented Jun 11, 2018 at 20:06
@TylerRoper whoops indeed, it matters. Retracted my vote to close. — lurker
– lurker, Commented Jun 11, 2018 at 20:07

Yogesh Sharma · Accepted Answer · 2018-06-11 20:09:22Z

3

I suspect you need schema prefix :

select <scheme>.CalculateHoursBetweenDates (. . . .)

However, the default schema name is dbo

edited Jun 11, 2018 at 20:09

answered Jun 11, 2018 at 20:06

Yogesh Sharma

50.2k5 gold badges31 silver badges53 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Create a recursive function in SQL Server

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related