USP_DATAFORMTEMPLATE_VIEW_BBNCCONSTITADDRESSDATAALL

The load procedure used by the view dataform template "NetCommunity Constituent All 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
@ADDRESSID	nvarchar(200)	INOUT	ADDRESSID
@ADDRESSTYPEDESCRIPTION	nvarchar(200)	INOUT	ADDRESSTYPEDESCRIPTION

Definition

Copy



CREATE procedure dbo.USP_DATAFORMTEMPLATE_VIEW_BBNCCONSTITADDRESSDATAALL
                (
                    @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,
                    @ADDRESSID nvarchar(200) = null output, 
                    @ADDRESSTYPEDESCRIPTION nvarchar(200) = null output
                )
                as
                    set nocount on;
                    set @DATALOADED = 1;
              declare @T table(    COUNTRYLONG nvarchar(100), 
                                COUNTRYSHORT nvarchar(5),
                                ADDRESSBLOCK nvarchar(150),
                                CITY nvarchar(50),
                                STATE nvarchar(100), 
                                POSTCODE nvarchar(12),
                                ADDRESSID nvarchar(200),
                                ADDRESSTYPEDESCRIPTION nvarchar(200)
                              );        
                    insert into @T
                    (
              COUNTRYLONG, 
                        COUNTRYSHORT,
                        ADDRESSBLOCK,
                        CITY,
                        STATE,
                        POSTCODE,
                        ADDRESSID,
                        ADDRESSTYPEDESCRIPTION
                    )
                    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,
                    ADDRESS.ID as ADDRESSID,
          [dbo].[UFN_ADDRESSTYPECODE_GETDESCRIPTION](ADDRESS.ADDRESSTYPECODEID) as ADDRESSTYPEDESCRIPTION
                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   
                        @COUNTRYLONG = [COUNTRYLONG],
                        @COUNTRYSHORT = [COUNTRYSHORT],
                        @ADDRESSBLOCK = [ADDRESSBLOCK],
                        @CITY = [CITY],
                        @STATE = [STATE],
                        @POSTCODE = [POSTCODE],
                        @ADDRESSID = [ADDRESSID],
                        @ADDRESSTYPEDESCRIPTION = [ADDRESSTYPEDESCRIPTION]
                    from @T;

                    return 0;