USP_DATAFORMTEMPLATE_VIEW_RELATIONSHIPSBYMARRIAGE

The load procedure used by the view dataform template "Relationships by Marriage View"

Parameters

Parameter	Parameter Type	Mode	Description
@ID	nvarchar(72)	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.
@RELATIONSHIPS	xml	INOUT	RELATIONSHIPS

Definition

Copy


CREATE procedure dbo.USP_DATAFORMTEMPLATE_VIEW_RELATIONSHIPSBYMARRIAGE
(
  @ID nvarchar(72),
  @DATALOADED bit = 0 output,
  @RELATIONSHIPS xml = null output
)
as
  set nocount on;

  set @DATALOADED = 0;

  declare @PARENTID uniqueidentifier;
  declare @CHILDID uniqueidentifier;
  declare @CONSTITUENTNAME nvarchar(155);

  set @PARENTID = convert(uniqueidentifier, substring(@ID, 0, 37));
  set @CHILDID = convert(uniqueidentifier, substring(@ID, 37, 37));

  select @CONSTITUENTNAME = (select NAME from dbo.UFN_CONSTITUENT_DISPLAYNAME(@CHILDID));

  select 
    @DATALOADED = 1, 
    @RELATIONSHIPS = 
    (
     select     
      ID,
      @CONSTITUENTNAME + ' is the:' RELATIONSHIP_NAME,
      RECIPROCALCONSTITUENTID, 
      RECIPROCALTYPECODEID RECIPROCAL_TYPECODEID,
      NF_OTHER.NAME + ' is the:' RECIPROCAL_NAME, 
      RELATIONSHIPCONSTITUENTID,
      RELATIONSHIPTYPECODEID RELATIONSHIP_TYPECODEID
    from dbo.UFN_RELATIONSHIPSBYMARRIAGE(@PARENTID, @CHILDID) RELATIONSHIPS 
    outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(RELATIONSHIPS.RECIPROCALCONSTITUENTID) NF_OTHER
    for xml raw('ITEM'),type,elements,root('RELATIONSHIPS'),BINARY BASE64)

  return 0;