开发者社区> 问答> 正文

在SQL Server 2016中,必须声明标量变量“ @AccessLevel”?

我需要编写一个这样的存储过程:我通过表中的值,编写这个存储过程,首先我需要检查数据Access和RoleId存在于数据库中; 如果没有,需要将其插入表中。

我写了这个:

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] Must declare the scalar variable "@AccessLevel".

Msg 137, Level 16, State 1, Procedure InsertAndUpdateAccessLevel, Line 8 [Batch Start Line 7] Must declare the scalar variable "@AC".

Msg 137, Level 16, State 1, Procedure InsertAndUpdateAccessLevel, Line 15 [Batch Start Line 7] Must declare the scalar variable "@AC".

Msg 137, Level 16, State 1, Procedure InsertAndUpdateAccessLevel, Line 16 [Batch Start Line 7] Must declare the scalar variable "@AC".

我该如何解决?

展开
收起
心有灵_夕 2019-11-29 21:48:30 1278 0
1 条回答
写回答
取消 提交回答
  • 请尝试以下更改:

    ALTER PROCEDURE [dbo].[InsertAndUpdateAccessLevel]
    (
     @AccessLevel AccessLevel READONLY
    )
    AS
    BEGIN
        IF NOT EXISTS
        (
            SELECT
                Id
            FROM RoleAccess 
            WHERE exists (select 1 from @AccessLevel as x where 
                  RoleAccess.RoleId=x.RoleId
                  AND RoleAccess.Access=x.Access)
        )
        INSERT INTO  RoleAccess (RoleId, Access, IsDelete )
        SELECT * FROM @AccessLevel
    END
    
    2019-11-29 21:49:10
    赞同 展开评论 打赏
问答排行榜
最热
最新

相关电子书

更多
SQL Server 2017 立即下载
GeoMesa on Spark SQL 立即下载
原生SQL on Hadoop引擎- Apache HAWQ 2.x最新技术解密malili 立即下载