Error Must declare the scalar variable with Table Valued Parameter

Question

I am not getting what I am doing wrong here. From the update segment, I am getting this error:

Must declare the scalar variable "@OrderTestTVP".

Here is my table-valued parameter type

CREATE TYPE [dbo].[TVP_OrderTest] AS TABLE(
    [OrderTestId] [int] NULL,
    [OrderId] [int] NOT NULL,
    [TestCode] [varchar](10) NOT NULL
    )
GO

ALTER PROCEDURE [dbo].[TVP_Save_OrderTest]
    @OrderTestTVP TVP_OrderTest READONLY
AS
BEGIN
    BEGIN TRY
        DECLARE @TableOfIdentities TABLE (IdentValue BIGINT, TestCode varchar(10) )
        INSERT INTO OrderTest
                (
                    OrderId
                    ,TestCode
                ) 
                OUTPUT Inserted.OrderTestId,inserted.TestCode 
                INTO @TableOfIdentities(IdentValue,TestCode)
                   SELECT  OrderId
                    ,TestCode
            FROM @OrderTestTVP
            WHERE OrdertestID = 0

        UPDATE 
                OrderTest
                SET   
                    OrderId = @OrderTestTVP.OrderId,
                    TestCode = @OrderTestTVP.TestCode
                    FROM OrderTest INNER JOIN @OrderTestTVP
                  ON OrderTest.OrderTestId = @OrderTestTVP.OrderTestId

        SELECT * FROM @TableOfIdentities
    END TRY
    BEGIN CATCH
        exec error_catch
    END CATCH
END

@OrderTestTVP TVP_OrderTest READONLY is missing a type declaration - right? — Gary Walker, Nov 14 '13 at 22:20
I have that type CREATE TYPE [dbo].[TVP_OrderTest] AS TABLE(). In fact it does not throw any error when I keep only 'insert' segment — Pratap Das, Nov 14 '13 at 22:22

score 16 · Answer 1 · answered Nov 14 '13 at 23:58

I'd say you have to use a table alias for the join operation:

UPDATE 
 OrderTest
 SET   
     OrderId = o.OrderId,
     TestCode = o.TestCode
 FROM OrderTest INNER JOIN @OrderTestTVP o
     ON OrderTest.OrderTestId = o.OrderTestId

I have no SQL at hand right now to try but if I recall correctly, table variables need aliases if used in expressions.

Error Must declare the scalar variable with Table Valued Parameter

1 Answers1