USP_DATALIST_ADHOCQUERYDEPENDENCY

CREATE procedure dbo.USP_DATALIST_ADHOCQUERYDEPENDENCY
(
    @ID uniqueidentifier
)
as
    set nocount on;

    --Returns other adhoc queries using the current adhoc query

    select
        ADHOCQUERYDEPENDENCY.ID as ID,
        ADHOCQUERY.ID as DEPENDENCYID,
        ADHOCQUERY.NAME,
        'Ad-hoc Query' as OBJECTTYPE,
            1 as ACTION,
        'A dependent ad-hoc query'  as ADDITIONALINFO,
        1 as LINKTYPE
    from dbo.ADHOCQUERYDEPENDENCY 
    inner join ADHOCQUERYIDSETDEPENDENCY on ADHOCQUERYIDSETDEPENDENCY.ID = ADHOCQUERYDEPENDENCY.RECORDID
    inner join ADHOCQUERY on ADHOCQUERY.ID = ADHOCQUERYIDSETDEPENDENCY.ADHOCQUERYID
    where ADHOCQUERYDEPENDENCY.DBTABLENAME = 'ADHOCQUERYIDSETDEPENDENCY' --Adhoc query

    and ADHOCQUERYDEPENDENCY.ADHOCQUERYID = @ID
    and ADHOCQUERY.ID <> @ID -- exclude dependencies to itself when using a selection from another query

        union
    select
        ADHOCQUERYDEPENDENCY.ID as ID,
        EXPORTPROCESS.ID as DEPENDENCYID, 
        EXPORTPROCESS.NAME,
        ADHOCQUERYDEPENDENCY.TABLENAME as OBJECTTYPE,
        1 as ACTION,
        'A dependent export process' as ADDITIONALINFO,
        2 as LINKTYPE
    from dbo.ADHOCQUERYDEPENDENCY
    inner join EXPORTPROCESS on EXPORTPROCESS.ID = ADHOCQUERYDEPENDENCY.RECORDID
    where ADHOCQUERYDEPENDENCY.DBTABLENAME = 'EXPORTPROCESS' --Export Process

    and ADHOCQUERYDEPENDENCY.ADHOCQUERYID = @ID 
        union
    select 
        ADHOCQUERYDEPENDENCY.ID as ID,
        ADHOCQUERYDEPENDENCY.RECORDID as DEPENDENCYID, 
        ADHOCQUERYDEPENDENCY.TABLENAME as NAME,
        'Smart query' as OBJECTTYPE,
            0 as ACTION,
        ADHOCQUERYDEPENDENCY.TABLEDESCRIPTION as ADDITIONALINFO,
        3 as LINKTYPE
    from dbo.ADHOCQUERYDEPENDENCY
    where ADHOCQUERYDEPENDENCY.DBTABLENAME = 'SMARTQUERYINSTANCE' --Smart query

    and ADHOCQUERYDEPENDENCY.ADHOCQUERYID = @ID  
        union
    select 
        ADHOCQUERYDEPENDENCY.ID as ID,
        ADHOCQUERYDEPENDENCY.RECORDID as DEPENDENCYID, 
        ADHOCQUERYDEPENDENCY.TABLENAME as NAME,
        'Smart field' as OBJECTTYPE,
            0 as ACTION,
        ADHOCQUERYDEPENDENCY.TABLEDESCRIPTION as ADDITIONALINFO,
        4 as LINKTYPE
    from dbo.ADHOCQUERYDEPENDENCY
    where ADHOCQUERYDEPENDENCY.DBTABLENAME = 'SMARTFIELD' --Smartfield

    and ADHOCQUERYDEPENDENCY.ADHOCQUERYID = @ID
        union
    select 
        ADHOCQUERYDEPENDENCY.ID as ID,
        ADHOCQUERYDEPENDENCY.RECORDID as DEPENDENCYID, 
        ADHOCQUERYDEPENDENCY.TABLENAME as NAME,
        'Task' as OBJECTTYPE,
        0 as ACTION,
        ADHOCQUERYDEPENDENCY.TABLEDESCRIPTION as ADDITIONALINFO,
        5 as LINKTYPE
    from dbo.ADHOCQUERYDEPENDENCY
    where ADHOCQUERYDEPENDENCY.DBTABLENAME = 'TASKCATALOG' --Task

    and ADHOCQUERYDEPENDENCY.ADHOCQUERYID = @ID

  order by ACTION desc;