CODEWITHSUNDEEP
sqlsql-servert-sqldatabase-designsql-server-2008-r2
M_Idrees
M_Idrees

Reputation: 2172

Invalid object name '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. 

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

Upvotes: 1

Views: 3596

Answers (1)

Lukasz Szozda
Lukasz Szozda

Reputation: 175736

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:

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.

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.

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

Upvotes: 2

Related Questions