USP_GET_BBCS_INFO

create procedure dbo.USP_GET_BBCS_INFO
(
@BLACKBAUDSERVICEUSER nvarchar(2048) output, @BLACKBAUDSERVICEPASSWORD nvarchar(2048) output, @CLARIFYSITEID nvarchar(50) output, @BBCSHOSTNAME nvarchar(2048) output
)
    as
    begin

           set @BLACKBAUDSERVICEUSER = null;
              set @BLACKBAUDSERVICEPASSWORD = null;
              set @CLARIFYSITEID = null;
              set @BBCSHOSTNAME = null;

          if (exists(select 1 from sys.symmetric_keys where name ='sym_BBInfinity') 
                    and exists (select 1 from sys.asymmetric_keys where name ='asym_BBInfinity')
              and exists (select 1 from sys.Objects where  Object_id = OBJECT_ID(N'dbo.EXTERNALSERVICECONFIGURATION') and Type = N'U')
              and exists (select 1 from sys.Objects where  Object_id = OBJECT_ID(N'dbo.INSTALLATIONINFO') and Type = N'U')
              )
          begin        
            open symmetric key sym_BBInfinity decryption by asymmetric key asym_BBInfinity;

            select @blackbaudServiceUser = DECRYPTBYKEY(Value)
            from [dbo].[EXTERNALSERVICECONFIGURATION] s    
            where NAME = 'BlackbaudServiceUser' and SITEID = 1

            select @blackbaudServicePassword= DECRYPTBYKEY(Value)
            from [dbo].[EXTERNALSERVICECONFIGURATION] s    
            where NAME = 'BlackbaudServicePassword' and SITEID = 1    

            close symmetric key sym_BBInfinity;

            select @BBCSHOSTNAME= Value
            from [dbo].[EXTERNALSERVICECONFIGURATION] s    
            where NAME = 'BlackbaudServiceHost' and SITEID = 1    


            select @clarifySiteID = CLARIFYSITEID from INSTALLATIONINFO
          end

          if Not ((isnull(@BLACKBAUDSERVICEUSER,'') <> '' and isnull(@blackbaudservicepassword,'') <> '' and isnull(@CLARIFYSITEID,'') <> '' and isnull(@BBCSHOSTNAME,'')<>''))
          begin  
              exec dbo.spAddUpdate_Error 0, 'List Unsubscribe SQL CLR', 'BBCS credential or siteID does not exist', null --Not found add error to table

         end
         return 0;
    end