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

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

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
  )

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 

SQL Server must declare the scalar variable - Querychat, SQL Server must declare the scalar variable – Querychat. The declaration of the SQL Server scalar variable contains values in certain data types that can be used locally either temporary held or returned in functions, procedures, or with various SQL statements and can be easily implemented. Your code doesn't throw 'Must declare the scalar variable' exception but always v1, v2 values are null – Müslüm ÖZTÜRK Jan 26 '14 at 13:57


@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 

Must declare the scalar variable, You can't concatenate an int to a string. Instead of: SET @sql = N'DECLARE @Rt int; SET @Rt = ' + @RowTo;. You need: SET @sql� Must declare the scalar variable "@year" That's because you are trying to run a bunch of code that includes BOTH the stored procedure execution AND the query below it (!). Just highlight the one you want to run or delete/comment out the one you are not interested in.


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.

Must Declare Scalar Variable Error, @id as part of the execution variable @sql is doing nothing. It is not tied to the declared and set variable unless you build the string around it, i.e. concatenate it � System.Data.SqlClient.SqlException: 'Must declare the scalar variable "@VersionId".' Here is the calling code that produces the exception:


Must Declare The Scalar Variable Sql Error, Download Must Declare The Scalar Variable Sql Error PDF. Download Must Declare The Scalar Variable Sql Error DOC. . Studio plus local sql statements still� Must declare the scalar variable "@varname" Tens of hours of research, tried multiple solutions without sucess. My aim is to create a login page that uses 2 textboxes, and a button, where it checks if the user exits based on the information stored in a Sql Database.


Must Declare The Scalar Variable Sql Function, Note that you must declare the scalar variable sql function after and paste this? Evolved a scalar function that error must declare variable sql server scalar. Must declare the scalar variable "@col". c827. MON2. Msg 137, Level 15, State 1, Line 3. Must declare the scalar variable "@col". e7ef. MON2-----DECLARE @MONTHP INT. DECLARE @REPORTEDMONTHS


Must declare the scalar variable "@value4". – SQLServerCentral, FROM '+@database_name+'..test_table'. exec (@qry). Error :- Msg 137, Level 15, State 1, Line 1. Must declare the scalar variable "@value4". Hi, Yes but this is a table variable not a scalar variable (ie you try to use it as if it is a varchar variable). Still I doubt your current approach could work (bcp won't run in the same context and won't have access to your table variable, you can't provide directly the select statement ?).