how to fix "Cannot find either column dbo or the user defined function or aggregate, or name is ambiguous"

Question

I need to call function in SQL server but got error!

 cannot find either column "dbo" or the user-defined function or
    aggregate "dbo.udf_Sum_ExtraHours", or the name is ambiguous.

i have function which i got from stack yesterday and its is working perfect when i tested it in Management studio individually but when i put this in inline function and need to call in stored procedure then it throw mentioned error,

function that i saved is :

ALTER FUNCTION dbo.udf_Sum_ExtraHours
    (
    @strt date,
    @end date

    )
RETURNS  TABLE 
AS
    RETURN

    WITH cte
AS (
    SELECT ExtraHrs 
        ,CASE 
            WHEN left(ExtraHrs, 1) = '-'
                THEN - 1
            ELSE 1
            END AS multiply
        ,right(ExtraHrs, 8) AS timestring
        ,
        --get hours in seconds:
        DATEPART(HOUR, right(ExtraHrs, 8)) * 3600 AS h_in_s
        ,
        --get minutes in seconds:
        DATEPART(MINUTE, right(ExtraHrs, 8)) * 60 AS m_in_s
        ,
        --get seconds:
        DATEPART(SECOND, right(ExtraHrs, 8)) AS s
    FROM  vw_Rept_Attend  where convert(date,AtnDate) between @strt and @end 
    )
    ,CTE3
AS (
    SELECT *
        ,c.h_in_s + c.m_in_s + c.s AddExtra
    FROM cte c
    )
    ,cte4
AS (
    SELECT sum(AddExtra * multiply) mn
    FROM cte3
    )
    ,cte5
AS (
    SELECT mn / 3600 hh
        ,(mn % 3600) / 60 mi
        ,(mn % 3600.0) % 60 ss
    FROM cte4
    )
SELECT 
    cast(hh AS VARCHAR) + ':' + cast(mi AS VARCHAR) + ':' + cast(ss AS VARCHAR) as ExtraHrs
FROM cte5

now stored procedure from where i want to call this function is

     select   UserID,
dbo.udfTimeSpanFromSeconds(Sum(Left(workhrs,2) * 3600 + substring(Convert(varchar(8),workhrs), 4,2) * 60 + substring(Convert(varchar(8),workhrs), 7,2))) as WorkHrs ,

dbo.udf_Sum_ExtraHours('2015-10-12','2015-10-14'),// function which throw error

EmpName,EmpType,UserName, Role,convert(VARCHAR(10),
StartDate,105) as StartDate,convert(VARCHAR(10),EndDate,105) as EndDate
from    vw_Rept_Attend  where  convert(date,AtnDate) between '2015-10-12' and '2015-10-14' 
group by UserID,

EmpName,EmpType,UserName, Role,StartDate,EndDate
      Order by UserID

but in SQL server management studio function give me accurate output when i execute individual function without using stored procedure

out put in SQL management studio looking as:

i read

Cannot find either column "dbo" or the user-defined function or aggregate "dbo.Splitfn", or the name is ambiguous

and Cannot find either column “dbo” or the user-defined function or aggregate “dbo.FN_Split”, or the name is ambiguous

and much more but cannot resolve my issue,

please help me to get out of this,

thanks

Answer 1

Your function returns a table and therefore cannot be used in the columns list of the select clause.

I see three approaches (propably there are more ;)):

1. join on the functions result:

   select [...], extraTime, [...]
   from    vw_Rept_Attend 
   cross apply dbo.udf_Sum_ExtraHours('2015-10-12','2015-10-14') as ex(extraTime)
   [...]
   

2. create a subselect in the columnlist:

   select [...], 
   (    
       select top 1 ExtraHrs from dbo.udf_Sum_ExtraHours('2015-10-12','2015-10-14')
   ) ExtraHrs, [...]
   from    vw_Rept_Attend 
   [...]
   

3. redefine the function to return a single value:

   ALTER FUNCTION dbo.udf_Sum_ExtraHours
   (
   @strt date,
   @end date
   
   )
   RETURNS INT -- or VARCHAR or some other single value type
   /* method body returning single value */