USP_SEARCHLIST_NOEVENTDESIGNATION

Parameters

Parameter	Parameter Type	Mode
@EVENTID	uniqueidentifier	IN
@CURRENTAPPUSERID	uniqueidentifier	IN
@USERID	nvarchar(100)	IN
@MAXROWS	smallint	IN
@DESIGNATIONNAME	nvarchar(512)	IN
@VANITYNAME	nvarchar(100)	IN
@INCLUDEINACTIVE	bit	IN
@DESIGNATIONUSECODEID	uniqueidentifier	IN
@DESIGNATIONLEVELTYPEID	uniqueidentifier	IN
@DESIGNATIONLEVELCATEGORYCODEID	uniqueidentifier	IN
@SITEID	uniqueidentifier	IN

Definition

Copy



create procedure dbo.USP_SEARCHLIST_NOEVENTDESIGNATION
(
  @EVENTID uniqueidentifier, --  to include designation from this event id

  @CURRENTAPPUSERID uniqueidentifier, 
    @USERID nvarchar(100) = null,
    @MAXROWS smallint = 500,
  @DESIGNATIONNAME nvarchar(512) = null,
  @VANITYNAME nvarchar(100) = null,
  @INCLUDEINACTIVE bit = 0,
  @DESIGNATIONUSECODEID uniqueidentifier = null,
  @DESIGNATIONLEVELTYPEID uniqueidentifier = null,
  @DESIGNATIONLEVELCATEGORYCODEID uniqueidentifier = null,
  @SITEID uniqueidentifier = null

)
as
    set @DESIGNATIONNAME = COALESCE(@DESIGNATIONNAME,'') + '%' ;

    select distinct top (@MAXROWS)
    DESIGNATION.ID
    ,DESIGNATION.USERID
    ,dbo.UFN_DESIGNATION_BUILDNAME(DESIGNATION.ID)
    ,DESIGNATION.VANITYNAME
    ,DESIGNATION.ISACTIVE
    ,(
        select dbo.UDA_BUILDLIST(ALTLOOKUPID + '(' + DESIGNATIONALTLOOKUPIDTYPECODE.DESCRIPTION + ')') 
        from dbo.DESIGNATIONALTLOOKUPID 
        inner join dbo.DESIGNATIONALTLOOKUPIDTYPECODE on DESIGNATIONALTLOOKUPIDTYPECODE.ID = DESIGNATIONALTLOOKUPID.ALTLOOKUPIDTYPECODEID 
        where DESIGNATIONALTLOOKUPID.DESIGNATIONID = DESIGNATION.ID 
    )
    ,dbo.UFN_DESIGNATIONUSECODE_GETDESCRIPTION(DESIGNATION.DESIGNATIONUSECODEID) [DESIGNATIONUSECODE]
    ,dbo.UFN_TRANSLATIONFUNCTION_SITE_GETNAME(coalesce(DL5.SITEID, DL4.SITEID, DL3.SITEID, DL2.SITEID, DL1.SITEID)) [SITE]
  from dbo.UFN_DESIGNATION_GET_BYNOEVENTASSOCIATION(@CURRENTAPPUSERID, @EVENTID) DD
  join dbo.DESIGNATION with (nolock) on DD.ID = DESIGNATION.ID
  left join dbo.DESIGNATIONLEVEL DL1 on DESIGNATION.DESIGNATIONLEVEL1ID = DL1.ID
  left join dbo.DESIGNATIONLEVEL DL2 on DESIGNATION.DESIGNATIONLEVEL2ID = DL2.ID
  left join dbo.DESIGNATIONLEVEL DL3 on DESIGNATION.DESIGNATIONLEVEL3ID = DL3.ID
  left join dbo.DESIGNATIONLEVEL DL4 on DESIGNATION.DESIGNATIONLEVEL4ID = DL4.ID
  left join dbo.DESIGNATIONLEVEL DL5 on DESIGNATION.DESIGNATIONLEVEL5ID = DL5.ID
  where DESIGNATION.ISREVENUEDESIGNATION = 1
   and (@INCLUDEINACTIVE = 0 or DESIGNATION.ISACTIVE = @INCLUDEINACTIVE)
   --and exists ( select 1 from dbo.UFN_SITEALLOWEDFORUSER(@CURRENTAPPUSERID, @FEATUREID, @FEATURETYPE) [SITES]                                 

   --where [SITES].SITEID = coalesce(DL5.SITEID, DL4.SITEID, DL3.SITEID, DL2.SITEID, DL1.SITEID) or                                       

   --[SITES].SITEID is null and coalesce(DL5.SITEID, DL4.SITEID, DL3.SITEID, DL2.SITEID, DL1.SITEID) is null                               ) 

  order by DESIGNATION.USERID