Nrthugu

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.

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.