USP_DATALIST_CATALOGITEM

CREATE procedure dbo.USP_DATALIST_CATALOGITEM(@RECORDTYPE uniqueidentifier = NULL, 
                                              @ITEMTYPE int = NULL)
as
    set nocount on;    

    if @ITEMTYPE = 0
        -- AddDataFormTemplateSpec

        select DATAFORMTEMPLATECATALOG.ID, DATAFORMTEMPLATECATALOG.TEMPLATENAME AS SPECNAME, DATAFORMTEMPLATECATALOG.TEMPLATESPECXML AS SPEC, @ITEMTYPE AS ITEMTYPE, RECORDTYPE.ID AS RECORDTYPE, RECORDTYPE.NAME AS RECORDTYPENAME
        from dbo.DATAFORMTEMPLATECATALOG
            inner join dbo.RECORDTYPE 
                on DATAFORMTEMPLATECATALOG.RECORDTYPEID = RECORDTYPE.ID
        where (MODE = 2) AND ((@RECORDTYPE IS NULL) OR (RECORDTYPEID = @RECORDTYPE))

    else if (@ITEMTYPE = 1) AND (@RECORDTYPE IS NULL)
        -- CodeTableSpec

        select ID, CODETABLENAME AS SPECNAME, CODETABLESPECXML AS SPEC, @ITEMTYPE AS ITEMTYPE, NULL AS RECORDTYPE, '' AS RECORDTYPENAME
        from dbo.CODETABLECATALOG

    else if @ITEMTYPE = 2
        -- DataListSpec 

        select DATALISTCATALOG.ID, DATALISTCATALOG.NAME AS SPECNAME, DATALISTCATALOG.DATALISTSPEC AS SPEC, @ITEMTYPE AS ITEMTYPE, RECORDTYPE.ID AS RECORDTYPE, ISNULL(RECORDTYPE.NAME, '') AS RECORDTYPENAME
        from dbo.DATALISTCATALOG 
            left join dbo.RECORDTYPE 
                on DATALISTCATALOG.RECORDTYPEID = RECORDTYPE.ID        
        where (@RECORDTYPE IS NULL) OR (RECORDTYPEID = @RECORDTYPE)

    else if @ITEMTYPE = 3 
        -- EditDataFormTemplateSpec

        select DATAFORMTEMPLATECATALOG.ID, DATAFORMTEMPLATECATALOG.TEMPLATENAME AS SPECNAME, DATAFORMTEMPLATECATALOG.TEMPLATESPECXML AS SPEC, @ITEMTYPE AS ITEMTYPE, RECORDTYPE.ID AS RECORDTYPE, RECORDTYPE.NAME AS RECORDTYPENAME
        from dbo.DATAFORMTEMPLATECATALOG 
            left join dbo.RECORDTYPE 
                on DATAFORMTEMPLATECATALOG.RECORDTYPEID = RECORDTYPE.ID
        where (MODE = 1) AND ((@RECORDTYPE IS NULL) OR (RECORDTYPEID = @RECORDTYPE))

    else if (@ITEMTYPE = 4) AND (@RECORDTYPE IS NULL)
        -- FunctionalAreaSpec

        select FUNCTIONALAREACATALOG.ID, FUNCTIONALAREACATALOG.NAME AS SPECNAME, FUNCTIONALAREACATALOG.FUNCTIONALAREASPECXML AS SPEC, @ITEMTYPE AS ITEMTYPE, NULL AS RECORDTYPE, '' AS RECORDTYPENAME
        from dbo.FUNCTIONALAREACATALOG 

    else if (@ITEMTYPE = 5) AND (@RECORDTYPE IS NULL)
        -- PackageSpec

        select ID, PACKAGENAME AS SPECNAME, PACKAGESPECXML AS SPEC, @ITEMTYPE AS ITEMTYPE, NULL AS RECORDTYPE, '' AS RECORDTYPENAME
        from dbo.PACKAGECATALOG 

    else if @ITEMTYPE = 6 
        -- PageDefinitionSpec

        select PAGEDEFINITIONCATALOG.ID, PAGEDEFINITIONCATALOG.NAME AS SPECNAME, PAGEDEFINITIONCATALOG.PAGEDEFINITIONSPEC AS SPEC, @ITEMTYPE AS ITEMTYPE, PAGEDEFINITIONCATALOG.CONTEXTRECORDTYPEID AS RECORDTYPE, RECORDTYPE.NAME AS RECORDTYPENAME
        from dbo.PAGEDEFINITIONCATALOG 
            left join dbo.RECORDTYPE 
                on PAGEDEFINITIONCATALOG.CONTEXTRECORDTYPEID = RECORDTYPE.ID        
        where (@RECORDTYPE IS NULL) OR (CONTEXTRECORDTYPEID = @RECORDTYPE)

    else if @ITEMTYPE = 7 
        -- QueryViewSpec

        select QUERYVIEWCATALOG.ID, QUERYVIEWCATALOG.DISPLAYNAME AS SPECNAME, QUERYVIEWCATALOG.QUERYVIEWSPEC AS SPEC, @ITEMTYPE AS ITEMTYPE, RECORDTYPE.ID AS RECORDTYPE, RECORDTYPE.NAME AS RECORDTYPENAME
        from dbo.QUERYVIEWCATALOG 
            left join dbo.RECORDTYPE 
                on QUERYVIEWCATALOG.RECORDTYPEID = RECORDTYPE.ID
        where (@RECORDTYPE IS NULL) OR (RECORDTYPEID = @RECORDTYPE)

    -- todo:  QueryViewSpecExtensions don't have their own ID

    else if @ITEMTYPE = 8 
        -- QueryViewSpecExtension

        select QUERYVIEWCATALOG.ID, QUERYVIEWCATALOG.DISPLAYNAME AS SPECNAME, QUERYVIEWCATALOG.QUERYVIEWSPECEXTENSION AS SPEC, @ITEMTYPE AS ITEMTYPE, RECORDTYPE.ID AS RECORDTYPE, RECORDTYPE.NAME AS RECORDTYPENAME
        from dbo.QUERYVIEWCATALOG 
            left join dbo.RECORDTYPE 
                on QUERYVIEWCATALOG.RECORDTYPEID = RECORDTYPE.ID
        where (datalength(QUERYVIEWSPECEXTENSION) > 5) AND ((@RECORDTYPE IS NULL) OR (RECORDTYPEID = @RECORDTYPE))

    else if (@ITEMTYPE = 9) AND (@RECORDTYPE IS NULL)
        -- ReportSpec

        select ID, NAME AS SPECNAME, REPORTSPECXML AS SPEC, @ITEMTYPE AS ITEMTYPE, NULL AS RECORDTYPE, '' AS RECORDTYPENAME
        from dbo.REPORTCATALOG 

    else if @ITEMTYPE = 10
        -- SearchListSpec

        select SEARCHLISTCATALOG.ID, SEARCHLISTCATALOG.NAME AS SPECNAME, SEARCHLISTCATALOG.SEARCHLISTSPEC AS SPEC, @ITEMTYPE AS ITEMTYPE, RECORDTYPE.ID AS RECORDTYPE, RECORDTYPE.NAME AS RECORDTYPENAME
        from dbo.SEARCHLISTCATALOG 
            inner join dbo.RECORDTYPE 
                on SEARCHLISTCATALOG.RECORDTYPEID = RECORDTYPE.ID
        where (@RECORDTYPE IS NULL) OR (RECORDTYPEID = @RECORDTYPE)

    else if (@ITEMTYPE = 11) AND (@RECORDTYPE IS NULL)
        -- SimpleDataListSpec

        select ID, NAME AS SPECNAME, SIMPLEDATALISTSPECXML AS SPEC, @ITEMTYPE AS ITEMTYPE, NULL AS RECORDTYPE, '' AS RECORDTYPENAME
        from dbo.SIMPLEDATALISTCATALOG 

    else if @ITEMTYPE = 12
        -- SmartQuerySpec

        select SMARTQUERYCATALOG.ID, SMARTQUERYCATALOG.NAME AS SPECNAME, SMARTQUERYCATALOG.SMARTQUERYSPEC AS SPEC, @ITEMTYPE AS ITEMTYPE, RECORDTYPE.ID AS RECORDTYPE, RECORDTYPE.NAME AS RECORDTYPENAME
        from dbo.SMARTQUERYCATALOG 
            inner join dbo.RECORDTYPE 
                on SMARTQUERYCATALOG.RECORDTYPEID = RECORDTYPE.ID
        where (@RECORDTYPE IS NULL) OR (RECORDTYPEID = @RECORDTYPE)

    else if (@ITEMTYPE = 13) AND (@RECORDTYPE IS NULL)
        -- SQLFunctionSpec

        select ID, FUNCTIONNAME AS SPECNAME, SQLFUNCTIONSPECXML AS SPEC, @ITEMTYPE AS ITEMTYPE, NULL AS RECORDTYPE, '' AS RECORDTYPENAME
        from dbo.SQLFUNCTIONCATALOG 

    else if (@ITEMTYPE = 14) AND (@RECORDTYPE IS NULL)
        -- SQLStoredProcedureSpec

        select ID, PROCEDURENAME AS SPECNAME, SQLPROCEDURESPECXML AS SPEC, @ITEMTYPE AS ITEMTYPE, NULL AS RECORDTYPE, '' AS RECORDTYPENAME
        from dbo.SQLPROCEDURECATALOG 

    else if (@ITEMTYPE = 15) AND (@RECORDTYPE IS NULL)
        -- TableSpec

        select ID, TABLENAME AS SPECNAME, TABLESPECXML AS SPEC, @ITEMTYPE AS ITEMTYPE, NULL AS RECORDTYPE, '' AS RECORDTYPENAME
        from dbo.TABLECATALOG 

    -- TableSpecExtensions don't have their own ID

    else if @ITEMTYPE = 16 
        select ID, TABLENAME AS SPECNAME, TABLESPECEXTENSIONXML AS SPEC, @ITEMTYPE AS ITEMTYPE, NULL AS RECORDTYPE, '' AS RECORDTYPENAME
        from dbo.TABLECATALOG 
        where datalength(TABLESPECEXTENSIONXML) > 5

    else if (@ITEMTYPE = 17) AND (@RECORDTYPE IS NULL)
        select ID, NAME AS SPECNAME, TASKSPECXML AS SPEC, @ITEMTYPE AS ITEMTYPE, NULL AS RECORDTYPE, '' AS RECORDTYPENAME
        from dbo.TASKCATALOG 

    else if @ITEMTYPE = 18 
        -- TranslationFunctionSpec

        select TRANSLATIONFUNCTIONCATALOG.ID, TRANSLATIONFUNCTIONCATALOG.NAME AS SPECNAME, TRANSLATIONFUNCTIONCATALOG.TRANSLATIONFUNCTIONSPEC AS SPEC, @ITEMTYPE AS ITEMTYPE, RECORDTYPE.ID AS RECORDTYPE, RECORDTYPE.NAME AS RECORDTYPENAME
        from dbo.TRANSLATIONFUNCTIONCATALOG
            left join dbo.RECORDTYPE 
                on TRANSLATIONFUNCTIONCATALOG.RECORDTYPEID = RECORDTYPE.ID
        where (@RECORDTYPE IS NULL) OR (RECORDTYPEID = @RECORDTYPE)

    else if @ITEMTYPE = 19 
        -- ViewDataFormTemplateSpec

        select DATAFORMTEMPLATECATALOG.ID, DATAFORMTEMPLATECATALOG.TEMPLATENAME AS SPECNAME, DATAFORMTEMPLATECATALOG.TEMPLATESPECXML AS SPEC, @ITEMTYPE AS ITEMTYPE, RECORDTYPE.ID AS RECORDTYPE, RECORDTYPE.NAME AS RECORDTYPENAME
        from dbo.DATAFORMTEMPLATECATALOG 
            left join dbo.RECORDTYPE 
                on DATAFORMTEMPLATECATALOG.RECORDTYPEID = RECORDTYPE.ID
        where (MODE = 0) AND ((@RECORDTYPE IS NULL) OR (RECORDTYPEID = @RECORDTYPE))

    else if (@ITEMTYPE = 20) AND (@RECORDTYPE IS NULL)
        -- BatchTypeSpec

        select ID, NAME AS SPECNAME, SPECXML AS SPEC, @ITEMTYPE AS ITEMTYPE, NULL AS RECORDTYPE, '' AS RECORDTYPENAME
        from dbo.BATCHTYPECATALOG 

    else if @ITEMTYPE = 21
        -- IDMapperSpec

        select IDMAPPERCATALOG.ID, IDMAPPERCATALOG.NAME AS SPECNAME, IDMAPPERCATALOG.IDMAPPERSPECXML AS SPEC, @ITEMTYPE AS ITEMTYPE, SOURCERECORDTYPEID AS RECORDTYPE, RECORDTYPE.NAME AS RECORDTYPENAME
        from dbo.IDMAPPERCATALOG
            left join dbo.RECORDTYPE 
                on IDMAPPERCATALOG.SOURCERECORDTYPEID = RECORDTYPE.ID        
        where (@RECORDTYPE IS NULL) OR (SOURCERECORDTYPEID = @RECORDTYPE)

    else if @ITEMTYPE = 22
        -- BusinessProcessSpec

        select BUSINESSPROCESSCATALOG.ID, BUSINESSPROCESSCATALOG.NAME AS SPECNAME, BUSINESSPROCESSCATALOG.BUSINESSPROCESSSPECXML AS SPEC, @ITEMTYPE AS ITEMTYPE, RECORDTYPE.ID AS RECORDTYPE, RECORDTYPE.NAME AS RECORDTYPENAME
        from dbo.BUSINESSPROCESSCATALOG 
            inner join dbo.RECORDTYPE 
                on BUSINESSPROCESSCATALOG.RECORDTYPEID = RECORDTYPE.ID    
        where (@RECORDTYPE IS NULL) OR (RECORDTYPEID = @RECORDTYPE)

    else if @ITEMTYPE = 23
        -- RecordOperationSpec

        select RECORDOPERATIONCATALOG.ID, RECORDOPERATIONCATALOG.DISPLAYNAME AS SPECNAME, RECORDOPERATIONCATALOG.RECORDOPERATIONSPECXML AS SPEC, @ITEMTYPE AS ITEMTYPE, RECORDTYPE.ID AS RECORDTYPE, RECORDTYPE.NAME AS RECORDTYPENAME
        from dbo.RECORDOPERATIONCATALOG 
            left join dbo.RECORDTYPE 
                on RECORDOPERATIONCATALOG.RECORDTYPEID = RECORDTYPE.ID
        where (@RECORDTYPE IS NULL) OR (RECORDTYPEID = @RECORDTYPE)

    else if @ITEMTYPE = 24
        select DASHBOARDCATALOG.ID, DASHBOARDCATALOG.NAME AS SPECNAME, DASHBOARDCATALOG.SPECXML AS SPEC, @ITEMTYPE AS ITEMTYPE, CONTEXTRECORDTYPEID AS RECORDTYPE, RECORDTYPE.NAME AS RECORDTYPENAME
        from dbo.DASHBOARDCATALOG 
            left join dbo.RECORDTYPE 
                on DASHBOARDCATALOG.CONTEXTRECORDTYPEID = RECORDTYPE.ID
        where (@RECORDTYPE IS NULL) OR (CONTEXTRECORDTYPEID = @RECORDTYPE)        

    else if @ITEMTYPE = 26
        -- MergeTaskSpec

        select MERGETASKCATALOG.ID, MERGETASKCATALOG.NAME AS SPECNAME, MERGETASKCATALOG.SPECXML AS SPEC, @ITEMTYPE AS ITEMTYPE, RECORDTYPE.ID AS RECORDTYPE, RECORDTYPE.NAME AS RECORDTYPENAME
        from dbo.MERGETASKCATALOG 
            inner join dbo.RECORDTYPE 
                on MERGETASKCATALOG.RECORDTYPEID = RECORDTYPE.ID
        where (@RECORDTYPE IS NULL) OR (RECORDTYPEID = @RECORDTYPE)    

    else if @ITEMTYPE IS NULL
        -- All the above items with unions in between

        -- AddDataFormTemplateSpec

        select DATAFORMTEMPLATECATALOG.ID, DATAFORMTEMPLATECATALOG.TEMPLATENAME AS SPECNAME, DATAFORMTEMPLATECATALOG.TEMPLATESPECXML AS SPEC, 0 AS ITEMTYPE, RECORDTYPE.ID AS RECORDTYPE, RECORDTYPE.NAME AS RECORDTYPENAME
        from dbo.DATAFORMTEMPLATECATALOG
            inner join dbo.RECORDTYPE 
                on DATAFORMTEMPLATECATALOG.RECORDTYPEID = RECORDTYPE.ID
        where (MODE = 2) AND ((@RECORDTYPE IS NULL) OR (RECORDTYPEID = @RECORDTYPE))

        union all
        -- CodeTableSpec

        select ID, CODETABLENAME AS SPECNAME, CODETABLESPECXML AS SPEC, 1 AS ITEMTYPE, NULL AS RECORDTYPE, '' AS RECORDTYPENAME
        from dbo.CODETABLECATALOG
        where (@RECORDTYPE IS NULL)

        union all
        -- DataListSpec 

        select DATALISTCATALOG.ID, DATALISTCATALOG.NAME AS SPECNAME, DATALISTCATALOG.DATALISTSPEC AS SPEC, 2 AS ITEMTYPE, RECORDTYPE.ID AS RECORDTYPE, ISNULL(RECORDTYPE.NAME, '') AS RECORDTYPENAME
        from dbo.DATALISTCATALOG 
            left join dbo.RECORDTYPE 
                on DATALISTCATALOG.RECORDTYPEID = RECORDTYPE.ID        
        where (@RECORDTYPE IS NULL) OR (RECORDTYPEID = @RECORDTYPE)

        union all
        -- EditDataFormTemplateSpec

        select DATAFORMTEMPLATECATALOG.ID, DATAFORMTEMPLATECATALOG.TEMPLATENAME AS SPECNAME, DATAFORMTEMPLATECATALOG.TEMPLATESPECXML AS SPEC, 3 AS ITEMTYPE, RECORDTYPE.ID AS RECORDTYPE, RECORDTYPE.NAME AS RECORDTYPENAME
        from dbo.DATAFORMTEMPLATECATALOG 
            left join dbo.RECORDTYPE 
                on DATAFORMTEMPLATECATALOG.RECORDTYPEID = RECORDTYPE.ID
        where (MODE = 1) AND ((@RECORDTYPE IS NULL) OR (RECORDTYPEID = @RECORDTYPE))

        union all
        -- FunctionalAreaSpec

        select FUNCTIONALAREACATALOG.ID, FUNCTIONALAREACATALOG.NAME AS SPECNAME, FUNCTIONALAREACATALOG.FUNCTIONALAREASPECXML AS SPEC, 4 AS ITEMTYPE, NULL AS RECORDTYPE, '' AS RECORDTYPENAME
        from dbo.FUNCTIONALAREACATALOG 
        where (@RECORDTYPE IS NULL)

        union all
        -- PackageSpec

        select ID, PACKAGENAME AS SPECNAME, PACKAGESPECXML AS SPEC, 5 AS ITEMTYPE, NULL AS RECORDTYPE, '' AS RECORDTYPENAME
        from dbo.PACKAGECATALOG 
        where (@RECORDTYPE IS NULL)

        union all
        -- PageDefinitionSpec

        select PAGEDEFINITIONCATALOG.ID, PAGEDEFINITIONCATALOG.NAME AS SPECNAME, PAGEDEFINITIONCATALOG.PAGEDEFINITIONSPEC AS SPEC, 6 AS ITEMTYPE, PAGEDEFINITIONCATALOG.CONTEXTRECORDTYPEID AS RECORDTYPE, RECORDTYPE.NAME AS RECORDTYPENAME
        from dbo.PAGEDEFINITIONCATALOG 
            left join dbo.RECORDTYPE 
                on PAGEDEFINITIONCATALOG.CONTEXTRECORDTYPEID = RECORDTYPE.ID        
        where (@RECORDTYPE IS NULL) OR (CONTEXTRECORDTYPEID = @RECORDTYPE)

        union all
        -- QueryViewSpec

        select QUERYVIEWCATALOG.ID, QUERYVIEWCATALOG.DISPLAYNAME AS SPECNAME, QUERYVIEWCATALOG.QUERYVIEWSPEC AS SPEC, 7 AS ITEMTYPE, RECORDTYPE.ID AS RECORDTYPE, RECORDTYPE.NAME AS RECORDTYPENAME
        from dbo.QUERYVIEWCATALOG 
            left join dbo.RECORDTYPE 
                on QUERYVIEWCATALOG.RECORDTYPEID = RECORDTYPE.ID
        where (@RECORDTYPE IS NULL) OR (RECORDTYPEID = @RECORDTYPE)

    -- todo:  QueryViewSpecExtensions don't have their own ID

        union all
        -- QueryViewSpecExtension

        select QUERYVIEWCATALOG.ID, QUERYVIEWCATALOG.DISPLAYNAME AS SPECNAME, QUERYVIEWCATALOG.QUERYVIEWSPECEXTENSION AS SPEC, 8 AS ITEMTYPE, RECORDTYPE.ID AS RECORDTYPE, RECORDTYPE.NAME AS RECORDTYPENAME
        from dbo.QUERYVIEWCATALOG 
            left join dbo.RECORDTYPE 
                on QUERYVIEWCATALOG.RECORDTYPEID = RECORDTYPE.ID
        where (datalength(QUERYVIEWSPECEXTENSION) > 5) AND ((@RECORDTYPE IS NULL) OR (RECORDTYPEID = @RECORDTYPE))

        union all
        -- ReportSpec

        select ID, NAME AS SPECNAME, REPORTSPECXML AS SPEC, 9 AS ITEMTYPE, NULL AS RECORDTYPE, '' AS RECORDTYPENAME
        from dbo.REPORTCATALOG 
        where (@RECORDTYPE IS NULL)

        union all
        -- SearchListSpec

        select SEARCHLISTCATALOG.ID, SEARCHLISTCATALOG.NAME AS SPECNAME, SEARCHLISTCATALOG.SEARCHLISTSPEC AS SPEC, 10 AS ITEMTYPE, RECORDTYPE.ID AS RECORDTYPE, RECORDTYPE.NAME AS RECORDTYPENAME
        from dbo.SEARCHLISTCATALOG 
            inner join dbo.RECORDTYPE 
                on SEARCHLISTCATALOG.RECORDTYPEID = RECORDTYPE.ID
        where (@RECORDTYPE IS NULL) OR (RECORDTYPEID = @RECORDTYPE)

        union all
        -- SimpleDataListSpec

        select ID, NAME AS SPECNAME, SIMPLEDATALISTSPECXML AS SPEC, 11 AS ITEMTYPE, NULL AS RECORDTYPE, '' AS RECORDTYPENAME
        from dbo.SIMPLEDATALISTCATALOG 
        where (@RECORDTYPE IS NULL)

        union all
        -- SmartQuerySpec

        select SMARTQUERYCATALOG.ID, SMARTQUERYCATALOG.NAME AS SPECNAME, SMARTQUERYCATALOG.SMARTQUERYSPEC AS SPEC, 12 AS ITEMTYPE, RECORDTYPE.ID AS RECORDTYPE, RECORDTYPE.NAME AS RECORDTYPENAME
        from dbo.SMARTQUERYCATALOG 
            inner join dbo.RECORDTYPE 
                on SMARTQUERYCATALOG.RECORDTYPEID = RECORDTYPE.ID
        where (@RECORDTYPE IS NULL) OR (RECORDTYPEID = @RECORDTYPE)

        union all
        -- SQLFunctionSpec

        select ID, FUNCTIONNAME AS SPECNAME, SQLFUNCTIONSPECXML AS SPEC, 13 AS ITEMTYPE, NULL AS RECORDTYPE, '' AS RECORDTYPENAME
        from dbo.SQLFUNCTIONCATALOG 
        where (@RECORDTYPE IS NULL)

        union all
        -- SQLStoredProcedureSpec

        select ID, PROCEDURENAME AS SPECNAME, SQLPROCEDURESPECXML AS SPEC, 14 AS ITEMTYPE, NULL AS RECORDTYPE, '' AS RECORDTYPENAME
        from dbo.SQLPROCEDURECATALOG 
        where (@RECORDTYPE IS NULL)

        union all
        -- TableSpec

        select ID, TABLENAME AS SPECNAME, TABLESPECXML AS SPEC, 15 AS ITEMTYPE, NULL AS RECORDTYPE, '' AS RECORDTYPENAME
        from dbo.TABLECATALOG 
        where (@RECORDTYPE IS NULL)

    -- TableSpecExtensions don't have their own ID

        union all
        select ID, TABLENAME AS SPECNAME, TABLESPECEXTENSIONXML AS SPEC, 16 AS ITEMTYPE, NULL AS RECORDTYPE, '' AS RECORDTYPENAME
        from dbo.TABLECATALOG 
        where datalength(TABLESPECEXTENSIONXML) > 5

        union all
        select ID, NAME AS SPECNAME, TASKSPECXML AS SPEC, 17 AS ITEMTYPE, NULL AS RECORDTYPE, '' AS RECORDTYPENAME
        from dbo.TASKCATALOG 
        where (@RECORDTYPE IS NULL)

        union all
        -- TranslationFunctionSpec

        select TRANSLATIONFUNCTIONCATALOG.ID, TRANSLATIONFUNCTIONCATALOG.NAME AS SPECNAME, TRANSLATIONFUNCTIONCATALOG.TRANSLATIONFUNCTIONSPEC AS SPEC, 18 AS ITEMTYPE, RECORDTYPE.ID AS RECORDTYPE, RECORDTYPE.NAME AS RECORDTYPENAME
        from dbo.TRANSLATIONFUNCTIONCATALOG
            left join dbo.RECORDTYPE 
                on TRANSLATIONFUNCTIONCATALOG.RECORDTYPEID = RECORDTYPE.ID
        where (@RECORDTYPE IS NULL) OR (RECORDTYPEID = @RECORDTYPE)

        union all
        -- ViewDataFormTemplateSpec

        select DATAFORMTEMPLATECATALOG.ID, DATAFORMTEMPLATECATALOG.TEMPLATENAME AS SPECNAME, DATAFORMTEMPLATECATALOG.TEMPLATESPECXML AS SPEC, 19 AS ITEMTYPE, RECORDTYPE.ID AS RECORDTYPE, RECORDTYPE.NAME AS RECORDTYPENAME
        from dbo.DATAFORMTEMPLATECATALOG 
            left join dbo.RECORDTYPE 
                on DATAFORMTEMPLATECATALOG.RECORDTYPEID = RECORDTYPE.ID
        where (MODE = 0) AND ((@RECORDTYPE IS NULL) OR (RECORDTYPEID = @RECORDTYPE))

        union all
        -- BatchTypeSpec

        select ID, NAME AS SPECNAME, SPECXML AS SPEC, 20 AS ITEMTYPE, NULL AS RECORDTYPE, '' AS RECORDTYPENAME
        from dbo.BATCHTYPECATALOG 
        where (@RECORDTYPE IS NULL)

        union all
        -- IDMapperSpec

        select IDMAPPERCATALOG.ID, IDMAPPERCATALOG.NAME AS SPECNAME, IDMAPPERCATALOG.IDMAPPERSPECXML AS SPEC, 21 AS ITEMTYPE, SOURCERECORDTYPEID AS RECORDTYPE, RECORDTYPE.NAME AS RECORDTYPENAME
        from dbo.IDMAPPERCATALOG
            left join dbo.RECORDTYPE 
                on IDMAPPERCATALOG.SOURCERECORDTYPEID = RECORDTYPE.ID        
        where (@RECORDTYPE IS NULL) OR (SOURCERECORDTYPEID = @RECORDTYPE)

        union all
        -- BusinessProcessSpec

        select BUSINESSPROCESSCATALOG.ID, BUSINESSPROCESSCATALOG.NAME AS SPECNAME, BUSINESSPROCESSCATALOG.BUSINESSPROCESSSPECXML AS SPEC, 22 AS ITEMTYPE, RECORDTYPE.ID AS RECORDTYPE, RECORDTYPE.NAME AS RECORDTYPENAME
        from dbo.BUSINESSPROCESSCATALOG 
            inner join dbo.RECORDTYPE 
                on BUSINESSPROCESSCATALOG.RECORDTYPEID = RECORDTYPE.ID    
        where (@RECORDTYPE IS NULL) OR (RECORDTYPEID = @RECORDTYPE)

        union all
        -- RecordOperationSpec

        select RECORDOPERATIONCATALOG.ID, RECORDOPERATIONCATALOG.DISPLAYNAME AS SPECNAME, RECORDOPERATIONCATALOG.RECORDOPERATIONSPECXML AS SPEC, 23 AS ITEMTYPE, RECORDTYPE.ID AS RECORDTYPE, RECORDTYPE.NAME AS RECORDTYPENAME
        from dbo.RECORDOPERATIONCATALOG 
            left join dbo.RECORDTYPE 
                on RECORDOPERATIONCATALOG.RECORDTYPEID = RECORDTYPE.ID
        where (@RECORDTYPE IS NULL) OR (RECORDTYPEID = @RECORDTYPE)

        union all
        select DASHBOARDCATALOG.ID, DASHBOARDCATALOG.NAME AS SPECNAME, DASHBOARDCATALOG.SPECXML AS SPEC, 24 AS ITEMTYPE, CONTEXTRECORDTYPEID AS RECORDTYPE, RECORDTYPE.NAME AS RECORDTYPENAME
        from dbo.DASHBOARDCATALOG 
            left join dbo.RECORDTYPE 
                on DASHBOARDCATALOG.CONTEXTRECORDTYPEID = RECORDTYPE.ID
        where (@RECORDTYPE IS NULL) OR (CONTEXTRECORDTYPEID = @RECORDTYPE)        

        union all
        -- MergeTaskSpec

        select MERGETASKCATALOG.ID, MERGETASKCATALOG.NAME AS SPECNAME, MERGETASKCATALOG.SPECXML AS SPEC, 26 AS ITEMTYPE, RECORDTYPE.ID AS RECORDTYPE, RECORDTYPE.NAME AS RECORDTYPENAME
        from dbo.MERGETASKCATALOG 
            inner join dbo.RECORDTYPE 
                on MERGETASKCATALOG.RECORDTYPEID = RECORDTYPE.ID
        where (@RECORDTYPE IS NULL) OR (RECORDTYPEID = @RECORDTYPE)