USP_DATALIST_NIMBUSDATAFORMRESOURCE

CREATE procedure dbo.USP_DATALIST_NIMBUSDATAFORMRESOURCE(@RECORDTYPEID uniqueidentifier = null)
as
    with xmlnamespaces('bb_appfx_viewdataformtemplate' as v,
        'bb_appfx_editdataformtemplate' as e,
        'bb_appfx_adddataformtemplate' as a)
    select DATAFORMINSTANCECATALOG.ID,
        DATAFORMINSTANCECATALOG.FORMNAME NAME,
        DATAFORMINSTANCECATALOG.DESCRIPTION,
        case DATAFORMTEMPLATECATALOG.MODE
            when 0 then    DATAFORMTEMPLATECATALOG.TEMPLATESPECXML.value('v:ViewDataFormTemplateSpec[1]/@Author', 'nvarchar(4000)')
            when 1 then    DATAFORMTEMPLATECATALOG.TEMPLATESPECXML.value('e:EditDataFormTemplateSpec[1]/@Author', 'nvarchar(4000)')
            when 2 then DATAFORMTEMPLATECATALOG.TEMPLATESPECXML.value('a:AddDataFormTemplateSpec[1]/@Author', 'nvarchar(4000)')
            else null
        end AUTHOR,
        DATAFORMINSTANCECATALOG.DATEADDED,
        DATAFORMINSTANCECATALOG.DATECHANGED,
        DATAFORMTEMPLATECATALOG.SECURITYUIFOLDER,
        DATAFORMTEMPLATECATALOG.MODENAME
    from dbo.DATAFORMINSTANCECATALOG
    left join dbo.DATAFORMTEMPLATECATALOG on DATAFORMINSTANCECATALOG.DATAFORMTEMPLATECATALOGID = DATAFORMTEMPLATECATALOG.ID
    where (@RECORDTYPEID is null and DATAFORMTEMPLATECATALOG.RECORDTYPEID is null) or DATAFORMTEMPLATECATALOG.RECORDTYPEID = @RECORDTYPEID
    order by DATAFORMINSTANCECATALOG.FORMNAME;