SET NOCOUNT ON;
USE AdventureWorks;
GO

-- Create the database master key
IF NOT EXISTS (
	SELECT 1
	FROM sys.symmetric_keys
	WHERE name = '##MS_DatabaseMasterKey##'
) BEGIN
	PRINT 'Create master key';
	CREATE MASTER KEY ENCRYPTION BY PASSWORD = N'thuyuy73r_!r-kuwru4huy&Yar=-athE';
END;
GO

-- Create the database certificate
IF NOT EXISTS (
	SELECT 1
	FROM sys.certificates
	WHERE name = N'TestCert1'
) BEGIN
	PRINT 'Create certificate';
	CREATE CERTIFICATE TestCert1 WITH SUBJECT = N'TestCert1';
END;
GO

-- Create the symmetric key
IF NOT EXISTS (
	SELECT 1
	FROM sys.symmetric_keys
	WHERE name = 'TestKey1'
) BEGIN
	PRINT 'Create key';
	CREATE SYMMETRIC KEY TestKey1
	WITH ALGORITHM = AES_256
	ENCRYPTION BY CERTIFICATE TestCert1;
END;
GO

OPEN SYMMETRIC KEY TestKey1 DECRYPTION BY CERTIFICATE TestCert1;
GO

PRINT '========================================= Simple string encryption/decryption =========================================';

DECLARE @string1			varchar(10);
DECLARE @encryptedString	varbinary(68);
DECLARE @resultString		varchar(10);

SELECT @string1 = 'simple';

SELECT @string1 AS 'Original string'

SELECT EncryptByKey(Key_GUID (N'TestKey1'), @string1) AS 'Result of in-line Encrypt';

-- Store in variable
SELECT @encryptedString = EncryptByKey(Key_GUID (N'TestKey1'), @string1);

SELECT @encryptedString AS '@encryptedString';

SELECT DecryptByKey(@encryptedString) AS 'varbinary result of in-line DecryptByKey'

SELECT CONVERT(varchar(10), DecryptByKey(@encryptedString)) AS 'Result of in-line DecryptByKey';

SELECT @resultString = CONVERT(varchar(10), DecryptByKey(@encryptedString));

SELECT @resultString AS '@resultString';

PRINT '========================================= Simple integer encryption/decryption =========================================';

DECLARE @int1			int;
DECLARE @encryptedInt	varbinary(68);
DECLARE @resultint		int;

SELECT @int1 = 123456;

SELECT @int1 AS 'Original integer'

SELECT EncryptByKey(Key_GUID (N'TestKey1'), CONVERT(varchar(11), @int1)) AS 'Result of in-line Encrypt';

-- Store in variable
SELECT @encryptedInt = EncryptByKey(Key_GUID (N'TestKey1'), CONVERT(varchar(11), @int1));

SELECT @encryptedInt AS '@encryptedInt';

SELECT DecryptByKey(@encryptedInt) AS 'varbinary result of in-line DecryptByKey'

SELECT CONVERT(varchar(11), DecryptByKey(@encryptedInt)) AS 'Result of in-line DecryptByKey converted to varchar (this is still a string)';

SELECT CONVERT(int, CONVERT(varchar(11), DecryptByKey(@encryptedInt))) AS 'Result of in-line DecryptByKey converted to varchar and then integer';

SELECT @resultInt = CONVERT(int, CONVERT(varchar(11), DecryptByKey(@encryptedInt)));

SELECT @resultInt AS '@resultInt';