USP_MKTAPPEALRECORDSOURCE_GETFIELDSFORSEARCH

CREATE procedure dbo.[USP_MKTAPPEALRECORDSOURCE_GETFIELDSFORSEARCH]
(
  @ID uniqueidentifier,
  @APPEALIDFIELD nvarchar(255),
  @DESCRIPTIONFIELD nvarchar(255),
  @INACTIVEFIELD nvarchar(255) = null,
  @STARTDATEFIELD nvarchar(255) = null,
  @ENDDATEFIELD nvarchar(255) = null
)
as
  set nocount on;

  declare @RECORDSOURCENAME nvarchar(255);
  declare @PRIMARYKEYFIELD nvarchar(128);
  declare @PRIMARYKEYTYPENAME nvarchar(128);
  declare @VIEWNAME nvarchar(128);
  declare @OUTPUTDEFINITIONXML xml;

  /* Grab some info about the appeal query view */
  select
    @RECORDSOURCENAME = [DISPLAYNAME],
    @PRIMARYKEYFIELD = [PRIMARYKEYFIELD],
    @PRIMARYKEYTYPENAME = [PRIMARYKEYTYPENAME],
    @VIEWNAME = [OBJECTNAME],
    @OUTPUTDEFINITIONXML = [OUTPUTDEFINITIONXML]
  from
    dbo.[QUERYVIEWCATALOG]
  where
    [ID] = @ID;

  /* Return all the info we need to build the appeal search screen  */
  select
    @RECORDSOURCENAME as [RECORDSOURCENAME],
    @VIEWNAME as [VIEWNAME],
    @PRIMARYKEYFIELD as [PRIMARYKEYFIELD],
    @PRIMARYKEYTYPENAME as [PRIMARYKEYTYPENAME],
    T.c.value('(@Name)[1]','nvarchar(255)') as [FIELDNAME],
    isnull(T.c.value('(@Caption)[1]','nvarchar(255)'), T.c.value('(@Name)[1]','nvarchar(255)')) as [CAPTION],
    [DATA_TYPE] as [DATATYPE]
  from
    @OUTPUTDEFINITIONXML.nodes('declare namespace QV="bb_appfx_queryview";/QueryViewOutput/QV:OutputFields/QV:OutputField') T(c)
  inner join
    INFORMATION_SCHEMA.COLUMNS [COLS]
  on
    [COLS].[TABLE_NAME] = @VIEWNAME and [COLS].[COLUMN_NAME] = T.c.value('(@Name)[1]','nvarchar(255)')
  where
    T.c.value('(@Name)[1]','nvarchar(255)') in (@PRIMARYKEYFIELD, @APPEALIDFIELD, @DESCRIPTIONFIELD, @INACTIVEFIELD, @STARTDATEFIELD, @ENDDATEFIELD);

  return 0;