CODEWITHSUNDEEP
sqlsql-server
adams
adams

Reputation: 301

Syntax Error when executing SQL Server Stored Procedure

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.

Upvotes: 0

Views: 346

Answers (1)

R Pelzer
R Pelzer

Reputation: 1268

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

Upvotes: 2

Related Questions