UFN_BBNC_BUILDFULLADDRESS

Returns a formatted address from its components for Blackbaud Internet Solutions.

Return

Return Type
nvarchar(300)

Parameters

Parameter	Parameter Type	Mode
@ADDRESSBLOCK	nvarchar(150)	IN
@CITY	nvarchar(50)	IN
@STATE	nvarchar(100)	IN
@POSTCODE	nvarchar(12)	IN
@COUNTRY	nvarchar(100)	IN

Definition

Copy



            CREATE function dbo.UFN_BBNC_BUILDFULLADDRESS(@ADDRESSBLOCK nvarchar(150), @CITY nvarchar(50), @STATE nvarchar(100), @POSTCODE nvarchar(12), @COUNTRY nvarchar(100)) 
            returns nvarchar(300)
            as
            begin
            declare @ADDRESSID uniqueidentifier;
                declare @COUNTRYID uniqueidentifier;
                declare @STATEID uniqueidentifier;
      if ( TRY_CAST(@COUNTRY as UNIQUEIDENTIFIER)?IS?NOT?NULL) and exists (select 1 from dbo.COUNTRY where ID = @COUNTRY)
      begin
        select @COUNTRYID=TRY_CAST(@COUNTRY as UNIQUEIDENTIFIER)
      end
      else
      begin
             if @COUNTRY is not null
                    select 
                        @COUNTRYID = [ID]
                    from
                        dbo.COUNTRY
                    where
                        [DESCRIPTION] = @COUNTRY;

             if @COUNTRYID is null
                    select 
                        @COUNTRYID = [ID]
                    from
                        dbo.COUNTRY
                    where
                        [ABBREVIATION] = @COUNTRY;
      end

     if ( TRY_CAST(@STATE as UNIQUEIDENTIFIER)?IS?NOT?NULL) and exists (select 1 from dbo.STATE where ID = @STATE)
     begin
        select @STATEID=TRY_CAST(@STATE as UNIQUEIDENTIFIER)
     end
     else
     begin
                if @STATE is not null
                    select 
                        @STATEID = [ID]
                    from
                        dbo.STATE
                    where
                        [ABBREVIATION] = @STATE;

                if @STATEID is null
                    select 
                        @STATEID = [ID]
                    from
                        dbo.STATE
                    where
                        [DESCRIPTION] = @STATE;
     end
                return dbo.UFN_BUILDFULLADDRESS(@ADDRESSID, @ADDRESSBLOCK, @CITY,    @STATEID, @POSTCODE, @COUNTRYID);
     end