USP_MKTRECORDSOURCE_GETINSTALLEDPRODUCTIDS

create procedure dbo.[USP_MKTRECORDSOURCE_GETINSTALLEDPRODUCTIDS]
(
  @RECORDSOURCEID uniqueidentifier
)
as
  set nocount on;

  declare @ROWS int;
  declare @INDEX int;
  declare @QUERYDEFINITIONXML xml;
  declare @RESULTS table ([ID] uniqueidentifier);
  declare @PRODUCTID uniqueidentifier;

  begin try
    select @QUERYDEFINITIONXML = [QUERYVIEWCATALOG].[QUERYVIEWSPEC]
    from dbo.[QUERYVIEWCATALOG]
      inner join dbo.[RECORDTYPE] on [RECORDTYPE].[ID] = [QUERYVIEWCATALOG].[RECORDTYPEID]
      left join dbo.[MKTRECORDSOURCE] on [MKTRECORDSOURCE].[ID] = [QUERYVIEWCATALOG].[ID]
    where [MKTRECORDSOURCE].[ID] = @RECORDSOURCEID;

    set @ROWS = @QUERYDEFINITIONXML.value('declare namespace AQ="bb_appfx_queryview";declare namespace c="bb_appfx_commontypes"; count(/AQ:QueryViewSpec/c:InstalledProductList/c:InstalledProduct/@ID)','int');
    set @INDEX = 1;

    while (@INDEX <= @ROWS)
    begin
        select @PRODUCTID = @QUERYDEFINITIONXML.value('declare namespace AQ="bb_appfx_queryview";declare namespace c="bb_appfx_commontypes"; ((/AQ:QueryViewSpec/c:InstalledProductList/c:InstalledProduct[sql:variable("@INDEX")]/@ID))[1]', 'uniqueidentifier');
        insert @RESULTS ([ID]) values (@PRODUCTID);
        set @INDEX = @INDEX + 1;
    end

    select [ID] from @RESULTS;
  end try

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

  return 0;