while encrypt a particular column dynamically.....i update the value in table ...but im getting value in column as "space " only.......i could not able to see the encrypted value in table.......

See more:

SQL-Server-2005

VB

SQL-Server

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go


ALTER PROCEDURE [dbo].[ETL_Encrypt] (@inputcolumn VARCHAR(200), @outputcolumn VARCHAR(max), @transtable VARCHAR(max))
AS
DECLARE @SQL VARCHAR(MAX)
BEGIN

SET @SQL= 'OPEN SYMMETRIC KEY TestTableKey DECRYPTION BY CERTIFICATE EncryptTestCert '

PRINT @SQL
EXEC(@SQL)
SET @SQL= 'UPDATE '+ @transtable +' SET '+ @outputcolumn +'= ENCRYPTBYKEY(KEY_GUID(''TestTableKey''),'+ @inputcolumn +')'

PRINT @SQL
EXEC(@SQL)
SET @SQL= 'CLOSE SYMMETRIC KEY TestTableKey '

PRINT @SQL
EXEC(@SQL)
END