Must declare the scalar variable when referencing a table valued parameter

Question

This question follows from this one.

The following SQL works:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[Update_Repair_Details]
    @RepairID BIGINT,
    @NewDetails NewDetails READONLY
AS
BEGIN
    SET NOCOUNT ON;

    DELETE FROM Repair_Details
    WHERE RepairID = @RepairID

    INSERT INTO Repair_Details
        SELECT *, GETDATE()
        FROM @NewDetails
END

But since RepairID is the first column in the user-defined table type, there is no reason to pass it as an additional parameter.

Thus I wrote:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[Update_Repair_Details]
    @NewDetails NewDetails READONLY
AS
BEGIN
    SET NOCOUNT ON;

    DELETE FROM Repair_Details
    WHERE RepairID = @NewDetails.RepairID

    INSERT INTO Repair_Details
        SELECT *, GETDATE()
        FROM @NewDetails
END

which causes an error:

Must declare the scalar variable "@NewDetails"

Why does this have the error while the previous version does not?

score 3 · Accepted Answer · answered Aug 21 '17 at 20:47

3

In this case, @NewDetails is a table; as such, you can't just do WHERE RepairID = @NewDetails.RepairID. You can use IN, EXISTS or a JOIN:

ALTER PROCEDURE [dbo].[Update_Repair_Details]
@NewDetails NewDetails READONLY
AS
BEGIN
    SET NOCOUNT ON;
    DELETE A
    FROM Repair_Details A
    INNER JOIN @NewDetails B
        ON A.RepairID = B.RepairID;

INSERT INTO Repair_Details
SELECT *, GETDATE()
FROM @NewDetails;
END

answered Aug 21 '17 at 20:47

Lamak

69,480
12
108
116

I'd like to understand this answer better (I'm pretty new to SQL). Is A a synonym for Repair_Details and B for @NewDetails? But if I do the substitution, I get the same error as in the OP. Please explain the roles that A and B play. – SezMe Aug 22 '17 at 03:18
I just read that "When you use a table-valued parameter with a JOIN in a FROM clause, you must also alias it,..." so A and B serve the role of alias, which is required for some reason. – SezMe Aug 22 '17 at 03:38
@SezMe yes, those are table aliases. Are you getting the same error?, that seems weird. Please post the definition of the `NewDetails` type – Lamak Aug 22 '17 at 12:19
No, your correction was right. Everything is working nowl. – SezMe Aug 22 '17 at 20:14

Must declare the scalar variable when referencing a table valued parameter

1 Answers1

Linked