Must declare the scalar variable “@AccessLevel”. in SQL Server 2016

Question

I need to write a stored procedure for this: I pass the table value parameters to this stored procedure, first I need to check data Access and RoleId exist in the database; if not, I insert it into table.

I wrote this :

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 

but I get these errors:

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".

What's the problem? How can I solve it?

Edit

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

Answer 1

Please try below changes -

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 

Answer 2

@AccessLevel is a table variable, so you will have to treat it as one, and also avoid using *:

CREATE PROCEDURE [dbo].[InsertAndUpdateAccessLevel]
    @AccessLevel AS AccessLevel READONLY
AS
BEGIN

    INSERT INTO RoleAccess (
        RoleID,
        Access,
        IsDelete)
    SELECT
        A.RoleID,
        A.Access,
        A.IsDelete
    FROM
        @AccessLevel AS A
    WHERE
        NOT EXISTS (
            SELECT 
                'RoleAccess does not exist currently'
            FROM
                RoleAccess AS R
            WHERE
                R.RoleID = A.RoleID AND
                R.Access = A.Access)

END 

Answer 3

first I need to check data Access and RoleId exist in the database; if not, I insert it into table.

This is the wrong approach. You should let the database do the checking, using a unique constraint or index.

CREATE UNIQUE INDEX unq_roleaccess_roleid_accessid on roleaccess(roleid, accessid);

Voila! You will now have data integrity. You can then use a TRY/CATCH block for your stored procedure:

BEGIN
    BEGIN TRY

        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 TRY;
    BEGIN CATCH
    -- do something here if there is a duplicate
    END CATCH;
END;

It is quite important to let the database validate the data when it can. In particular, this prevents race conditions -- where two different threads run the stored procedure at the same time.

Perhaps more importantly, this validates data integrity regardless of how the data is changed. The unique constraint/index ensures no duplicates even when some clueless person goes into the database and manually alters data.