USP_DATAFORMTEMPLATE_VIEW_BBNCCONSTITADDITIONALADDRESSDATA

The load procedure used by the view dataform template "NetCommunity Constituent Additional Address Data View Form"

Parameters

Parameter	Parameter Type	Mode	Description
@ID	uniqueidentifier	IN	The input ID parameter used to load the fields defined on the form.
@DATALOADED	bit	INOUT	Output parameter indicating whether or not data was actually loaded.
@COUNTRYLONG	nvarchar(100)	INOUT	COUNTRYLONG
@COUNTRYSHORT	nvarchar(5)	INOUT	COUNTRYSHORT
@ADDRESSBLOCK	nvarchar(150)	INOUT	ADDRESSBLOCK
@CITY	nvarchar(50)	INOUT	CITY
@STATE	nvarchar(100)	INOUT	STATE
@POSTCODE	nvarchar(12)	INOUT	POSTCODE
@ADDRESSTYPECODE	nvarchar(200)	INOUT	ADDRESSTYPECODE
@HISTORICALSTARTDATE	date	INOUT	HISTORICALSTARTDATE
@HISTORICALENDDATE	date	INOUT	HISTORICALENDDATE
@STARTDATE	nvarchar(4)	INOUT	STARTDATE
@ENDDATE	nvarchar(4)	INOUT	ENDDATE
@DONOTMAIL	bit	INOUT	DONOTMAIL
@ISPRIMARY	bit	INOUT	ISPRIMARY

Definition

Copy



                CREATE procedure dbo.USP_DATAFORMTEMPLATE_VIEW_BBNCCONSTITADDITIONALADDRESSDATA
                (
                    @ID uniqueidentifier,
                    @DATALOADED bit = 0 output,
                    @COUNTRYLONG nvarchar(100) = null output,
                    @COUNTRYSHORT nvarchar(5) = null output,
                    @ADDRESSBLOCK nvarchar(150) = null output,
                    @CITY nvarchar(50) = null output,
                    @STATE nvarchar(100) = null output,
                    @POSTCODE nvarchar(12) = null output,
          @ADDRESSTYPECODE nvarchar(200) = null output,
          @HISTORICALSTARTDATE date = null output,
          @HISTORICALENDDATE date = null output,
          @STARTDATE nvarchar(4) = null output,
          @ENDDATE nvarchar(4) = null output,
          @DONOTMAIL bit = null output,
          @ISPRIMARY bit = null output
                )
                as
                    set nocount on;
                    set @DATALOADED = 0;        



      declare @T table(COUNTRYLONG nvarchar(100), 
                                     COUNTRYSHORT nvarchar(5),
                                     ADDRESSBLOCK nvarchar(150),
                                     CITY nvarchar(50),
                                     STATE nvarchar(100), --TMV 4/17/2007 CR272644-041207 For UK and New Zealand states we get the full description so the data type size needs to match

                                     POSTCODE nvarchar(12),
                   ADDRESSTYPECODE nvarchar(200),
                   HISTORICALSTARTDATE date,
                   HISTORICALENDDATE date,
                   STARTDATE nvarchar(4),
                   ENDDATE nvarchar(4),
                   DONOTMAIL bit,
                   ISPRIMARY bit
                                     );
                    insert into @T 
                    (
                        COUNTRYLONG, 
                        COUNTRYSHORT,
                        ADDRESSBLOCK,
                        CITY,
                        STATE,
                        POSTCODE,
            ADDRESSTYPECODE,
            HISTORICALSTARTDATE,
            HISTORICALENDDATE,
            STARTDATE,
            ENDDATE,
            DONOTMAIL,
            ISPRIMARY
                    )
                    select
                    COUNTRY.DESCRIPTION as COUNTRYLONG,
                    COUNTRY.ABBREVIATION as COUNTRYSHORT,
                    ADDRESS.ADDRESSBLOCK,
                    ADDRESS.CITY,
                    case
                        --TMV 04/11/2007 CR272266-040907 BBNC expects the full description for New Zealand city, GUID copied from dbo.USP_COUNTRYADDRESSFORMAT_GETDATA.

                        --TMV 04/13/2007 CR272671-041207 BBNC expects the full description for United Kingdom county, GUID copied from dbo.USP_COUNTRYADDRESSFORMAT_GETDATA.

                        when 
                        (
                            COUNTRY.COUNTRYADDRESSFORMATID = '959809FB-8FA7-4A19-888F-9951BD9B29D3' --United Kingdom

                            or
                            COUNTRY.COUNTRYADDRESSFORMATID = 'A3B050A5-E1C8-4E1B-99AE-40E9FCADA0BC' --New Zealand

                        )
                        then
                            STATE.DESCRIPTION
                        else
                            STATE.ABBREVIATION
                    end as STATE,
                    ADDRESS.POSTCODE,
          [dbo].[UFN_ADDRESSTYPECODE_GETDESCRIPTION](ADDRESS.ADDRESSTYPECODEID) as ADDRESSTYPECODE,
          ADDRESS.HISTORICALSTARTDATE,
          ADDRESS.HISTORICALENDDATE,
          ADDRESS.STARTDATE,
          ADDRESS.ENDDATE,
          ADDRESS.DONOTMAIL,
          ADDRESS.ISPRIMARY
                from dbo.ADDRESS
                left join
                    dbo.COUNTRY on ADDRESS.COUNTRYID = COUNTRY.ID
                left join
                    dbo.STATE on ADDRESS.STATEID = STATE.ID
        where ADDRESS.ID = @ID;

                    select top 1 
            @DATALOADED = 1,
                        @COUNTRYLONG = [COUNTRYLONG],
                        @COUNTRYSHORT = [COUNTRYSHORT],
                        @ADDRESSBLOCK = [ADDRESSBLOCK],
                        @CITY = [CITY],
                        @STATE = [STATE],
                        @POSTCODE = [POSTCODE],
            @ADDRESSTYPECODE = [ADDRESSTYPECODE],
            @HISTORICALSTARTDATE = [HISTORICALSTARTDATE],
            @HISTORICALENDDATE = [HISTORICALENDDATE],
            @STARTDATE = [STARTDATE],
            @ENDDATE = [ENDDATE],
            @DONOTMAIL = [DONOTMAIL],
            @ISPRIMARY = [ISPRIMARY]
                    from @T;

                    return 0;