USP_SPONSORSHIP_CREATEPROGRAMUNIONQUERYVIEW

CREATE procedure dbo.USP_SPONSORSHIP_CREATEPROGRAMUNIONQUERYVIEW
(
  @QUERYVIEWCATALOGID uniqueidentifier
)
as
begin
    declare @SQL nvarchar(max) ='';
  declare @SPONSORSHIPPROGRAMS table(ID uniqueidentifier, LABEL nvarchar(255));
  declare @SPONSORSHIPPROGRAMID uniqueidentifier;
  declare @PROGRAMCOUNT int;
  declare @LOOPINDEX int=1;
  declare @QUERYSPEC xml;
  declare @LABEL nvarchar(255);

  select 
    @QUERYSPEC = QUERYVIEWSPEC
  from dbo.QUERYVIEWCATALOG where ID= @QUERYVIEWCATALOGID;

  insert into 
      @SPONSORSHIPPROGRAMS 
  execute ('execute USP_SIMPLEDATALIST_SPONSORSHIPPROGRAMTYPE');

  select
      @PROGRAMCOUNT = count(*)
  from @SPONSORSHIPPROGRAMS

  -- Loop through all programs and then create the SQL for the query view spec.

  declare SPONSORSHIP_CURSOR cursor local fast_forward for
  select ID, LABEL from @SPONSORSHIPPROGRAMS;
  open SPONSORSHIP_CURSOR;
  begin try
      fetch next from SPONSORSHIP_CURSOR into
          @SPONSORSHIPPROGRAMID, @LABEL
      while (@@FETCH_STATUS = 0)
      begin
              set @SQL = @SQL + char(13)+ 'select'+char(13)+
              'SPONSORSHIPOPPORTUNITYID ID,'+char(13)+
              ''''+ replace(@LABEL,'''','''''') +''' PROGRAM, '+char(13) +
              ''''+ upper(@SPONSORSHIPPROGRAMID)+''' SPONSORSHIPPROGRAMID,' +char(13)+ 
              'GNORDER' + char(13)+ 
              'from dbo.V_QUERY_SPONSORSHIPGREATESTNEED'+ replace(upper(@SPONSORSHIPPROGRAMID),'-','')+char(13)

              if @LOOPINDEX <> @PROGRAMCOUNT
                  set @SQL = @SQL + 'union all'+char(13);    

              set @LOOPINDEX = @LOOPINDEX + 1;
      fetch next from SPONSORSHIP_CURSOR into
          @SPONSORSHIPPROGRAMID, @LABEL;
      end
      close SPONSORSHIP_CURSOR;
      deallocate SPONSORSHIP_CURSOR;
  end try
  begin catch
    close SPONSORSHIP_CURSOR;
    deallocate SPONSORSHIP_CURSOR;
    exec dbo.USP_RAISE_ERROR;
  end catch

  -- If no programs were found the revert back to the old SQL for the query view.

  if @LOOPINDEX = 1
      set @SQL = char(13) + 'select '+char (13)+
      'newid() ID,' + char(13) +
      'newid() SPONSORSHIPPROGRAMID,' +char(13)+
      ''''' PROGRAM,'+ char(13)+
      '0 GNORDER'+char(13);


  -- Load the new query view spec with the newly created SQL.

  if @QUERYSPEC is not null
  begin
    set @QUERYSPEC.modify('declare namespace BB="bb_appfx_queryview"; 
    replace value of (/BB:QueryViewSpec/BB:ViewImplementation/BB:ViewSQL/text())[1] with sql:variable("@SQL")')

    exec dbo.USP_LOADSPEC @QUERYSPEC, null, null, null;
  end
end