sql-server - 如何修复 "Cannot find either column dbo or the user defined function or aggregate, or name is ambiguous"

Question

我需要调用 SQL Server 中的函数，但出现错误!

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

我有昨天从堆栈中获得的函数，当我在管理工作室中单独测试它时，它工作得很好，但是当我将其放入内联函数中并且需要在存储过程中调用时，它会抛出提到的错误，

我保存的函数是:

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

现在我想调用这个函数的存储过程是

     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

但是在 SQL Server Management Studio 中，当我在不使用存储过程的情况下执行单个函数时，函数会提供准确的输出

在 SQL Management Studio 中输出如下:

我读过

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

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

还有更多，但无法解决我的问题，

请帮助我摆脱困境，

谢谢

Answer 1

您的函数返回一个表，因此不能在 select 子句的列列表中使用。

我看到三种方法(可能还有更多；)):

1. 加入函数结果:

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

2. 在列列表中创建子选择:

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

3. 重新定义函数以返回单个值:

   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 */