If you look at the documentation at
ENCRYPTBYKEY (Transact-SQL) - SQL Server | Microsoft Docs[
^] section B it gives this example
USE AdventureWorks2012;
ALTER TABLE Sales.CreditCard
ADD CardNumber_Encrypted varbinary(128);
GO
OPEN SYMMETRIC KEY CreditCards_Key11
DECRYPTION BY CERTIFICATE Sales09;
UPDATE Sales.CreditCard
SET CardNumber_Encrypted = EncryptByKey(Key_GUID('CreditCards_Key11'),
CardNumber, 1, CONVERT( varbinary, CreditCardID) );
GO
What is not clear without looking at the database is that
CreditCardID
is an int (see
Sales.CreditCard - AdventureWorks[
^] )
I.e. convert your
int
to a
varbinary
in order to Encrypt it. Convert the
varbinary
returned from DECRYPTBYKEY back to an
int
on select