USE [IEOSMultiTenancy];  
GO  
CREATE PROCEDURE [dbo].[AddAndEncrypt]    
	@ConnectionString nvarchar(max),
	@UserGroupName NVarChar(max)	
AS
BEGIN

OPEN SYMMETRIC KEY IDSCANDBENCKEY
   DECRYPTION BY CERTIFICATE IDSCANDBCERT;  

Declare @encryptedConnectionString nvarchar(Max); 

SET @encryptedConnectionString = EncryptByKey(Key_GUID('IDSCANDBENCKEY'), @ConnectionString);

INSERT INTO [dbo].[MultiTenancyMapping]
           ([Id]
           ,[UserGroupName]
           ,[ConnectionString]
           ,[CreatedDatetime])
     VALUES (NEWID(),@UserGroupName, @encryptedConnectionString, GETDATE())
END

GO  
CREATE PROCEDURE [dbo].[GetDecryptedItem]      
 	@UserGroupName NVarChar(max)	
AS
BEGIN


OPEN SYMMETRIC KEY IDSCANDBENCKEY  
   DECRYPTION BY CERTIFICATE IDSCANDBCERT;  

SELECT Id, UserGroupName, CONVERT(nvarchar(max), DecryptByKey(ConnectionString)) AS 'ConnectionString', CreatedDatetime FROM MultiTenancyMapping WHERE UserGroupName = @UserGroupName
CLOSE SYMMETRIC KEY IDSCANDBENCKEY;

END

GO  
CREATE PROCEDURE [dbo].[GetDecryptedItems]      

AS
BEGIN

OPEN SYMMETRIC KEY IDSCANDBENCKEY  
   DECRYPTION BY CERTIFICATE IDSCANDBCERT;  

SELECT
Id, UserGroupName, CONVERT(nvarchar(max), DecryptByKey(ConnectionString)) AS 'ConnectionString', CreatedDatetime FROM MultiTenancyMapping;
 
CLOSE SYMMETRIC KEY IDSCANDBENCKEY;

END

GO  
CREATE PROCEDURE [dbo].[UpdateAndEncrypt]     
    	@ConnectionString nvarchar(max),
	@UserGroupName NVarChar(max),
        @GroupID uniqueidentifier		

AS
BEGIN

OPEN SYMMETRIC KEY IDSCANDBENCKEY
   DECRYPTION BY CERTIFICATE IDSCANDBCERT;  

Declare @encryptedConnectionString nvarchar(Max); 

SET @encryptedConnectionString = EncryptByKey(Key_GUID('IDSCANDBENCKEY'), @ConnectionString);

UPDATE [dbo].[MultiTenancyMapping]
        SET [UserGroupName] = @UserGroupName
           ,[ConnectionString] = @encryptedConnectionString
           ,[CreatedDatetime] = GETDATE() WHERE Id = @GroupID
END