这里我加密列值并插入表中，而im插入加密值则存储为“spacevalues”。在表格中....我怎么能解决这个....这个代码我动态地尝试.... [英] Here i encrypt the column value and inserting in a table,while im inserting encrypted value is stored as "spacevalues" in table....how can i solve this....this code i tried with dynamically....

问题描述

紧急请

Urgent please

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 '
--OPEN SYMMETRIC KEY TestTableKey DECRYPTION BY CERTIFICATE EncryptTestCert
PRINT @SQL
EXEC(@SQL)
SET @SQL= 'UPDATE '+ @transtable +' SET '+ @outputcolumn +'= ENCRYPTBYKEY(KEY_GUID(''TestTableKey''),convert(varbinary,'+ @inputcolumn +'))'
--UPDATE TestTable SET EncryptSecondCol = ENCRYPTBYKEY(KEY_GUID('TestTableKey'),SecondCol)
PRINT @SQL
EXEC(@SQL)
SET @SQL= 'CLOSE SYMMETRIC KEY TestTableKey '
--CLOSE SYMMETRIC KEY TestTableKey
PRINT @SQL
EXEC(@SQL)
END

推荐答案

试试这个查询：

SET @ SQL ='UPDATE'+ @transtable +'SET'+ @outputcolumn +'= ENCRYPTBYKEY（KEY_GUID（N''TestTableKey''），convert（varbinary，'+ @inputcolumn +'））'

try this query:
SET @SQL= 'UPDATE '+ @transtable +' SET '+ @outputcolumn +'= ENCRYPTBYKEY(KEY_GUID(N''TestTableKey''),convert(varbinary,'+ @inputcolumn +'))'

这篇关于这里我加密列值并插入表中，而im插入加密值则存储为“spacevalues”。在表格中....我怎么能解决这个....这个代码我动态地尝试....的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！