USP_DATAFORMTEMPLATE_VIEW_QUERYDETAILS

CREATE procedure dbo.USP_DATAFORMTEMPLATE_VIEW_QUERYDETAILS
(
  @ID uniqueidentifier,
  @DATALOADED bit = 0 output,
  @ADDEDBYUSER nvarchar(128) = null output,
  @DATEADDED datetime = null output,
  @CHANGEDBYUSER nvarchar(128) = null output,
  @DATECHANGED datetime = null output,
  @SOURCEVIEW nvarchar(255) = null output,
  @RECORDTYPE nvarchar(50) = null output,
  @DESCRIPTION nvarchar(1024) = null output,
  @SELECTIONTYPE nvarchar(10) = null output,
  @RECORDCOUNT int = null output,
  @ASOF datetime = null output,
  @SHOWINQUERY bit = null output,
  @DELETEERRORMESSAGE nvarchar(250) = null output
)
as
  set nocount on;


  set @DATALOADED = 0;

  if exists (select 1 from dbo.[ADHOCQUERY] where [ID] = @ID)

    select
      @DATALOADED = 1,
      @ADDEDBYUSER = dbo.[UFN_CHANGEAGENT_GETUSERNAME]([ADHOCQUERY].[ADDEDBYID]),
      @DATEADDED = [ADHOCQUERY].[DATEADDED],
      @CHANGEDBYUSER = dbo.[UFN_CHANGEAGENT_GETUSERNAME]([ADHOCQUERY].[CHANGEDBYID]),
      @DATECHANGED = [ADHOCQUERY].[DATECHANGED],
      @SOURCEVIEW = [QUERYVIEWCATALOG].[DISPLAYNAME],
      @RECORDTYPE = [RECORDTYPE].[NAME],
      @DESCRIPTION = [ADHOCQUERY].[DESCRIPTION],
      @SELECTIONTYPE = case when [IDSETREGISTER].[STATIC] = 1 then 'Static' when [IDSETREGISTER].[STATIC] = 0 then 'Dynamic' else 'None' end,
      @RECORDCOUNT = [IDSETREGISTER].[NUMROWS],
      @ASOF = [IDSETREGISTER].[DATECHANGED],
      @SHOWINQUERY = [IDSETREGISTER].[USEINQUERYDESIGNER],
      @DELETEERRORMESSAGE = [QUERYWITHDELETEERROR].[DELETEERRORMESSAGE]
    from dbo.[ADHOCQUERY]
    inner join dbo.[QUERYVIEWCATALOG] on [ADHOCQUERY].[QUERYVIEWCATALOGID] = [QUERYVIEWCATALOG].[ID]
    left join dbo.[RECORDTYPE] on [QUERYVIEWCATALOG].[RECORDTYPEID] = [RECORDTYPE].[ID]
    left join dbo.[IDSETREGISTERADHOCQUERY] on [IDSETREGISTERADHOCQUERY].[ADHOCQUERYID] = [ADHOCQUERY].[ID]
    left join dbo.[IDSETREGISTER] on [IDSETREGISTERADHOCQUERY].[IDSETREGISTERID] = [IDSETREGISTER].[ID]
    left join dbo.[QUERYWITHDELETEERROR] on [ADHOCQUERY].[ID] = [QUERYWITHDELETEERROR].[QUERYID]
    where [ADHOCQUERY].[ID] = @ID

  else

    select
      @DATALOADED = 1,
      @ADDEDBYUSER = dbo.[UFN_CHANGEAGENT_GETUSERNAME]([SMARTQUERYINSTANCE].[ADDEDBYID]),
      @DATEADDED = [SMARTQUERYINSTANCE].[DATEADDED],
      @CHANGEDBYUSER = dbo.[UFN_CHANGEAGENT_GETUSERNAME]([SMARTQUERYINSTANCE].[CHANGEDBYID]),
      @DATECHANGED = [SMARTQUERYINSTANCE].[DATECHANGED],
      @SOURCEVIEW = [SMARTQUERYCATALOG].[NAME],
      @RECORDTYPE = [RECORDTYPE].[NAME],
      @DESCRIPTION = [SMARTQUERYINSTANCE].[DESCRIPTION],
      @SELECTIONTYPE = case when [IDSETREGISTER].[STATIC] = 1 then 'Static' when [IDSETREGISTER].[STATIC] = 0 then 'Dynamic' else 'None' end,
      @RECORDCOUNT = [IDSETREGISTER].[NUMROWS],
      @ASOF = [IDSETREGISTER].[DATECHANGED],
      @SHOWINQUERY = [IDSETREGISTER].[USEINQUERYDESIGNER],
      @DELETEERRORMESSAGE = [QUERYWITHDELETEERROR].[DELETEERRORMESSAGE]
    from dbo.[SMARTQUERYINSTANCE]
    inner join dbo.[SMARTQUERYCATALOG] on [SMARTQUERYINSTANCE].[SMARTQUERYCATALOGID] = [SMARTQUERYCATALOG].[ID]
    left join dbo.[RECORDTYPE] on [SMARTQUERYCATALOG].[RECORDTYPEID] = [RECORDTYPE].[ID]
    left join dbo.[IDSETREGISTERSMARTQUERYINSTANCE] on [IDSETREGISTERSMARTQUERYINSTANCE].[SMARTQUERYINSTANCEID] = [SMARTQUERYINSTANCE].[ID]
    left join dbo.[IDSETREGISTER] on [IDSETREGISTERSMARTQUERYINSTANCE].[IDSETREGISTERID] = [IDSETREGISTER].[ID]
    left join dbo.[QUERYWITHDELETEERROR] on [SMARTQUERYINSTANCE].[ID] = [QUERYWITHDELETEERROR].[QUERYID]
    where [SMARTQUERYINSTANCE].[ID] = @ID

    return 0;