USP_DATAFORMTEMPLATE_VIEW_ADDRESSINFO

The load procedure used by the view dataform template "Address Information 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.
@ADDRESSTYPE	nvarchar(200)	INOUT	Type
@COUNTRYID	uniqueidentifier	INOUT	Country ID
@COUNTRY	nvarchar(100)	INOUT	Country
@ADDRESSBLOCK	nvarchar(150)	INOUT	Address
@CITY	nvarchar(50)	INOUT	City
@STATE	nvarchar(100)	INOUT	State
@POSTCODE	nvarchar(12)	INOUT	Post code
@ISPRIMARY	bit	INOUT	Is primary
@DONOTMAIL	bit	INOUT	Do not mail
@DONOTMAILREASONCODE	nvarchar(200)	INOUT	Do not mail reason code
@ISCONFIDENTIAL	bit	INOUT	Is confidential
@STARTDATE	date	INOUT	Start date
@ENDDATE	date	INOUT	End date
@DATEADDED	date	INOUT	Date added
@SEASONALSTARTDATE	UDT_MONTHDAY	INOUT	Start date
@SEASONALENDDATE	UDT_MONTHDAY	INOUT	End date
@INFOSOURCECODE	nvarchar(100)	INOUT	Information source
@INFOSOURCECOMMENTS	nvarchar(256)	INOUT	Comments
@STATEABBREVIATION	nvarchar(50)	INOUT

Definition

Copy


CREATE procedure dbo.USP_DATAFORMTEMPLATE_VIEW_ADDRESSINFO
(
  @ID uniqueidentifier,
  @DATALOADED bit = 0 output,
  @ADDRESSTYPE nvarchar(200) = null output,
  @COUNTRYID uniqueidentifier = null output,
  @COUNTRY nvarchar(100) = null output,
  @ADDRESSBLOCK nvarchar(150) = null output,
  @CITY nvarchar(50) = null output,
  @STATE nvarchar(100) = null output,
  @POSTCODE nvarchar(12) = null output,
  @ISPRIMARY bit = null output,
  @DONOTMAIL bit = null output,
  @DONOTMAILREASONCODE nvarchar(200) = null output,
  @ISCONFIDENTIAL bit = null output,
  @STARTDATE date = null output,
  @ENDDATE date = null output,
  @DATEADDED date = null output,
  @SEASONALSTARTDATE dbo.UDT_MONTHDAY = null output,
  @SEASONALENDDATE dbo.UDT_MONTHDAY = null output,
  @INFOSOURCECODE nvarchar(100) = null output,
  @INFOSOURCECOMMENTS nvarchar(256) = null output,
  @STATEABBREVIATION nvarchar(50) = null output

) as
  set nocount on;

  set @DATALOADED = 0;

  select
    @DATALOADED = 1,
    @ADDRESSTYPE = dbo.UFN_ADDRESSTYPECODE_GETDESCRIPTION(ADDRESS.ADDRESSTYPECODEID),
    @COUNTRYID = COUNTRY.ID,
    @COUNTRY = COUNTRY.DESCRIPTION,
    @ADDRESSBLOCK = ADDRESS.ADDRESSBLOCK,
    @CITY = ADDRESS.CITY,
    @STATE = STATE.DESCRIPTION,
    @POSTCODE = ADDRESS.POSTCODE,
    @ISPRIMARY = ADDRESS.ISPRIMARY,
    @DONOTMAIL = ADDRESS.DONOTMAIL,
    @DONOTMAILREASONCODE = dbo.UFN_DONOTMAILREASONCODE_GETDESCRIPTION(ADDRESS.DONOTMAILREASONCODEID),
    @ISCONFIDENTIAL = ADDRESS.ISCONFIDENTIAL,
    @STARTDATE = ADDRESS.HISTORICALSTARTDATE,
    @ENDDATE = ADDRESS.HISTORICALENDDATE,
    @DATEADDED = ADDRESS.DATEADDED,
    @SEASONALSTARTDATE = ADDRESS.STARTDATE,
    @SEASONALENDDATE = ADDRESS.ENDDATE,
    @INFOSOURCECODE = dbo.UFN_INFOSOURCECODE_GETDESCRIPTION(ADDRESSVALIDATIONUPDATE.INFOSOURCECODEID),
    @INFOSOURCECOMMENTS = ADDRESSVALIDATIONUPDATE.INFOSOURCECOMMENTS,
    @STATEABBREVIATION = STATE.ABBREVIATION
  from
    dbo.ADDRESS
  left join
    dbo.ADDRESSVALIDATIONUPDATE on ADDRESSVALIDATIONUPDATE.ID = ADDRESS.ID
  left join
    dbo.COUNTRY on COUNTRY.ID = ADDRESS.COUNTRYID
  left join
    dbo.STATE on STATE.ID = ADDRESS.STATEID
  where 
    ADDRESS.ID = @ID;

return 0;