引用表值参数时必须声明标量变量

问题描述

这个问题来自这个问题.

以下SQL可以工作:

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

但是，由于RepairID是用户定义表类型的第一列，因此没有理由将其作为附加参数传递.

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

因此，我写道:

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

这会导致错误:

为什么以前的版本没有此错误?

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

推荐答案

在这种情况下，@NewDetails是一个表；因此，您不能只执行WHERE RepairID = @NewDetails.RepairID.您可以使用IN，EXISTS或JOIN:

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

这篇关于引用表值参数时必须声明标量变量的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！