USP_SMARTQUERYINSTANCE_GETMETADATA

CREATE procedure dbo.USP_SMARTQUERYINSTANCE_GETMETADATA
(
    @ID as uniqueidentifier,
    @APPUSERID uniqueidentifier = null,
    @LOADROLEPERMISSIONS bit = 0
)
as

select 
    SMARTQUERYINSTANCE.ID, 
    SMARTQUERYINSTANCE.SMARTQUERYCATALOGID,
    SMARTQUERYINSTANCE.NAME,
    SMARTQUERYINSTANCE.DESCRIPTION,
    SMARTQUERYINSTANCE.QUERYCATEGORYCODEID,
    SMARTQUERYINSTANCE.DATAFORMINSTANCEID,
    SMARTQUERYINSTANCE.DATAFORMITEMFILTERXML,
    SMARTQUERYINSTANCE.OTHERSCANMODIFY,
    SMARTQUERYINSTANCE.OWNERID,
    APPUSER.USERNAME as [OWNER],
    SMARTQUERYCATALOG.OUTPUTDEFINITION,
    SMARTQUERYCATALOG.TVFNAME,
    SMARTQUERYCATALOG.TVFPARAMETERLIST,
    SMARTQUERYCATALOG.SMARTQUERYSPEC,
    IDSETREGISTER.ID as [IDSETREGISTER_ID],
    IDSETREGISTER.STATIC as [IDSETREGISTER_STATIC],
    IDSETREGISTER.USEINQUERYDESIGNER as [IDSETREGISTER_USEINQUERYDESIGNER],
    SMARTQUERYINSTANCE.ADDEDBYID,
    ADDEDBY.USERNAME ADDEDBYUSER,
    SMARTQUERYINSTANCE.CHANGEDBYID,
    CHANGEDBY.USERNAME CHANGEDBYUSER,
    SMARTQUERYINSTANCE.DATEADDED,
    SMARTQUERYINSTANCE.DATECHANGED,
    SMARTQUERYINSTANCE.TSLONG,
    SMARTQUERYINSTANCE.SITEID,
    SMARTQUERYINSTANCE.MOBILIZE,
    SMARTQUERYINSTANCE.FOLDERID,
    case when exists(select ID from dbo.APPUSERSMARTQUERYINSTANCEFAVORITE where SMARTQUERYINSTANCEID = SMARTQUERYINSTANCE.ID and APPUSERID = @APPUSERID)
        then convert(bit, 1) else convert(bit, 0)
    end as ISFAVORITE,
    SMARTQUERYINSTANCE.SECURITYLEVEL,
    case 
        when @LOADROLEPERMISSIONS = 1 then dbo.UFN_SMARTQUERYINSTANCE_GETSYSTEMROLEPERMISSIONSLIST_TOITEMLISTXML(@ID)
        else null
    end as PERMISSIONEDROLES
from dbo.SMARTQUERYINSTANCE
left join dbo.IDSETREGISTERSMARTQUERYINSTANCE on SMARTQUERYINSTANCE.ID = IDSETREGISTERSMARTQUERYINSTANCE.SMARTQUERYINSTANCEID
left join dbo.IDSETREGISTER on IDSETREGISTERSMARTQUERYINSTANCE.IDSETREGISTERID = IDSETREGISTER.ID
inner join dbo.APPUSER on SMARTQUERYINSTANCE.OWNERID = APPUSER.ID
inner join dbo.SMARTQUERYCATALOG on SMARTQUERYINSTANCE.SMARTQUERYCATALOGID = SMARTQUERYCATALOG.ID
inner join dbo.CHANGEAGENT ADDEDBY on SMARTQUERYINSTANCE.ADDEDBYID = ADDEDBY.ID
inner join dbo.CHANGEAGENT CHANGEDBY on SMARTQUERYINSTANCE.CHANGEDBYID = CHANGEDBY.ID
where SMARTQUERYINSTANCE.ID = @ID