USP_SPONSORSHIPOPPORTUNITYRESREVATIONKEY_SEARCH

Search for a sponsorship opportunity reservation key

Parameters

Parameter	Parameter Type	Mode	Description
@NAME	nvarchar(100)	IN	Reservation key name
@INCLUDEINACTIVE	bit	IN	Include inactive keys
@MAXROWS	smallint	IN	Input parameter indicating the maximum number of rows to return.

Definition

Copy



CREATE procedure dbo.USP_SPONSORSHIPOPPORTUNITYRESREVATIONKEY_SEARCH
(
  @NAME nvarchar(100) = null,    
    --@GROUPID uniqueidentifier = null,    

    @INCLUDEINACTIVE bit = 0,
    @MAXROWS smallint = 500
)
as 

if ISNULL(@NAME, '%') = '%'
begin  
    set @NAME = '%';   
end
else
begin
  if @NAME is not null 
  begin
     set @NAME = @NAME + '%';
  end
end

  select top(@MAXROWS)
    SPP.ID,
    SPP.NAME,
    SPS.STATUS
    --(select count(*) from dbo.SPONSORSHIPOPPORTUNITY where SPONSORSHIPOPPORTUNITY.RESERVATIONKEYID = SPP.ID) RESERVED

  from 
    dbo.SPONSORSHIPOPPORTUNITYRESERVEPROCESS SPP 
    inner join dbo.SPONSORSHIPOPPORTUNITYRESERVATIONSTATUS SPS on SPS.OPPORTUNITYRESERVATIONKEYID = SPP.ID
  where 
    (SPP.NAME LIKE @NAME or @NAME IS NULL)
        --and (@GROUPID is null or @GROUPID in (select SPONSORSHIPOPPORTUNITYGROUPID from dbo.SPONSORSHIPOPPORTUNITY where RESERVATIONKEYID = SPP.ID))

    and SPS.STATUSCODE in (1, case @INCLUDEINACTIVE when 0 then 1 else 0 end)
    order by SPP.NAME