无效的对象名称'User-Defined-Table-Type'

I am getting error:

我收到错误:

Invalid object name 'UDTT_SuppliersList'.

Which is User-Defined table type as:

用户定义的表类型为:

CREATE TYPE [dbo].[UDTT_SuppliersList] AS TABLE(
    [SupplierID] [int] NOT NULL,
    [Region_Name] [varchar](255) NULL,
    PRIMARY KEY CLUSTERED 
(
    [SupplierID] ASC
)WITH (IGNORE_DUP_KEY = OFF)
)
GO

I am using it (pass as parameter) with StoredProcedures and it is working fine. But today I tried to pass it as parameter to Table-Valued function. Table is created successfully with no errors. But when I try to call that function it is giving me error as mentioned above.

我正在使用它(作为参数传递)与StoredProcedures,它工作正常。但是今天我试着把它作为参数传递给Table-Valued函数。表已成功创建且没有错误。但是,当我尝试调用该函数时,它给出了我上面提到的错误。

declare @tbl_UDTT_SuppliersList UDTT_SuppliersList 
--declare, and assume populated with data

select* from dbo.GetSupplierInvoicesByDate(@tbl_UDTT_SuppliersList)

Here is the function code:

这是功能代码:

ALTER FUNCTION [dbo].[GetSupplierInvoicesByDate]
(
    @tbl_UDTT_SuppliersList as dbo.UDTT_SuppliersList READONLY
)
RETURNS 
@tableList TABLE(
    SupplierID int,
    InvoiceAmount decimal(19, 3)
    )
AS
BEGIN
    INSERT INTO @tableList (
        SupplierID, 
        InvoiceAmount
    )

    SELECT 
    inv.SupplierID as SupplierID
    ,inv.Amount as InvoiceAmount
    FROM dbo.Invoices inv
    inner join UDTT_SuppliersList tSupp on tSupp.SupplierID = inv.SupplierID
    WHERE inv.IsPaidFully = 0

    RETURN
END

1 个解决方案

#1

The problem is with your function:

问题出在你的功能上:

 SELECT 
     inv.SupplierID as SupplierID
    ,inv.Amount as InvoiceAmount
 FROM dbo.Invoices inv
 inner join UDTT_SuppliersList tSupp       -- use use type, not table variable
    on tSupp.SupplierID = inv.SupplierID

should be:

SELECT 
     inv.SupplierID as SupplierID
     ,inv.Amount as InvoiceAmount
FROM dbo.Invoices inv
JOIN @tbl_UDTT_SuppliersList tSupp 
  ON tSupp.SupplierID = inv.SupplierID;

You may wonder why SQL Server allows that, answer is simple Deferred Name Resolution:

您可能想知道为什么SQL Server允许这样做,答案是简单的延迟名称解析:

When a stored procedure is created, the statements in the procedure are parsed for syntactical accuracy. If a syntactical error is encountered in the procedure definition, an error is returned and the stored procedure is not created. If the statements are syntactically correct, the text of the stored procedure is stored in the sys.sql_modules catalog view.

创建存储过程时,将解析过程中的语句的语法准确性。如果在过程定义中遇到语法错误,则返回错误并且不会创建存储过程。如果语句在语法上是正确的,则存储过程的文本存储在sys.sql_modules目录视图中。

When a stored procedure is executed for the first time, the query processor reads the text of the stored procedure from the sys.sql_modules catalog view and checks that the names of the objects used by the procedure are present. This process is called deferred name resolution because table objects referenced by the stored procedure need not exist when the stored procedure is created, but only when it is executed.

当第一次执行存储过程时,查询处理器从sys.sql_modules目录视图中读取存储过程的文本,并检查该过程使用的对象的名称是否存在。此过程称为延迟名称解析,因为存储过程引用的表对象在创建存储过程时不需要存在,但仅在执行时才存在。

Consider using inline syntax for better performance:

考虑使用内联语法以获得更好的性能:

ALTER FUNCTION [dbo].[GetSupplierInvoicesByDate](
    @tbl_UDTT_SuppliersList as dbo.UDTT_SuppliersList READONLY)
RETURNS  TABLE
AS
RETURN (SELECT 
         inv.SupplierID AS SupplierID
         ,inv.Amount AS InvoiceAmount
        FROM dbo.Invoices inv
        JOIN @tbl_UDTT_SuppliersList tSupp 
          ON tSupp.SupplierID = inv.SupplierID
        WHERE inv.IsPaidFully = 0);

I suggest also reading about Inline vs Multistatement Table Function

我建议阅读有关内联与多语句表功能的内容

#1