USP_DATAFORMTEMPLATE_VIEW_QUEUEPROCESS

The load procedure used by the view dataform template "Queue Process 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.
@NAME	nvarchar(255)	INOUT	Name
@DESCRIPTION	nvarchar(1000)	INOUT	Description
@BUSINESSPROCESSCATALOGID	uniqueidentifier	INOUT	Business process catalog ID
@LASTBUSINESSPROCESSSTATUSID	uniqueidentifier	INOUT	Last business process status ID
@PARAMETERSID	uniqueidentifier	INOUT	Parameters ID
@LASTRUNHASENDED	bit	INOUT	Has ended
@STEPPARAMETERSETSDONOTEXIST	bit	INOUT	For some steps the parameter set does not exist

Definition

Copy



CREATE procedure dbo.USP_DATAFORMTEMPLATE_VIEW_QUEUEPROCESS(
    @ID uniqueidentifier,
    @DATALOADED bit = 0 output,
    @NAME nvarchar(255) = null output,
    @DESCRIPTION nvarchar(1000) = null output,
    @BUSINESSPROCESSCATALOGID uniqueidentifier = null output,
    @LASTBUSINESSPROCESSSTATUSID uniqueidentifier = null output,
    @PARAMETERSID uniqueidentifier = null output,
    @LASTRUNHASENDED bit = null output,
    @STEPPARAMETERSETSDONOTEXIST bit = null output
)
with execute as caller
as
    set nocount on;

    begin try

        set @DATALOADED = 0

        select @DATALOADED = 1,
            @NAME = QUEUEPROCESS.NAME,
            @DESCRIPTION = QUEUEPROCESS.DESCRIPTION,
            @PARAMETERSID = QUEUEPROCESS.ID
        from dbo.QUEUEPROCESS
        where QUEUEPROCESS.ID = @ID;

        set @STEPPARAMETERSETSDONOTEXIST = 0;

        if @DATALOADED = 1 and exists
        (
            select
                1
            from
                dbo.QUEUEPROCESS
                cross apply QUEUEPROCESS.STEPLISTXML.nodes('/ArrayOfQueueProcessStep/QueueProcessStep') QUEUEPROCESSSTEP(ELEMENT)
                left join dbo.V_BUSINESSPROCESSPARAMETERSETS on
                    QUEUEPROCESSSTEP.ELEMENT.value('@ParameterSetID', 'uniqueidentifier') = V_BUSINESSPROCESSPARAMETERSETS.PARAMETERSETID
                    and QUEUEPROCESSSTEP.ELEMENT.value('@BusinessProcessID', 'uniqueidentifier') = V_BUSINESSPROCESSPARAMETERSETS.BUSINESSPROCESSID
            where
                QUEUEPROCESS.ID = @ID
                and V_BUSINESSPROCESSPARAMETERSETS.PARAMETERSETID is null
        )
            set @STEPPARAMETERSETSDONOTEXIST = 1;

        set @BUSINESSPROCESSCATALOGID = '1b4480fc-b7ab-4edb-a0bf-c897eded1f58';

        select top(1) @LASTBUSINESSPROCESSSTATUSID = BUSINESSPROCESSSTATUS.ID,
            @LASTRUNHASENDED = case when BUSINESSPROCESSSTATUS.ENDEDON is null then convert(bit, 0) else convert(bit, 1) end
        from dbo.BUSINESSPROCESSSTATUS 
        inner join dbo.QUEUEPROCESSSTATUS on BUSINESSPROCESSSTATUS.ID = QUEUEPROCESSSTATUS.ID
        inner join dbo.QUEUEPROCESS on QUEUEPROCESSSTATUS.PARAMETERSETID = QUEUEPROCESS.ID
        where BUSINESSPROCESSCATALOGID = '1b4480fc-b7ab-4edb-a0bf-c897eded1f58' 
        and QUEUEPROCESS.ID = @ID order by STARTEDON desc;

        if @LASTBUSINESSPROCESSSTATUSID is null
        begin
            set @LASTBUSINESSPROCESSSTATUSID = '00000000-0000-0000-0000-000000000000'
        end;

    end try
    begin catch
        exec dbo.USP_RAISE_ERROR;
        return 1;
    end catch;

    return 0;