我在存储过程中声明了一个自定义类型的变量，但仍然收到错误“必须声明标量变量”

I created a custom table type

我创建了一个自定义表类型

Create type dbo.InsertPatient as table
(SSN Int,
 LastUpdated Datetime,
 PatientId varchar(50),
 McdNo varchar(50),
 mcrno varchar(50)
)
GO

I tried to declare an input parameter using this type

我尝试使用此类型声明输入参数

Create PROCEDURE [dbo].[proc_PatientDt_Insert]
(
    @PatientTable InsertPatient READONLY,
    @FacilityID int,
    @IsDeleted bit = 0
)
AS
BEGIN
    SET NOCOUNT OFF
    DECLARE @Err int

    IF(@IsDeleted = 1)
    BEGIN
        DELETE FROM [Patient]
        WHERE LTRIM(RTRIM([Patientid])) = LTRIM(RTRIM(@PatientTable.PatientId))
          AND FacilityID = @FacilityID

        RETURN
    END

    SET @Err = @@Error

    RETURN @Err
END

When I try to execute, I get this error

当我尝试执行时，我收到此错误

Msg 137, Level 16, State 1, Procedure proc_PatientDt_Insert, Line 18
Must declare the scalar variable "@PatientTable".

消息137，级别16，状态1，过程proc_PatientDt_Insert，第18行必须声明标量变量“@PatientTable”。

1 个解决方案

#1

The error message is a little misleading, but it's because you're trying to use a Table Varaible as if it was a plain old scalar variable. Table variables work more or less like tables. While it's possible the variable will only have one row, it could have 100. You need to SELECT and/or JOIN from that variable. If you only ever expect to have 1 row, or want to take an arbitrary first row and ignore others, you could change

错误消息有点误导，但这是因为你试图使用Table Varaible，好像它是一个普通的旧标量变量。表变量或多或少像表一样工作。虽然变量可能只有一行，但它可能有100.你需要从该变量中选择SELECT和/或JOIN。如果你只想要有一行，或者想要任意第一行并忽略其他行，你可以改变

LTRIM(RTRIM([Patientid])) = LTRIM(RTRIM(@PatientTable.PatientId)) AND FacilityID=@FacilityID

to

至

LTRIM(RTRIM([Patientid])) = LTRIM(RTRIM(SELECT TOP 1 PatientId FROM @PatientTable)) AND FacilityID=@FacilityID

But you'd probably be best off doing:

但你可能最好做的事情是：

DELETE p
FROM Patient p
INNER JOIN @PatientTable p2
ON LTRIM(RTRIM(p.PatientId) =  LTRIM(RTRIM(p2.PatientId)
WHERE p.FacilityId = @FacilityId