UFN_GETSPECBYNAME

create function dbo.UFN_GETSPECBYNAME(@NAME nvarchar(200), @CatalogItemType int) returns xml
as
begin
    declare @xml xml

    if @CatalogItemType = 0
        select @xml = TEMPLATESPECXML from dbo.DATAFORMTEMPLATECATALOG where TEMPLATENAME = @NAME            -- AddDataFormTemplateSpec
    else if @CatalogItemType = 1 
        select @xml = CODETABLESPECXML from dbo.CODETABLECATALOG where CODETABLENAME = @NAME                    -- CodeTableSpec
    else if @CatalogItemType = 2 
        select @xml = DATALISTSPEC from dbo.DATALISTCATALOG where [NAME] = @NAME                        -- DataListSpec 
    else if @CatalogItemType = 3 
        select @xml = TEMPLATESPECXML from dbo.DATAFORMTEMPLATECATALOG where TEMPLATENAME = @NAME            -- EditDataFormTemplateSpec
    else if @CatalogItemType = 4
        select @xml = FUNCTIONALAREASPECXML from dbo.FUNCTIONALAREACATALOG where [NAME] = @NAME        -- FunctionalAreaSpec
    else if @CatalogItemType = 5 
        select @xml = PACKAGESPECXML from dbo.PACKAGECATALOG where PACKAGENAME = @NAME                        -- PackageSpec
    else if @CatalogItemType = 6 
        select @xml = PAGEDEFINITIONSPEC from dbo.PAGEDEFINITIONCATALOG where [NAME] = @NAME            -- PageDefinitionSpec
    else if @CatalogItemType = 7 
        select @xml = QUERYVIEWSPEC from dbo.QUERYVIEWCATALOG where OBJECTNAME = @NAME                    -- QueryViewSpec

    -- todo:  QueryViewSpecExtensions don't have their own ID
    else if @CatalogItemType = 8 
        select @xml = QUERYVIEWSPECEXTENSION from dbo.QUERYVIEWCATALOG where OBJECTNAME = @NAME            -- QueryViewSpecExtension

    else if @CatalogItemType = 9                                        
        select @xml = REPORTSPECXML from dbo.REPORTCATALOG where [NAME] = @NAME                        -- ReportSpec
    else if @CatalogItemType = 10
        select @xml = SEARCHLISTSPEC from dbo.SEARCHLISTCATALOG where [NAME] = @NAME                    -- SearchListSpec
    else if @CatalogItemType = 11
        select @xml = SIMPLEDATALISTSPECXML from dbo.SIMPLEDATALISTCATALOG where [NAME] = @NAME        -- SimpleDataListSpec
    else if @CatalogItemType = 12
        select @xml = SMARTQUERYSPEC from dbo.SMARTQUERYCATALOG where [NAME] = @NAME                    -- SmartQuerySpec
    else if @CatalogItemType = 13
        select @xml = SQLFUNCTIONSPECXML from dbo.SQLFUNCTIONCATALOG where FUNCTIONNAME = @NAME                -- SQLFunctionSpec
    else if @CatalogItemType = 14
        select @xml = SQLPROCEDURESPECXML from dbo.SQLPROCEDURECATALOG where PROCEDURENAME = @NAME            -- SQLStoredProcedureSpec
    else if @CatalogItemType = 15
        select @xml = TABLESPECXML from dbo.TABLECATALOG where TABLENAME = @NAME                            -- TableSpec

    -- TableSpecExtensions don't have their own ID
    else if @CatalogItemType = 16 
        select @xml = TABLESPECEXTENSIONXML from dbo.TABLECATALOG where TABLENAME = @NAME

    else if @CatalogItemType = 17
        select @xml = TASKSPECXML from dbo.TASKCATALOG where [NAME] = @NAME                            -- TaskSpec
    else if @CatalogItemType = 18 
        select @xml = TRANSLATIONFUNCTIONSPEC from dbo.TRANSLATIONFUNCTIONCATALOG where [NAME] = @NAME-- TranslationFunctionSpec
    else if @CatalogItemType = 19 
        select @xml = TEMPLATESPECXML from dbo.DATAFORMTEMPLATECATALOG where TEMPLATENAME = @NAME            -- ViewDataFormTemplateSpec
    else if @CatalogItemType = 20
        select @xml = SPECXML from dbo.BATCHTYPECATALOG where [NAME] = @NAME                            -- BatchTypeSpec
    else if @CatalogItemType = 21
        select @xml = IDMAPPERSPECXML from dbo.IDMAPPERCATALOG where [NAME] = @NAME                    -- IDMapperSpec
    else if @CatalogItemType = 22
        select @xml = BUSINESSPROCESSSPECXML from dbo.BUSINESSPROCESSCATALOG where [NAME] = @NAME        -- BusinessProcessSpec
    else if @CatalogItemType = 23
        select @xml = RECORDOPERATIONSPECXML from dbo.RECORDOPERATIONCATALOG where PROCEDURENAME = @NAME        -- RecordOperationSpec

    else if @CatalogItemType = 24
        select @xml = SPECXML from dbo.DASHBOARDCATALOG where [NAME] = @NAME        

    else if @CatalogItemType = 26
        select @xml = SPECXML from dbo.MERGETASKCATALOG where [NAME] = @NAME                            -- MergeTaskSpec


    else
        begin
            return null
        end

    return @xml
end