Could not create procedure Error: Must declare the scalar variable “@TableName”

Question

CREATE PROCEDURE [dbo].[sp_DropMasks]
@TableName nvarchar(127)='',
@FieldName nvarchar(127)='',
@MaskFunction nvarchar(127)=''
AS 
BEGIN
    declare @ATableName nvarchar(127)=''
    declare @AFieldName nvarchar(127)=''
    declare @AMaskFunction nvarchar(127)=''
    declare @SqlStr nvarchar(max)=''
    DECLARE crs CURSOR Read_Only Fast_Forward FOR
        SELECT TOP 100 PERCENT
            TableName=tbl.name,
            ColumnName=c.name,
            c.masking_function
        FROM 
            sys.masked_columns AS c inner join 
            sys.tables AS tbl ON c.object_id = tbl.object_id
        WHERE 
            is_masked=1 and 
            tbl.name like '%'+@TableName+'%' and 
            c.name like '%'+@FieldName+'%' and 
            c.masking_function like '%'+@MaskFunction+'%'
    Open crs
    Fetch Next From crs INTO @ATableName,@AFieldName,@AMaskFunction
    WHILE @@FETCH_STATUS = 0
    BEGIN
        set @SqlStr = @SqlStr + 
                    ' ALTER TABLE ' + @ATableName +
                    ' ALTER COLUMN ' + @AFieldName + 
                    ' DROP MASKED ' 
        Fetch Next From crs INTO @ATableName,@AFieldName,@AMaskFunction
    END
    CLOSE crs
    DEALLOCATE crs
    if @SqlStr <> ''
        exec(@SqlStr)
END;

I cannot see the invalid syntax or the missing declaration.

The error is;

Msg 156, Level 15, State 1, Line 28 Incorrect syntax near the keyword 'PROCEDURE'.

Msg 156, Level 15, State 1, Line 28 Incorrect syntax near the keyword 'PROCEDURE'.

Msg 156, Level 15, State 1, Line 28 Incorrect syntax near the keyword 'PROCEDURE'.

Msg 137, Level 15, State 2, Line 45 Must declare the scalar variable "@TableName".

Msg 137, Level 15, State 2, Line 45 Must declare the scalar variable "@TableName".

Msg 137, Level 15, State 2, Line 45 Must declare the scalar variable "@TableName".

Msg 8180, Level 16, State 1, Procedure sp_describe_parameter_encryption, Line 1 [Batch Start Line 27] Statement(s) could not be prepared.

Msg 8180, Level 16, State 1, Procedure sp_describe_parameter_encryption, Line 1 [Batch Start Line 27] Statement(s) could not be prepared.

Msg 8180, Level 16, State 1, Procedure sp_describe_parameter_encryption, Line 1 [Batch Start Line 27] Statement(s) could not be prepared.

If your parameters are intended to be table/column/function names, why on earth would you pick nvarchar(127) as the type/size? sysname, the actual data type used for such names is the same as nvarchar(128). — Damien_The_Unbeliever

Serkan Ekşioğlu Serkan Ekşioğlu · Accepted Answer · 2018-11-13T08:37:09

i forgot to switch always encrypted off when i connect.

thanks to

Cannot add a stored procedure to database due to encryption message

now works just fine.

Could not create procedure Error: Must declare the scalar variable “@TableName”

1 Answers