USP_DATAFORMTEMPLATE_VIEW_DESIGNATIONFIELDS

The load procedure used by the view dataform template "Designation Fields View"

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.
@PURPOSENAME	nvarchar(512)	INOUT	Purpose Name
@PURPOSELOOKUPID	nvarchar(100)	INOUT	Purpose Lookup ID
@PURPOSEDESCRIPTION	nvarchar(255)	INOUT	Purpose Description
@PURPOSESITE	nvarchar(250)	INOUT	Purpose Site
@PURPOSECATEGORY	nvarchar(100)	INOUT	Purpose Category
@PURPOSETYPE	nvarchar(100)	INOUT	Purpose Type
@PURPOSEPACKAGE	nvarchar(100)	INOUT	Purpose Package
@DESIGNATIONLOOKUPID	nvarchar(512)	INOUT	Designation Lookup ID
@DESIGNATIONPUBLICNAME	nvarchar(512)	INOUT	Designation Public Name
@DESIGNATIONSTARTDATE	datetime	INOUT	Designation Start Date
@DESIGNATIONENDDATE	datetime	INOUT	Designation End Date

Definition

Copy



CREATE procedure dbo.USP_DATAFORMTEMPLATE_VIEW_DESIGNATIONFIELDS
(
    @ID uniqueidentifier,
    @DATALOADED bit = 0 output,
    @PURPOSENAME nvarchar(512) = null output,
    @PURPOSELOOKUPID nvarchar(100) = null output,
    @PURPOSEDESCRIPTION nvarchar(255) = null output,  
  @PURPOSESITE nvarchar(250) = null output,
  @PURPOSECATEGORY nvarchar(100) = null output,
  @PURPOSETYPE nvarchar(100) = null output,
  @PURPOSEPACKAGE nvarchar(100) = null output,
  @DESIGNATIONLOOKUPID nvarchar(512) = null output,
  @DESIGNATIONPUBLICNAME nvarchar(512) = null output,
  @DESIGNATIONSTARTDATE datetime = null output,
  @DESIGNATIONENDDATE datetime = null output
)
as
    set nocount on;

    -- be sure to set this, in case the select returns no rows

    set @DATALOADED = 0;

    -- populate the output parameters, which correspond to fields on the form.  Note that

    -- we set @DATALOADED = 1 to indicate that the load was successful.  Otherwise, the system

    -- will display a "no data loaded" message.

    select @DATALOADED = 1,
    @PURPOSENAME = ISNULL(case DL.VANITYNAME when '' then null else DL.VANITYNAME END, DL.NAME),
    @PURPOSELOOKUPID = DL.USERID,
    @PURPOSEDESCRIPTION = DL.DESCRIPTION,
    @PURPOSESITE = S.NAME,
    @PURPOSECATEGORY = DLCC.DESCRIPTION,
    @PURPOSETYPE = DLT.DESCRIPTION,
    @PURPOSEPACKAGE = SPP.NAME,
    @DESIGNATIONLOOKUPID = D.USERID,
    @DESIGNATIONPUBLICNAME = D.VANITYNAME,
    @DESIGNATIONSTARTDATE = D.STARTDATE,
    @DESIGNATIONENDDATE = D.ENDDATE
  from dbo.DESIGNATION D
  left outer join dbo.DESIGNATIONLEVEL DL on DL.ID = coalesce(D.designationlevel5ID, D.designationlevel4ID, D.designationlevel3ID, D.designationlevel2ID, D.designationlevel1ID)
  left outer join dbo.SITE S on S.ID = DL.SITEID
  left outer join dbo.DESIGNATIONLEVELCATEGORYCODE DLCC on DLCC.ID = DL.DESIGNATIONLEVELCATEGORYCODEID
  left outer join dbo.DESIGNATIONLEVELTYPE DLT on DLT.ID = DL.DESIGNATIONLEVELTYPEID
  left outer join dbo.STEWARDSHIPPACKAGEPROCESS SPP on SPP.ID = DL.STEWARDSHIPPACKAGEPROCESSID
    where D.ID = @ID
    return 0;