USP_SAVE_EXTERNALSERVICECONFIGURATION

      CREATE procedure dbo.USP_SAVE_EXTERNALSERVICECONFIGURATION
      (
          @CHANGEAGENTID uniqueidentifier = null,
        @HOST nvarchar(2048),
        @USER nvarchar(2048),
        @PASS nvarchar(2048),
        @USESECURE nvarchar(2048),
        @TRANSACTIONALPATH nvarchar(2048) = '' output,
        @GENERALPURPOSEPATH nvarchar(2048) = '' output,
        @STATUSPATH nvarchar(2048) = '' output,
        @BINDING nvarchar(256) = '0' output,
        @USESHAREDSERVICESCOMMSERVICE nvarchar(2048) = 0 output--,

        --@CHUNKSIZE nvarchar(2048)

      )
          as
          begin
              set nocount on;

            exec dbo.USP_GET_KEY_ACCESS;

              if @CHANGEAGENTID is null  
                  exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output;

             begin try

            --Enable BBCS

                      set @TRANSACTIONALPATH = 'Transactional/EmailSubmission/blackbaud/services/EmailSubmissionService.svc'
                      set @GENERALPURPOSEPATH = 'GeneralPurpose/EmailSubmission/blackbaud/services/EmailSubmissionService.svc'
                      set @STATUSPATH = 'EmailStatus/blackbaud/services/EmailStatusService.svc' 
                      set @BINDING = '0'
                      set @USESHAREDSERVICESCOMMSERVICE = 'true'

             MERGE dbo.EXTERNALSERVICECONFIGURATION  AS target
                USING (SELECT 'BlackbaudServiceHost') AS source (NAME)
                ON (target.NAME = source.NAME)
                WHEN MATCHED THEN 
                    update set VALUE = @Host
                WHEN NOT MATCHED THEN    
                    Insert (ID,NAME,VALUE,SITEID,ADDEDBYID, CHANGEDBYID)
                    values(NEWID(),'BlackbaudServiceHost', @Host, 1, @CHANGEAGENTID, @CHANGEAGENTID);

              MERGE dbo.EXTERNALSERVICECONFIGURATION  AS target
                USING (SELECT 'BlackbaudServiceUser') AS source (NAME)
                ON (target.NAME = source.NAME)
                WHEN MATCHED THEN 
                    update set VALUE = encryptbykey(key_guid('sym_BBInfinity'), @USER)
                WHEN NOT MATCHED THEN    
                    Insert (ID,NAME,VALUE,SITEID,ADDEDBYID, CHANGEDBYID)
                    values(NEWID(),'BlackbaudServiceUser', encryptbykey(key_guid('sym_BBInfinity'), @USER), 1, @CHANGEAGENTID, @CHANGEAGENTID);

               MERGE dbo.EXTERNALSERVICECONFIGURATION  AS target
                USING (SELECT 'BlackbaudServicePassword') AS source (NAME)
                ON (target.NAME = source.NAME)
                WHEN MATCHED THEN 
                    update set VALUE = encryptbykey(key_guid('sym_BBInfinity'), @PASS)
                WHEN NOT MATCHED THEN    
                    Insert (ID,NAME,VALUE,SITEID,ADDEDBYID, CHANGEDBYID)
                    values(NEWID(),'BlackbaudServicePassword', encryptbykey(key_guid('sym_BBInfinity'), @PASS), 1, @CHANGEAGENTID, @CHANGEAGENTID);

               MERGE dbo.EXTERNALSERVICECONFIGURATION  AS target
                USING (SELECT 'BlackbaudServiceUseSecureServices') AS source (NAME)
                ON (target.NAME = source.NAME)
                WHEN MATCHED THEN 
                    update set VALUE = @USESECURE
                WHEN NOT MATCHED THEN    
                    Insert (ID,NAME,VALUE,SITEID,ADDEDBYID, CHANGEDBYID)
                    values(NEWID(),'BlackbaudServiceUseSecureServices', @USESECURE, 1, @CHANGEAGENTID, @CHANGEAGENTID);

               MERGE dbo.EXTERNALSERVICECONFIGURATION  AS target
                USING (SELECT 'EmailServiceBinding') AS source (NAME)
                ON (target.NAME = source.NAME)
                WHEN MATCHED THEN 
                    update set VALUE = @BINDING
                WHEN NOT MATCHED THEN    
                    Insert (ID,NAME,VALUE,SITEID,ADDEDBYID, CHANGEDBYID)
                    values(NEWID(),'EmailServiceBinding', @BINDING, 1, @CHANGEAGENTID, @CHANGEAGENTID);

               MERGE dbo.EXTERNALSERVICECONFIGURATION  AS target
                USING (SELECT 'UseSharedServicesCommunicationService') AS source (NAME)
                ON (target.NAME = source.NAME)
                WHEN MATCHED THEN 
                    update set VALUE = @USESHAREDSERVICESCOMMSERVICE
                WHEN NOT MATCHED THEN    
                    Insert (ID,NAME,VALUE,SITEID,ADDEDBYID, CHANGEDBYID)
                    values(NEWID(),'UseSharedServicesCommunicationService', @USESHAREDSERVICESCOMMSERVICE, 1, @CHANGEAGENTID, @CHANGEAGENTID);

               MERGE dbo.EXTERNALSERVICECONFIGURATION  AS target
                USING (SELECT 'EmailServiceTransactionalPath') AS source (NAME)
                ON (target.NAME = source.NAME)
                WHEN MATCHED THEN 
                    update set VALUE = @TRANSACTIONALPATH
                WHEN NOT MATCHED THEN    
                    Insert (ID,NAME,VALUE,SITEID,ADDEDBYID, CHANGEDBYID)
                    values(NEWID(),'EmailServiceTransactionalPath', @TRANSACTIONALPATH, 1, @CHANGEAGENTID, @CHANGEAGENTID);

              MERGE dbo.EXTERNALSERVICECONFIGURATION  AS target
                USING (SELECT 'EmailServiceGeneralPurposePath') AS source (NAME)
                ON (target.NAME = source.NAME)
                WHEN MATCHED THEN 
                    update set VALUE = @GENERALPURPOSEPATH
                WHEN NOT MATCHED THEN    
                    Insert (ID,NAME,VALUE,SITEID,ADDEDBYID, CHANGEDBYID)
                    values(NEWID(),'EmailServiceGeneralPurposePath', @GENERALPURPOSEPATH, 1, @CHANGEAGENTID, @CHANGEAGENTID);

               MERGE dbo.EXTERNALSERVICECONFIGURATION  AS target
                USING (SELECT 'EmailServiceStatusPath') AS source (NAME)
                ON (target.NAME = source.NAME)
                WHEN MATCHED THEN 
                    update set VALUE = @STATUSPATH
                WHEN NOT MATCHED THEN    
                    Insert (ID,NAME,VALUE,SITEID,ADDEDBYID, CHANGEDBYID)
                    values(NEWID(),'EmailServiceStatusPath', @STATUSPATH, 1, @CHANGEAGENTID, @CHANGEAGENTID);

              close symmetric key sym_BBInfinity;
            end try
              begin catch
              close symmetric key sym_BBInfinity;
                  exec dbo.USP_RAISE_ERROR;
                  return 1;
              end catch

          return 0;
        end