USP_DATAFORMTEMPLATE_EDITLOAD_DESIGNATIONHYBRID

The load procedure used by the edit dataform template "Designation Hybrid Edit Data 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.
@TSLONG	bigint	INOUT	Output parameter indicating the TSLONG value of the record being edited. This is used to manage multi-user concurrency issues when multiple users access the same record.
@PARENTDESIGNATIONPATH	nvarchar(512)	INOUT
@PARENTLOOKUPID	nvarchar(412)	INOUT
@PARENTDESIGNATIONID	uniqueidentifier	INOUT
@DESIGNATIONLEVELTYPEID	uniqueidentifier	INOUT	Type
@NAME	nvarchar(100)	INOUT	Name
@VANITYNAME	nvarchar(512)	INOUT	Public name
@DESCRIPTION	nvarchar(255)	INOUT	Description
@DESIGNATIONLEVELCATEGORYCODEID	uniqueidentifier	INOUT	Category
@LOOKUPID	nvarchar(100)	INOUT	Lookup ID
@DESIGNATIONREPORT1CODEID	uniqueidentifier	INOUT	Report code 1
@DESIGNATIONREPORT2CODEID	uniqueidentifier	INOUT	Report code 2
@STARTDATE	datetime	INOUT	Start date
@ENDDATE	datetime	INOUT	End date
@SITEID	uniqueidentifier	INOUT	Site

Definition

Copy


CREATE procedure dbo.USP_DATAFORMTEMPLATE_EDITLOAD_DESIGNATIONHYBRID
(
  @ID uniqueidentifier,
    @DATALOADED bit = 0 output,
  @TSLONG bigint = 0 output,
    @PARENTDESIGNATIONPATH nvarchar(512) = null output,
    @PARENTLOOKUPID nvarchar(412) = null output,
    @PARENTDESIGNATIONID uniqueidentifier = null output,
    @DESIGNATIONLEVELTYPEID uniqueidentifier = null output,
    @NAME nvarchar(100) = null output,
    @VANITYNAME nvarchar(512) = null output,
    @DESCRIPTION nvarchar(255) = null output,
    @DESIGNATIONLEVELCATEGORYCODEID uniqueidentifier = null output,
    @LOOKUPID nvarchar(100) = null output,
    @DESIGNATIONREPORT1CODEID uniqueidentifier = null output,
    @DESIGNATIONREPORT2CODEID uniqueidentifier = null output,
    @STARTDATE datetime = null output,
    @ENDDATE datetime = null output,
  @SITEID uniqueidentifier = null output
)
as
    set nocount on;

    set @DATALOADED = 0;
    set @TSLONG = 0;

    declare @PARENTID uniqueidentifier;
    declare @DESIGNATIONLEVELID uniqueidentifier;

    select 
        @DATALOADED = 1,
        @TSLONG = DESIGNATION.TSLONG,
        @PARENTID = PARENTDESIGNATION.ID,
        @PARENTDESIGNATIONPATH = DESIGNATION.NAME,
        @PARENTLOOKUPID = PARENTDESIGNATION.USERID,
        @PARENTDESIGNATIONID = PARENTDESIGNATION.ID,
        @DESIGNATIONREPORT1CODEID = DESIGNATION.DESIGNATIONREPORT1CODEID,
        @DESIGNATIONREPORT2CODEID = DESIGNATION.DESIGNATIONREPORT2CODEID,
        @STARTDATE = DESIGNATION.STARTDATE,
        @ENDDATE = DESIGNATION.ENDDATE,
        @DESIGNATIONLEVELID = case
            when DESIGNATION.DESIGNATIONLEVEL2ID is null then DESIGNATION.DESIGNATIONLEVEL1ID
            when DESIGNATION.DESIGNATIONLEVEL3ID is null then DESIGNATION.DESIGNATIONLEVEL2ID
            when DESIGNATION.DESIGNATIONLEVEL4ID is null then DESIGNATION.DESIGNATIONLEVEL3ID
            when DESIGNATION.DESIGNATIONLEVEL5ID is null then DESIGNATION.DESIGNATIONLEVEL4ID
            else DESIGNATION.DESIGNATIONLEVEL5ID
        end,
        @VANITYNAME = DESIGNATION.VANITYNAME
    from
        dbo.DESIGNATION
    left outer join
        dbo.DESIGNATION [PARENTDESIGNATION] on DESIGNATION.DESIGNATIONLEVEL1ID = PARENTDESIGNATION.DESIGNATIONLEVEL1ID
            and (DESIGNATION.DESIGNATIONLEVEL2ID = PARENTDESIGNATION.DESIGNATIONLEVEL2ID or (PARENTDESIGNATION.DESIGNATIONLEVEL2ID is null and DESIGNATION.DESIGNATIONLEVEL3ID is null))
            and (DESIGNATION.DESIGNATIONLEVEL3ID = PARENTDESIGNATION.DESIGNATIONLEVEL3ID or (PARENTDESIGNATION.DESIGNATIONLEVEL3ID is null and DESIGNATION.DESIGNATIONLEVEL4ID is null))
            and (DESIGNATION.DESIGNATIONLEVEL4ID = PARENTDESIGNATION.DESIGNATIONLEVEL4ID or (PARENTDESIGNATION.DESIGNATIONLEVEL4ID is null and DESIGNATION.DESIGNATIONLEVEL5ID is null))
            and DESIGNATION.ID <> PARENTDESIGNATION.ID
    where
        DESIGNATION.ID = @ID;

    if @PARENTLOOKUPID is not null
        set @PARENTLOOKUPID = @PARENTLOOKUPID + '-';

    select
        @LOOKUPID = USERID,
        @DESCRIPTION = DESCRIPTION,
        @DESIGNATIONLEVELCATEGORYCODEID = DESIGNATIONLEVELCATEGORYCODEID,
        @DESIGNATIONLEVELTYPEID = DESIGNATIONLEVELTYPEID,
        @NAME = NAME,
    @SITEID = SITEID
    from
        dbo.DESIGNATIONLEVEL
    where
        ID = @DESIGNATIONLEVELID;

    return 0;