USP_DATALIST_MASKEDSPONSORSHIPOPPORTUNITYID

Returns a list of opportunity IDs that will need a masking image.

Parameters

Parameter	Parameter Type	Mode	Description
@ID	uniqueidentifier	IN	Input parameter indicating the context ID for the data list.
@OPPORTUNITYIDS	xml	IN

Definition

Copy



        CREATE procedure dbo.USP_DATALIST_MASKEDSPONSORSHIPOPPORTUNITYID
        (
         @ID uniqueidentifier,
         @OPPORTUNITYIDS xml
        )
        as

          set nocount on;

            declare @MASKTABLE as nvarchar(500);
            declare @IDSETREGISTERID uniqueidentifier = null;
            declare @SQL nvarchar(max);

            select @IDSETREGISTERID = IDSETREGISTERID
            from dbo.IDSETREGISTERADHOCQUERY 
            where ADHOCQUERYID = @ID

            select @MASKTABLE = 'dbo.' + IDSETREGISTER.DBOBJECTNAME + case IDSETREGISTER.OBJECTTYPE when 1 then '()' else '' end
            from IDSETREGISTER where IDSETREGISTER.ID = @IDSETREGISTERID

            create table #Opps (
            ID uniqueidentifier )

            insert into #Opps (ID) 
            select ParamValues.ID.value('.', 'uniqueidentifier')
            from @OPPORTUNITYIDS.nodes('declare namespace ns="SponsorshipOpportunity"; /ns:ArrayOfString/ns:string') as ParamValues(ID)

            set @SQL = 'select O.ID OPPORTUNITYID from #Opps O inner join ' + @MASKTABLE + ' MASK on O.ID = MASK.ID'

            exec sp_executesql @SQL

            drop table #Opps