CODEWITHSUNDEEP
sqlsql-server
Luis felipe De jesus Munoz
Luis felipe De jesus Munoz

Reputation: 7923

Create a recursive function in SQL Server

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

Upvotes: 2

Views: 3691

Answers (1)

Yogesh Sharma
Yogesh Sharma

Reputation: 50163

I suspect you need schema prefix :

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

However, the default schema name is dbo

Upvotes: 3

Related Questions