USP_ADHOCQUERY_GETDEFINITION

CREATE procedure dbo.USP_ADHOCQUERY_GETDEFINITION
(
    @APPUSERID uniqueidentifier, 
    @ADHOCQUERYID uniqueidentifier, 
    @LOADROLEPERMISSIONS bit = 0,
    @LOADIDSETINUSEFLAG bit = 1
)
    with execute as caller
    as

    set nocount on;

  declare @IDSETINUSEBYADHOCQUERY bit = 0; 

  if @LOADIDSETINUSEFLAG = 1
  begin
    select top 1 
      @IDSETINUSEBYADHOCQUERY = 1 
    from [dbo].[ADHOCQUERYIDSETDEPENDENCY] 
    where [ADHOCQUERYIDSETDEPENDENCY].[ADHOCQUERYDEPENDENCYID] = @ADHOCQUERYID;
  end

    select QUERYDEFINITIONXML, 
        ADHOCQUERY.QUERYVIEWCATALOGID,
        QUERYVIEWCATALOG.DISPLAYNAME as QUERYVIEWDISPLAYNAME,
        ADHOCQUERY.OTHERSCANMODIFY,
        ADHOCQUERY.OWNERID,
        APPUSER.USERNAME as [OWNER],
        @IDSETINUSEBYADHOCQUERY as [IDSETINUSEBYADHOCQUERY],
        RECORDTYPE.NAME as [RECORDTYPE],
        ADHOCQUERY.ADDEDBYID,
        ADDEDBY.USERNAME as ADDEDBY_USERNAME,
        ADHOCQUERY.CHANGEDBYID,
        CHANGEDBY.USERNAME as CHANGEDBY_USERNAME,
        ADHOCQUERY.DATEADDED,
        ADHOCQUERY.DATECHANGED,
        ADHOCQUERY.FOLDERID,
        ADHOCQUERY.SECURITYLEVEL,
        ADHOCQUERY.SECURITYLEVELEDIT,
        case 
            when @LOADROLEPERMISSIONS = 1 then dbo.UFN_ADHOCQUERYINSTANCE_GETSYSTEMROLEPERMISSIONSLIST_TOITEMLISTXML(ADHOCQUERY.ID)
            else null
        end as PERMISSIONEDROLES,
        case 
            when @LOADROLEPERMISSIONS = 1 then dbo.UFN_ADHOCQUERYINSTANCE_GETSYSTEMROLEEDITPERMISSIONSLIST_TOITEMLISTXML(ADHOCQUERY.ID)
            else null
        end as PERMISSIONEDROLESEDIT,
        ADHOCQUERY.MOBILIZE,
        case when exists(select ID from dbo.APPUSERADHOCQUERYFAVORITE where ADHOCQUERYID = ADHOCQUERY.ID and APPUSERID = @APPUSERID)
            then convert(bit, 1) else convert(bit, 0)
        end as ISFAVORITE
    from dbo.ADHOCQUERY
    inner join dbo.QUERYVIEWCATALOG on ADHOCQUERY.QUERYVIEWCATALOGID = QUERYVIEWCATALOG.ID
    left join dbo.RECORDTYPE on QUERYVIEWCATALOG.RECORDTYPEID = RECORDTYPE.ID
    inner join dbo.APPUSER on ADHOCQUERY.OWNERID = APPUSER.ID
    inner join dbo.CHANGEAGENT as ADDEDBY on ADHOCQUERY.ADDEDBYID = ADDEDBY.ID
    inner join dbo.CHANGEAGENT as CHANGEDBY on ADHOCQUERY.CHANGEDBYID = CHANGEDBY.ID
    where ADHOCQUERY.ID = @ADHOCQUERYID;