USP_RECOGNITIONPROGRAM_RECREATEQUERY

create procedure dbo.USP_RECOGNITIONPROGRAM_RECREATEQUERY
(
  @RECOGNITIONPROGRAMID uniqueidentifier = null,
  @CHANGEAGENTID uniqueidentifier = null
)
as
  begin try
    if @CHANGEAGENTID is null
      exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output;

    declare @TEMPLATESQL nvarchar(max);

    select
      @TEMPLATESQL = T.c.value('(text())[1]','nvarchar(max)')
    from
      dbo.QUERYVIEWCATALOG
    cross apply
      QUERYVIEWCATALOG.QUERYVIEWSPEC.nodes('declare default element namespace "bb_appfx_queryview";/QueryViewSpec/ViewImplementation/ViewSQL') T(c)
    where
      ID = '1F975D0A-DD74-4A4B-88F0-8A515679E845' -- Template query view


    declare RECOGNITIONPROGRAMCURSOR cursor local fast_forward for
    select
      RECOGNITIONPROGRAM.ID,
      QUERYVIEWCATALOG.ID,
      QUERYVIEWCATALOG.QUERYVIEWSPEC
    from
      dbo.RECOGNITIONPROGRAM
    inner join
      dbo.QUERYVIEWCATALOG on QUERYVIEWCATALOG.OBJECTNAME = 'V_QUERY_RECOGNITION_' + upper(REPLACE(CONVERT(nvarchar(36), RECOGNITIONPROGRAM.ID), '-', ''))
    where
      @RECOGNITIONPROGRAMID is null or RECOGNITIONPROGRAM.ID = @RECOGNITIONPROGRAMID;

    declare @QUERYVIEWCATALOGID uniqueidentifier, @QUERYVIEWSPEC xml;

    open RECOGNITIONPROGRAMCURSOR;
    fetch next from RECOGNITIONPROGRAMCURSOR into @RECOGNITIONPROGRAMID, @QUERYVIEWCATALOGID, @QUERYVIEWSPEC;

    while @@FETCH_STATUS = 0
    begin
      declare @RECOGNITIONPROGRAMSQL nvarchar(max) = replace(@TEMPLATESQL, '/*WHERECLAUSE*/', ' where CR.RECOGNITIONPROGRAMID = ''' + convert(nvarchar(36), @RECOGNITIONPROGRAMID) + '''')
      set @QUERYVIEWSPEC.modify('declare default element namespace "bb_appfx_queryview";
                                 replace value of (/QueryViewSpec/ViewImplementation/ViewSQL/text())[1] with sql:variable("@RECOGNITIONPROGRAMSQL")')

      exec dbo.USP_LOADSPEC @QUERYVIEWSPEC, @CHANGEAGENTID;

      fetch next from RECOGNITIONPROGRAMCURSOR into @RECOGNITIONPROGRAMID, @QUERYVIEWCATALOGID, @QUERYVIEWSPEC;
    end

    close RECOGNITIONPROGRAMCURSOR;
    deallocate RECOGNITIONPROGRAMCURSOR;
  end try

  begin catch
    exec dbo.USP_RAISE_ERROR;
    return 1;
  end catch