USP_DATALIST_SMARTQUERYPARAMETERS

CREATE procedure dbo.USP_DATALIST_SMARTQUERYPARAMETERS
(
    @SMARTQUERYID uniqueidentifier
)
as
    set nocount on;

    with xmlnamespaces ('bb_appfx_smartquery' as tns, 'bb_appfx_commontypes' as common)
    select 
        smartquery.fields.value('@FieldID', 'nvarchar(max)') as FIELDID,
        smartquery.fields.value('@Caption', 'nvarchar(max)') as CAPTION,
        coalesce(smartquery.fields.value('@DataType', 'nvarchar(18)'), 'String') as DATATYPE,
        case
            when smartquery.fields.exist('common:CodeTable') = 1 then 
                'Code Table (' 
                + (select UINAME from dbo.CODETABLECATALOG where DBTABLENAME = smartquery.fields.value('(common:CodeTable/@CodeTableName)[1]', 'nvarchar(100)'))
                + ')'
            when smartquery.fields.exist('common:ValueList') = 1  then 'Value List'
            when smartquery.fields.exist('common:SimpleDataList') = 1  then 
                'Simple Data List ('
                + (select UINAME from dbo.SIMPLEDATALISTCATALOG where ID = smartquery.fields.value('(common:SimpleDataList/@SimpleDataListID)[1]', 'nvarchar(60)'))
                + ')'
            when smartquery.fields.exist('common:Collection') = 1  then 'Collection'
            when smartquery.fields.exist('common:SearchList') = 1  then
                'Search List ('
                + (select UINAME from dbo.SEARCHLISTCATALOG where ID = smartquery.fields.value('(common:SearchList/@SearchListID)[1]', 'nvarchar(60)'))
                + ')'
            when smartquery.fields.exist('common:Link') = 1  then 'Link'
            when smartquery.fields.exist('common:File') = 1  then 'File'
            when smartquery.fields.exist('common:CreditCard') = 1  then 'Credit Card'
            when smartquery.fields.exist('common:Html') = 1  then 'Html'
            else ''
        end as DESCRIPTOR,
        case
            when smartquery.fields.exist('common:CodeTable') = 1 then 1 
            when smartquery.fields.exist('common:ValueList') = 1 then 2
            when smartquery.fields.exist('common:SimpleDataList') = 1 then 3
            when smartquery.fields.exist('common:Collection') = 1 then 4
            when smartquery.fields.exist('common:SearchList') = 1 then 5
            when smartquery.fields.exist('common:Link') = 1  then 6
            when smartquery.fields.exist('common:File') = 1  then 7
            when smartquery.fields.exist('common:CreditCard') = 1 then 8
            when smartquery.fields.exist('common:Html') = 1  then 9
            else 0
        end as DESCRIPTORTYPE,
        case
            when smartquery.fields.exist('common:CodeTable') = 1 then 
                (select ID from dbo.CODETABLECATALOG where DBTABLENAME = smartquery.fields.value('(common:CodeTable/@CodeTableName)[1]', 'nvarchar(100)'))
            when smartquery.fields.exist('common:ValueList') = 1 then null
            when smartquery.fields.exist('common:SimpleDataList') = 1 then
                smartquery.fields.value('(common:SimpleDataList/@SimpleDataListID)[1]', 'uniqueidentifier')
            when smartquery.fields.exist('common:Collection') = 1 then null
            when smartquery.fields.exist('common:SearchList') = 1 then 
                smartquery.fields.value('(common:SearchList/@SearchListID)[1]', 'uniqueidentifier')
            when smartquery.fields.exist('common:Link') = 1  then null
            when smartquery.fields.exist('common:File') = 1  then null
            when smartquery.fields.exist('common:CreditCard') = 1 then null
            when smartquery.fields.exist('common:Html') = 1  then null
            else null
        end as DESCRIPTORID,
        coalesce(smartquery.fields.value('@Hidden', 'bit'), 0) as HIDDEN,
        coalesce(smartquery.fields.value('@Required', 'bit'), 0) as REQUIRED,
        coalesce(smartquery.fields.value('@ReadOnly', 'bit'), 0) as READONLY,        
        dbo.UFN_INSTALLEDPRODUCTS_TRANSLATELIST(smartquery.fields.query('common:InstalledProductList')) as INSTALLEDPRODUCTSLIST,
        dbo.UFN_INSTALLEDPRODUCTS_OPTIONALPRODUCTSINSTALLED(smartquery.fields.query('common:InstalledProductList')) as INSTALLED
    from dbo.SMARTQUERYCATALOG as Q
        cross apply Q.SMARTQUERYSPEC.nodes('tns:SmartQuerySpec/tns:SmartQueryFormDefinition/common:FormMetaData/common:FormFields/common:FormField') as smartquery(fields)
    where Q.ID = @SMARTQUERYID