USP_DATAFORMTEMPLATE_VIEW_ASKLADDER

CREATE procedure dbo.USP_DATAFORMTEMPLATE_VIEW_ASKLADDER
(
  @ID uniqueidentifier,
  @DATALOADED bit = 0 output,
  @ASKLADDERNAME nvarchar(100) = null output,
  @BASECURRENCYID uniqueidentifier = null output, 
  @ENTRYAMOUNT nvarchar(200) = null output,
  @OBJECTKEY nvarchar(400) = null output,
  @ITEMLIST xml = null output,
  @SITENAME nvarchar(100) = null output
)
as
  set nocount on;

  set @DATALOADED = 0;

  select
    @ASKLADDERNAME = [MKTASKLADDER].[NAME],
    @ENTRYAMOUNT = coalesce([FIELDINFO].[PARENTTYPE] + ' \  ' + [FIELDINFO].[DISPLAYNAME],[SMARTFIELDINFO].[PARENTTYPE] + ' \  ' + [SMARTFIELDINFO].[DISPLAYNAME], [MKTASKLADDER].[OBJECTKEY]),
    @OBJECTKEY = [MKTASKLADDER].[OBJECTKEY],
    @BASECURRENCYID = [BASECURRENCYID],
    @ITEMLIST = dbo.[UFN_MKTASKLADDER_GETITEMLIST_TOITEMLISTXML]([MKTASKLADDER].[ID]),
    @SITENAME = [SITE].[NAME],
    @DATALOADED = 1
  from dbo.[MKTASKLADDER]
  left join dbo.[SITE] on [SITE].ID = [MKTASKLADDER].[SITEID]
  left join 
    (select 
      [QUERYVIEWCATALOG].[DISPLAYNAME] as [PARENTTYPE],
      (select isnull(T.c.value('(@Caption)[1]', 'nvarchar(255)'), T.c.value('(@Name)[1]', 'nvarchar(255)')) from [QUERYVIEWCATALOG].[OUTPUTDEFINITIONXML].nodes('declare namespace QV="bb_appfx_queryview";/QueryViewOutput/QV:OutputFields/QV:OutputField') T(c) where T.c.value('(@Name)[1]', 'nvarchar(255)') = [SYSCOLUMNS].[NAME]) as [DISPLAYNAME],
      [SYSCOLUMNS].[NAME] + '.' + [QUERYVIEWCATALOG].[OBJECTNAME] as [OBJECTKEY]
    from dbo.[QUERYVIEWCATALOG] 
    left join dbo.[SYSOBJECTS] on [SYSOBJECTS].[NAME] = [QUERYVIEWCATALOG].[OBJECTNAME] 
    left join dbo.[SYSCOLUMNS] on [SYSCOLUMNS].[ID] = [SYSOBJECTS].[ID])
  as [FIELDINFO] on [FIELDINFO].[OBJECTKEY] = [MKTASKLADDER].[OBJECTKEY]
  left join
    (select 
      'Smart Field' as [PARENTTYPE],
      [SMARTFIELD].[NAME] as [DISPLAYNAME],
      [SMARTFIELD].[VALUECOLUMNNAME] + '.' + [TABLECATALOG].[TABLENAME] as [OBJECTKEY]
    from dbo.[SMARTFIELD]
    inner join dbo.[SMARTFIELDCATALOG] on [SMARTFIELDCATALOG].[ID] = [SMARTFIELD].[SMARTFIELDCATALOGID]
    inner join dbo.[TABLECATALOG] on [TABLECATALOG].[ID] = [SMARTFIELD].[TABLECATALOGID])
  as [SMARTFIELDINFO] on [SMARTFIELDINFO].[OBJECTKEY] = [MKTASKLADDER].[OBJECTKEY]
  where [MKTASKLADDER].[ID] = @ID;

  return 0;