USP_DATALIST_FEATUREGROUPFEATURE

CREATE procedure dbo.USP_DATALIST_FEATUREGROUPFEATURE(
    @FEATUREGROUPCATALOGID uniqueidentifier,
    @FEATURETYPECODE tinyint = null
)
as
    set nocount on;

    select BATCHTYPECATALOG.ID,
        FEATUREGROUPFEATURE.FEATURETYPE,
        FEATUREGROUPFEATURE.FEATURETYPECODE,
        BATCHTYPECATALOG.NAME,
        BATCHTYPECATALOG.DESCRIPTION
    from dbo.BATCHTYPECATALOG
    inner join dbo.FEATUREGROUPFEATURE on FEATUREGROUPFEATURE.FEATURETYPECODE = 0
        and BATCHTYPECATALOG.ID = FEATUREGROUPFEATURE.FEATUREID
    where FEATUREGROUPFEATURE.FEATUREGROUPCATALOGID = @FEATUREGROUPCATALOGID and
        (@FEATURETYPECODE is null or @FEATURETYPECODE = 0)

    union all

    select BUSINESSPROCESSCATALOG.ID,
        FEATUREGROUPFEATURE.FEATURETYPE,
        FEATUREGROUPFEATURE.FEATURETYPECODE,
        BUSINESSPROCESSCATALOG.NAME,
        BUSINESSPROCESSCATALOG.DESCRIPTION      
    from dbo.BUSINESSPROCESSCATALOG
    inner join dbo.FEATUREGROUPFEATURE on FEATUREGROUPFEATURE.FEATURETYPECODE = 1 
        and BUSINESSPROCESSCATALOG.ID = FEATUREGROUPFEATURE.FEATUREID
    where FEATUREGROUPFEATURE.FEATUREGROUPCATALOGID = @FEATUREGROUPCATALOGID and
        (@FEATURETYPECODE is null or @FEATURETYPECODE = 1)

    union all

    select CODETABLECATALOG.ID,
           FEATUREGROUPFEATURE.FEATURETYPE,
           FEATUREGROUPFEATURE.FEATURETYPECODE,
           CODETABLECATALOG.UINAME NAME,
           '' DESCRIPTION
    from dbo.CODETABLECATALOG
    inner join dbo.FEATUREGROUPFEATURE on FEATUREGROUPFEATURE.FEATURETYPECODE = 2
        and CODETABLECATALOG.ID = FEATUREGROUPFEATURE.FEATUREID
    where FEATUREGROUPFEATURE.FEATUREGROUPCATALOGID = @FEATUREGROUPCATALOGID and
        (@FEATURETYPECODE is null or @FEATURETYPECODE = 2)

    union all

    select DASHBOARDCATALOG.ID,
           FEATUREGROUPFEATURE.FEATURETYPE,
           FEATUREGROUPFEATURE.FEATURETYPECODE,
           DASHBOARDCATALOG.NAME,
           DASHBOARDCATALOG.DESCRIPTION
    from dbo.DASHBOARDCATALOG
    inner join dbo.FEATUREGROUPFEATURE on FEATUREGROUPFEATURE.FEATURETYPECODE = 3
        and DASHBOARDCATALOG.ID = FEATUREGROUPFEATURE.FEATUREID
    where FEATUREGROUPFEATURE.FEATUREGROUPCATALOGID = @FEATUREGROUPCATALOGID and
        (@FEATURETYPECODE is null or @FEATURETYPECODE = 3)

    union all

    select DATAFORMINSTANCECATALOG.ID,
           FEATUREGROUPFEATURE.FEATURETYPE,
           FEATUREGROUPFEATURE.FEATURETYPECODE,
           DATAFORMINSTANCECATALOG.UINAME NAME,
           DATAFORMINSTANCECATALOG.DESCRIPTION
    from dbo.DATAFORMINSTANCECATALOG
    inner join dbo.FEATUREGROUPFEATURE on FEATUREGROUPFEATURE.FEATURETYPECODE = 4
        and DATAFORMINSTANCECATALOG.ID = FEATUREGROUPFEATURE.FEATUREID
    where FEATUREGROUPFEATURE.FEATUREGROUPCATALOGID = @FEATUREGROUPCATALOGID and
        (@FEATURETYPECODE is null or @FEATURETYPECODE = 4)

    union all

    select KPICATALOG.ID,
           FEATUREGROUPFEATURE.FEATURETYPE,
           FEATUREGROUPFEATURE.FEATURETYPECODE,
           KPICATALOG.NAME,
           KPICATALOG.DESCRIPTION
    from dbo.KPICATALOG
    inner join dbo.FEATUREGROUPFEATURE on FEATUREGROUPFEATURE.FEATURETYPECODE = 5
        and KPICATALOG.ID = FEATUREGROUPFEATURE.FEATUREID
    where FEATUREGROUPFEATURE.FEATUREGROUPCATALOGID = @FEATUREGROUPCATALOGID and
        (@FEATURETYPECODE is null or @FEATURETYPECODE = 5)

    union all

    select MAPENTITYCATALOG.ID,
           FEATUREGROUPFEATURE.FEATURETYPE,
           FEATUREGROUPFEATURE.FEATURETYPECODE,
           MAPENTITYCATALOG.UINAME NAME,
           MAPENTITYCATALOG.DESCRIPTION
    from dbo.MAPENTITYCATALOG
    inner join dbo.FEATUREGROUPFEATURE on FEATUREGROUPFEATURE.FEATURETYPECODE = 6
        and MAPENTITYCATALOG.ID = FEATUREGROUPFEATURE.FEATUREID
    where FEATUREGROUPFEATURE.FEATUREGROUPCATALOGID = @FEATUREGROUPCATALOGID and
        (@FEATURETYPECODE is null or @FEATURETYPECODE = 6)

    union all

    select DATALISTCATALOG.ID,
           FEATUREGROUPFEATURE.FEATURETYPE,
           FEATUREGROUPFEATURE.FEATURETYPECODE,
           DATALISTCATALOG.UINAME NAME,
           DATALISTCATALOG.DESCRIPTION
    from dbo.DATALISTCATALOG
    inner join dbo.FEATUREGROUPFEATURE on FEATUREGROUPFEATURE.FEATURETYPECODE = 7
        and DATALISTCATALOG.ID = FEATUREGROUPFEATURE.FEATUREID
    where FEATUREGROUPFEATURE.FEATUREGROUPCATALOGID = @FEATUREGROUPCATALOGID and
        (@FEATURETYPECODE is null or @FEATURETYPECODE = 7)

    union all

    select QUERYVIEWCATALOG.ID,
           FEATUREGROUPFEATURE.FEATURETYPE,
           FEATUREGROUPFEATURE.FEATURETYPECODE,
           QUERYVIEWCATALOG.DISPLAYNAME NAME,
           QUERYVIEWCATALOG.DESCRIPTION
    from dbo.QUERYVIEWCATALOG
    inner join dbo.FEATUREGROUPFEATURE on FEATUREGROUPFEATURE.FEATURETYPECODE = 8
        and QUERYVIEWCATALOG.ID = FEATUREGROUPFEATURE.FEATUREID
    where FEATUREGROUPFEATURE.FEATUREGROUPCATALOGID = @FEATUREGROUPCATALOGID and
        (@FEATURETYPECODE is null or @FEATURETYPECODE = 8)

    union all

    select RECORDOPERATIONCATALOG.ID,
           FEATUREGROUPFEATURE.FEATURETYPE,
           FEATUREGROUPFEATURE.FEATURETYPECODE,
           RECORDOPERATIONCATALOG.UINAME NAME,
           RECORDOPERATIONCATALOG.DESCRIPTION
    from dbo.RECORDOPERATIONCATALOG
    inner join dbo.FEATUREGROUPFEATURE on FEATUREGROUPFEATURE.FEATURETYPECODE = 9
        and RECORDOPERATIONCATALOG.ID = FEATUREGROUPFEATURE.FEATUREID
    where FEATUREGROUPFEATURE.FEATUREGROUPCATALOGID = @FEATUREGROUPCATALOGID and
        (@FEATURETYPECODE is null or @FEATURETYPECODE = 9)

    union all

    select REPORTCATALOG.ID,
           FEATUREGROUPFEATURE.FEATURETYPE,
           FEATUREGROUPFEATURE.FEATURETYPECODE,
           REPORTCATALOG.UINAME NAME,
           REPORTCATALOG.DESCRIPTION
    from dbo.REPORTCATALOG
    inner join dbo.FEATUREGROUPFEATURE on FEATUREGROUPFEATURE.FEATURETYPECODE = 10
        and REPORTCATALOG.ID = FEATUREGROUPFEATURE.FEATUREID
    where FEATUREGROUPFEATURE.FEATUREGROUPCATALOGID = @FEATUREGROUPCATALOGID and
        (@FEATURETYPECODE is null or @FEATURETYPECODE = 10)

    union all

    select SEARCHLISTCATALOG.ID,
           FEATUREGROUPFEATURE.FEATURETYPE,
           FEATUREGROUPFEATURE.FEATURETYPECODE,
           SEARCHLISTCATALOG.UINAME NAME,
           SEARCHLISTCATALOG.DESCRIPTION
    from dbo.SEARCHLISTCATALOG
    inner join dbo.FEATUREGROUPFEATURE on FEATUREGROUPFEATURE.FEATURETYPECODE = 11
        and SEARCHLISTCATALOG.ID = FEATUREGROUPFEATURE.FEATUREID
    where FEATUREGROUPFEATURE.FEATUREGROUPCATALOGID = @FEATUREGROUPCATALOGID and
        (@FEATURETYPECODE is null or @FEATURETYPECODE = 11)

    union all

    select SMARTQUERYCATALOG.ID,
           FEATUREGROUPFEATURE.FEATURETYPE,
           FEATUREGROUPFEATURE.FEATURETYPECODE,
           SMARTQUERYCATALOG.UINAME NAME,
           SMARTQUERYCATALOG.DESCRIPTION
    from dbo.SMARTQUERYCATALOG
    inner join dbo.FEATUREGROUPFEATURE on FEATUREGROUPFEATURE.FEATURETYPECODE = 12
        and SMARTQUERYCATALOG.ID = FEATUREGROUPFEATURE.FEATUREID
    where FEATUREGROUPFEATURE.FEATUREGROUPCATALOGID = @FEATUREGROUPCATALOGID and
        (@FEATURETYPECODE is null or @FEATURETYPECODE = 12)

    union all

    select SYSTEMPRIVILEGECATALOG.ID,
           FEATUREGROUPFEATURE.FEATURETYPE,
           FEATUREGROUPFEATURE.FEATURETYPECODE,
           SYSTEMPRIVILEGECATALOG.NAME,
           SYSTEMPRIVILEGECATALOG.DESCRIPTION
    from dbo.SYSTEMPRIVILEGECATALOG
    inner join dbo.FEATUREGROUPFEATURE on FEATUREGROUPFEATURE.FEATURETYPECODE = 13
        and SYSTEMPRIVILEGECATALOG.ID = FEATUREGROUPFEATURE.FEATUREID
    where FEATUREGROUPFEATURE.FEATUREGROUPCATALOGID = @FEATUREGROUPCATALOGID and
        (@FEATURETYPECODE is null or @FEATURETYPECODE = 13)

    union all

    select TASKCATALOG.ID,
           FEATUREGROUPFEATURE.FEATURETYPE,
           FEATUREGROUPFEATURE.FEATURETYPECODE,
           TASKCATALOG.NAME,
           TASKCATALOG.DESCRIPTION
    from dbo.TASKCATALOG
    inner join dbo.FEATUREGROUPFEATURE on FEATUREGROUPFEATURE.FEATURETYPECODE = 14
        and TASKCATALOG.ID = FEATUREGROUPFEATURE.FEATUREID
    where FEATUREGROUPFEATURE.FEATUREGROUPCATALOGID = @FEATUREGROUPCATALOGID and
        (@FEATURETYPECODE is null or @FEATURETYPECODE = 14)

    order by FEATURETYPE, NAME;