SET NOCOUNT ON;
USE Adventureworks;
GO

-- ============== BEGIN CERT CODE ==============
IF EXISTS (
	SELECT 1
	FROM sys.certificates
	WHERE name = N'TestCert1'
) BEGIN
	PRINT 'Drop certificate';
	DROP CERTIFICATE TestCert1;
END;
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'TestCert1';
--END;
--GO

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

-- ============== END CERT CODE ==============

IF EXISTS (SELECT 1 FROM sys.objects WHERE name = 'EncrTest1') BEGIN
	DROP TABLE dbo.EncrTest1;
END;
GO

CREATE TABLE dbo.EncrTest1 (
	ColID			int					IDENTITY,
	EncrData		varbinary(128)		NOT NULL,
	OriginalType	varchar(32)			NOT NULL
);

DECLARE @str0		varchar(32);
DECLARE @str1		varchar(32);
DECLARE @i0			int;
DECLARE @i1			int;
--DECLARE @iNull		int;		-- Attempting to encrypt NULL returns NULL
DECLARE @bi1		bigint;
DECLARE @d1			datetime;

SELECT
	@str0	= '',
	@str1	= 'test5678901234567890123456789012',
	@i0		= 0,
	@i1		= 123456,
	@bi1	= 1111222233334444,
	@d1		= GETDATE();

INSERT INTO dbo.EncrTest1 (EncrData, OriginalType) VALUES (EncryptByCert (Cert_ID (N'TestCert1'), @str0), 'varchar(32)');
INSERT INTO dbo.EncrTest1 (EncrData, OriginalType) VALUES (EncryptByCert (Cert_ID (N'TestCert1'), @str1), 'varchar(32)');
INSERT INTO dbo.EncrTest1 (EncrData, OriginalType) VALUES (EncryptByCert (Cert_ID (N'TestCert1'), CAST (@i0 AS varchar(11))), 'int');
INSERT INTO dbo.EncrTest1 (EncrData, OriginalType) VALUES (EncryptByCert (Cert_ID (N'TestCert1'), CAST (@i1 AS varchar(11))), 'int');
INSERT INTO dbo.EncrTest1 (EncrData, OriginalType) VALUES (EncryptByCert (Cert_ID (N'TestCert1'), CAST (@bi1 AS varchar(20))), 'bigint');
INSERT INTO dbo.EncrTest1 (EncrData, OriginalType) VALUES (EncryptByCert (Cert_ID (N'TestCert1'), CONVERT (varchar(23), @d1, 126)), 'datetime');

SELECT ColID, EncrData FROM dbo.EncrTest1;

SELECT
	@str0	AS '@str0',
	@str1	AS '@str1',
	@i0		AS '@i0',
	@i1		AS '@i1',
	@bi1	AS '@bi1',
	CONVERT(varchar(23), @d1, 126) AS '@d1';

PRINT 'Decrypt as strings';
SELECT ColID, CAST (DecryptByCert (Cert_ID (N'TestCert1'), EncrData) AS varchar(32)) AS DecryptAsString FROM dbo.EncrTest1;

PRINT 'Decrypt as int';
SELECT
	ColID,
	CAST (
		CAST (
			DecryptByCert (Cert_ID (N'TestCert1'), EncrData)
			AS varchar(32)
		)
		AS int
	) AS StringThenInt
FROM dbo.EncrTest1
WHERE OriginalType = 'int';

PRINT 'Decrypt as bigint';
SELECT
	ColID,
	CAST (
		CAST (
			DecryptByCert (Cert_ID (N'TestCert1'), EncrData)
			AS varchar(32)
		)
		AS bigint
	) AS StringThenBigint
FROM dbo.EncrTest1
WHERE OriginalType = 'bigint';

PRINT 'Decrypt as datetime';
SELECT
	ColID,
	CONVERT (char(23),
		CAST (
			CAST (
				DecryptByCert (Cert_ID (N'TestCert1'), EncrData)
				AS varchar(32)
			)
			AS datetime
		), 126
	) AS StringThenDateTime
FROM dbo.EncrTest1
WHERE OriginalType = 'datetime';