USP_DATALIST_CATALOGSINGLEITEM

CREATE procedure dbo.USP_DATALIST_CATALOGSINGLEITEM(@SPECID uniqueidentifier, 
                                              @ITEMTYPE int)
as
    set nocount on;    

    /* 
  pdg 8.18.2009
  fix for bbmetalweb.exe code generator.
  make joins to record type a left join to return forms etc. with no record type.
  */

    if @ITEMTYPE = 0
        -- AddDataFormTemplateSpec

        select DATAFORMTEMPLATECATALOG.ID, DATAFORMTEMPLATECATALOG.TEMPLATENAME AS SPECNAME, DATAFORMTEMPLATECATALOG.TEMPLATESPECXML AS SPEC, @ITEMTYPE AS ITEMTYPE, RECORDTYPE.ID AS RECORDTYPE, COALESCE(RECORDTYPE.NAME,'') AS RECORDTYPENAME
        from dbo.DATAFORMTEMPLATECATALOG
            left join dbo.RECORDTYPE 
                on DATAFORMTEMPLATECATALOG.RECORDTYPEID = RECORDTYPE.ID
        where (MODE = 2) AND DATAFORMTEMPLATECATALOG.ID=@SPECID

    else if (@ITEMTYPE = 1)
        -- CodeTableSpec

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

    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 DATALISTCATALOG.ID=@SPECID

    else if @ITEMTYPE = 3 
        -- EditDataFormTemplateSpec

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

    else if (@ITEMTYPE = 4)
        -- FunctionalAreaSpec

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

    else if (@ITEMTYPE = 5)
        -- PackageSpec

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

    else if @ITEMTYPE = 6 
        -- PageDefinitionSpec

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

    else if @ITEMTYPE = 7 
        -- QueryViewSpec

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

    else if @ITEMTYPE = 8 
        -- QueryViewSpecExtension

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

    else if (@ITEMTYPE = 9)
        -- ReportSpec

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

    else if @ITEMTYPE = 10
        -- SearchListSpec

        select SEARCHLISTCATALOG.ID, SEARCHLISTCATALOG.NAME AS SPECNAME, SEARCHLISTCATALOG.SEARCHLISTSPEC AS SPEC, @ITEMTYPE AS ITEMTYPE, RECORDTYPE.ID AS RECORDTYPE, COALESCE(RECORDTYPE.NAME,'') AS RECORDTYPENAME
        from dbo.SEARCHLISTCATALOG 
            left join dbo.RECORDTYPE 
                on SEARCHLISTCATALOG.RECORDTYPEID = RECORDTYPE.ID
        where SEARCHLISTCATALOG.ID=@SPECID

    else if (@ITEMTYPE = 11)
        -- SimpleDataListSpec

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

    else if @ITEMTYPE = 12
        -- SmartQuerySpec

        select SMARTQUERYCATALOG.ID, SMARTQUERYCATALOG.NAME AS SPECNAME, SMARTQUERYCATALOG.SMARTQUERYSPEC AS SPEC, @ITEMTYPE AS ITEMTYPE, RECORDTYPE.ID AS RECORDTYPE, COALESCE(RECORDTYPE.NAME,'') AS RECORDTYPENAME
        from dbo.SMARTQUERYCATALOG 
            left join dbo.RECORDTYPE 
                on SMARTQUERYCATALOG.RECORDTYPEID = RECORDTYPE.ID
        where SMARTQUERYCATALOG.ID=@SPECID

    else if (@ITEMTYPE = 13)
        -- SQLFunctionSpec

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

    else if (@ITEMTYPE = 14)
        -- SQLStoredProcedureSpec

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

    else if (@ITEMTYPE = 15)
        -- TableSpec

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

    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 AND TABLECATALOG.ID=@SPECID

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

    else if @ITEMTYPE = 18 
        -- TranslationFunctionSpec

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

    else if @ITEMTYPE = 19 
        -- ViewDataFormTemplateSpec

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

    else if (@ITEMTYPE = 20)
        -- BatchTypeSpec

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

    else if @ITEMTYPE = 21
        -- IDMapperSpec

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

    else if @ITEMTYPE = 22
        -- BusinessProcessSpec

        select BUSINESSPROCESSCATALOG.ID, BUSINESSPROCESSCATALOG.NAME AS SPECNAME, BUSINESSPROCESSCATALOG.BUSINESSPROCESSSPECXML AS SPEC, @ITEMTYPE AS ITEMTYPE, RECORDTYPE.ID AS RECORDTYPE, COALESCE(RECORDTYPE.NAME,'') AS RECORDTYPENAME
        from dbo.BUSINESSPROCESSCATALOG 
            left join dbo.RECORDTYPE 
                on BUSINESSPROCESSCATALOG.RECORDTYPEID = RECORDTYPE.ID    
        where BUSINESSPROCESSCATALOG.ID=@SPECID

    else if @ITEMTYPE = 23
        -- RecordOperationSpec

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

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

    else if @ITEMTYPE = 26
        -- MergeTaskSpec

        select MERGETASKCATALOG.ID, MERGETASKCATALOG.NAME AS SPECNAME, MERGETASKCATALOG.SPECXML AS SPEC, @ITEMTYPE AS ITEMTYPE, RECORDTYPE.ID AS RECORDTYPE, COALESCE(RECORDTYPE.NAME,'') AS RECORDTYPENAME
        from dbo.MERGETASKCATALOG 
            left join dbo.RECORDTYPE 
                on MERGETASKCATALOG.RECORDTYPEID = RECORDTYPE.ID
        where MERGETASKCATALOG.ID=@SPECID