Syntax Error when executing SQL Server Stored Procedure

Question

ALTER PROCEDURE dbo.encrypt 
    @columnname NVARCHAR(100), 
    @TblName NVARCHAR(200) 
WITH ENCRYPTION 
AS 
BEGIN 
    SET NOCOUNT ON; 

    DECLARE @encryptcolumn NVARCHAR(200); 
    DECLARE @AlterQuery NVARCHAR(200);
    DECLARE @TblName NVARCHAR(200); 

    OPEN SYMMETRIC KEY Password_Key DECRYPTION BY CERTIFICATE PasswordCertificate;
    SET @encryptcolumn = (@columnname + 'encrypt');

    IF @encryptcolumn IS NOT NULL
        SET @AlterQuery = 'ALTER TABLE ['+@TblName+'] add  ['+@encryptcolumn+'] varbinary(max)null);' 

        EXEC sp_executesql @AlterQuery; 

        UPDATE @TblName 
        SET @encryptcolumn = ENCRYPTBYKEY(KEY_GUID('datamartSymKey'), CONVERT(varbinary, @columnname)) 
    END 
GO

When I try to execute this, it's throwing this error:

The variable name '@TblName' has already been declared. Variable names must be unique within a query batch or stored procedure.

@TblName is already a parameter but you try to (re)declare it in the procedure body, — sticky bit
– sticky bit, Commented Dec 24, 2018 at 8:17
after removing declare. its not working throwing this error..Must declare the table variable "@TblName" — adams
– adams, Commented Dec 24, 2018 at 8:27
If you want to do an update on a dynamic table name, I'm fairly sure you need to use use sp_executesql to execute it as you did with @AlterQuery just the line before, not just try to pass the table name as a variable. The syntax you're using requires @TblName to be a table variable. — Joachim Isaksson
– Joachim Isaksson, Commented Dec 24, 2018 at 8:28
R Pelzers code below was updated to do just that (although he uses EXEC instead of sp_executesql) Can't test it, but should do the same thing. — Joachim Isaksson
– Joachim Isaksson, Commented Dec 24, 2018 at 8:35

Billal BEGUERADJ · Accepted Answer · 2018-12-24 08:37:09Z

2

You got this error because you already passed @tblName in the procedure. You can solve this by removing the declare and passing the value through the procedure call.

Beside this you have to use EXEC to run the update query, because your table name could change.

ALTER PROCEDURE dbo.encrypt @columnname nvarchar(100), @TblName nvarchar(200) WITH ENCRYPTION AS BEGIN SET NOCOUNT ON; 
    DECLARE @encryptcolumn nvarchar(200); 
    DECLARE @AlterQuery nvarchar(200);

    OPEN SYMMETRIC KEY Password_Key DECRYPTION BY CERTIFICATE PasswordCertificate;
    set @encryptcolumn=(@columnname + 'encrypt');
    if @encryptcolumn is not null 
        SET @AlterQuery='ALTER TABLE ['+@TblName+'] add  ['+@encryptcolumn+'] varbinary(max)null);' 
        exec sp_executesql @AlterQuery; 

        DECLARE @sqlCommand varchar(1000)
        SET @sqlCommand = 'UPDATE ' +@TblName + ' SET ' + @encryptcolumn + ' = ENCRYPTBYKEY(KEY_GUID(''datamartSymKey''),CONVERT(varbinary, ' + @columnname + '))'
        EXEC (@sqlCommand)
    END 
GO

edited Dec 24, 2018 at 8:37

Billal BEGUERADJ

23k45 gold badges125 silver badges142 bronze badges

answered Dec 24, 2018 at 8:17

R Pelzer

1,2881 gold badge16 silver badges34 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

adams Over a year ago

@R Pelzer when i remove declare its throwing this error..Must declare the table variable "@TblName".

R Pelzer Over a year ago

I updated the code. Because you want to use a variable table name, you have to use an 'EXEC' to run an update query.

Collectives™ on Stack Overflow

Syntax Error when executing SQL Server Stored Procedure

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related