USP_DATALIST_DATAFORMINSTANCETASKREFERENCES

CREATE procedure dbo.USP_DATALIST_DATAFORMINSTANCETASKREFERENCES

@DATAFORMINSTANCEID uniqueidentifier

as
    set nocount on;

    with xmlnamespaces ('bb_appfx_task' as tns, 'bb_appfx_commontypes' as common)
    select 
        T.ID,
        T.NAME,
        T.DESCRIPTION,
        F.NAME as FUNCTIONALAREANAME,
        T.TASKGROUP,
        T.SEQUENCE,
        T.FUNCTIONALAREAID,
        coalesce(T.TASKSPECXML.value('(tns:TaskSpec/@ImageKey)[1]', 'nvarchar(256)'), 'res:tasks') as TASKIMAGEKEY
    from dbo.TASKCATALOG as T
        inner join dbo.UFN_DATAFORMINSTANCE_TASKREFERENCES(@DATAFORMINSTANCEID) as TT on T.ID = TT.TASKID
        inner join dbo.FUNCTIONALAREACATALOG as F on T.FUNCTIONALAREAID = F.ID
    order by F.NAME, T.SEQUENCE, T.TASKGROUP, T.NAME;