SQL Server 2016表值参数存储过程报错：必须声明标量变量@AccessLevel

问题背景 #

我编写了一个SQL Server存储过程，目的是接收表值参数AccessLevel，先检查RoleAccess表中是否存在对应RoleId和Access的记录，不存在就插入参数数据。相关代码和执行时的报错信息如下：

表值类型定义

CREATE TYPE [dbo].[AccessLevel] AS TABLE (
    [RoleId] [INT] NULL,
    [Access] [NVARCHAR](MAX) NULL,
    [IsDelete] [BIT] NULL
)

初始存储过程代码

ALTER PROCEDURE [dbo].[InsertAndUpdateAccessLevel] (@AccessLevel AS AccessLevel READONLY)
AS
BEGIN
    DECLARE @AC AccessLevel;
    SET @AC = @AccessLevel;
    IF NOT EXISTS (SELECT Id FROM RoleAccess WHERE RoleAccess.RoleId = @AC.RoleId AND RoleAccess.Access = @AC.Access)
        INSERT INTO RoleAccess (RoleId, Access, IsDelete) SELECT * FROM @AccessLevel
END

执行报错信息

Msg 137, Level 16, State 1, Procedure InsertAndUpdateAccessLevel, Line 8 [Batch Start Line 7]
必须声明标量变量 "@AccessLevel"。
Msg 137, Level 16, State 1, Procedure InsertAndUpdateAccessLevel, Line 8 [Batch Start Line 7]
必须声明标量变量 "@AC"。
Msg 137, Level 16, State 1, Procedure InsertAndUpdateAccessLevel, Line 15 [Batch Start Line 7]
必须声明标量变量 "@AC"。
Msg 137, Level 16, State 1, Procedure InsertAndUpdateAccessLevel, Line 16 [Batch Start Line 7]
必须声明标量变量 "@AC"。

报错原因 #

核心问题在于对表值参数/表变量的用法理解错误：

1. 表值参数是表类型，不能直接用SET赋值给另一个表变量，SET @AC = @AccessLevel;是完全错误的语法。
2. @AC.RoleId这种写法试图直接获取表变量的单个字段值，但表变量是一个数据集合（可能包含多条记录），SQL Server无法识别你要取哪一条的字段，语法解析失败后就抛出了“必须声明标量变量”的错误。

解决方法 #

方法1：直接使用表值参数（推荐，更简洁高效）

我们可以直接关联表值参数和目标表，筛选出不存在的记录进行插入，不需要额外的表变量：

ALTER PROCEDURE [dbo].[InsertAndUpdateAccessLevel] (@AccessLevel AS AccessLevel READONLY)
AS
BEGIN
    -- 插入RoleAccess中不存在的匹配记录（按RoleId+Access判断）
    INSERT INTO RoleAccess (RoleId, Access, IsDelete)
    SELECT al.RoleId, al.Access, al.IsDelete
    FROM @AccessLevel al
    WHERE NOT EXISTS (
        SELECT 1 
        FROM RoleAccess ra
        WHERE ra.RoleId = al.RoleId 
          AND ra.Access = al.Access
    )
END

方法2：若需使用表变量（先处理参数数据的场景）

如果业务需要先把表值参数的数据导入到表变量中处理，正确的赋值方式是用INSERT INTO ... SELECT ...：

ALTER PROCEDURE [dbo].[InsertAndUpdateAccessLevel] (@AccessLevel AS AccessLevel READONLY)
AS
BEGIN
    DECLARE @AC AccessLevel;
    -- 正确将表值参数的数据导入表变量
    INSERT INTO @AC (RoleId, Access, IsDelete)
    SELECT RoleId, Access, IsDelete FROM @AccessLevel;

    -- 关联表变量和目标表，插入不存在的记录
    INSERT INTO RoleAccess (RoleId, Access, IsDelete)
    SELECT ac.RoleId, ac.Access, ac.IsDelete
    FROM @AC ac
    WHERE NOT EXISTS (
        SELECT 1 
        FROM RoleAccess ra
        WHERE ra.RoleId = ac.RoleId 
          AND ra.Access = ac.Access
    )
END

关键注意事项 #

• 表值参数定义时加了READONLY，所以不能直接对它执行INSERT/UPDATE/DELETE操作，如需修改参数数据，必须先导入到普通表变量中。
• 处理表类型变量（包括表值参数）时，要把它当作一张表来操作，必须通过SELECT或JOIN访问其中的数据，不能像标量变量那样直接用.引用字段。

内容的提问来源于stack exchange，提问作者kianoush dortaj