USP_SEARCHLIST_SPONSORSHIPLOCATION

Search for sponsorship locations.

Parameters

Parameter	Parameter Type	Mode	Description
@NAME	nvarchar(100)	IN	Name
@TYPE	uniqueidentifier	IN	Type
@LOOKUPID	nvarchar(100)	IN	Lookup ID
@INCLUDEINACTIVE	bit	IN	Include inactive
@INCLUDECLOSED	bit	IN	Include closed
@INCLUDECHILDNODES	bit	IN	Include sublocations of matches
@WITHINLOCATION	uniqueidentifier	IN	WITHINLOCATION
@WITHINLOCATIONDISPLAY	nvarchar(max)	IN	Within
@EXCLUDELOCATION	uniqueidentifier	IN	EXCLUDELOCATION
@MAXROWS	smallint	IN	Input parameter indicating the maximum number of rows to return.

Definition

Copy



CREATE procedure dbo.USP_SEARCHLIST_SPONSORSHIPLOCATION
(
    @NAME nvarchar(100) = null,
    @TYPE uniqueidentifier = null,
    @LOOKUPID nvarchar(100) = null,
    @INCLUDEINACTIVE bit = 0,
    @INCLUDECLOSED bit = 0,
    @INCLUDECHILDNODES bit = 0,
    @WITHINLOCATION uniqueidentifier = null,
    -- not used, for UI only

    @WITHINLOCATIONDISPLAY nvarchar(max) = null,
    @EXCLUDELOCATION uniqueidentifier = null,
    @MAXROWS smallint = 500
)
as
    set @NAME = replace(coalesce(@NAME,''),' \ ','\')+'%';

    select distinct top(@MAXROWS)
        SPONSORSHIPLOCATION.ID,
        dbo.UFN_SPONSORSHIPLOCATION_GETFULLNAME(SPONSORSHIPLOCATION.ID) FULLSTRING,
        SPONSORSHIPLOCATIONTYPECODE.DESCRIPTION TYPE,
        SPONSORSHIPLOCATION.STATUS
    from dbo.SPONSORSHIPLOCATION
    inner join
        dbo.SPONSORSHIPLOCATIONTYPECODE on SPONSORSHIPLOCATIONTYPECODE.ID = SPONSORSHIPLOCATION.SPONSORSHIPLOCATIONTYPECODEID
    inner join
    (
        select SPONSORSHIPLOCATION.ID,
               SPONSORSHIPLOCATION.HIERARCHYPATH
        from 
            dbo.SPONSORSHIPLOCATION
        inner join
            dbo.SPONSORSHIPLOCATIONTYPECODE on SPONSORSHIPLOCATIONTYPECODE.ID = SPONSORSHIPLOCATION.SPONSORSHIPLOCATIONTYPECODEID
        where (SPONSORSHIPLOCATION.NAME like @NAME or
                -- allow name searches like 'Africa\Mali'

               (@NAME like '%\%' and
                '\'+dbo.UFN_SPONSORSHIPLOCATION_FULLSTRING(SPONSORSHIPLOCATION.ID,'\',0,0) like '%\'+@NAME and
                SPONSORSHIPLOCATION.NAME like substring(@NAME,len(@NAME)-charindex('\',reverse(@NAME))+2,len(@NAME))))
        and (@TYPE is null or SPONSORSHIPLOCATION.SPONSORSHIPLOCATIONTYPECODEID = @TYPE)
        and (@LOOKUPID is null or SPONSORSHIPLOCATION.LOOKUPID like '%'+ @LOOKUPID + '%')
        and (SPONSORSHIPLOCATION.STATUSCODE = 0 or
             (@INCLUDEINACTIVE = 1 and SPONSORSHIPLOCATION.STATUSCODE = 1) or
             (@INCLUDECLOSED = 1 and SPONSORSHIPLOCATION.STATUSCODE = 2))
        and (@WITHINLOCATION is null or SPONSORSHIPLOCATION.HIERARCHYPATH.IsDescendantOf((select HIERARCHYPATH from dbo.SPONSORSHIPLOCATION where ID=@WITHINLOCATION)) = 1)
        and (@EXCLUDELOCATION is null or SPONSORSHIPLOCATION.HIERARCHYPATH.IsDescendantOf((select HIERARCHYPATH from dbo.SPONSORSHIPLOCATION where ID=@EXCLUDELOCATION)) = 0)
    ) MATCHES on 1=1
    where (SPONSORSHIPLOCATION.ID = MATCHES.ID or
           (@INCLUDECHILDNODES = 1 and SPONSORSHIPLOCATION.HIERARCHYPATH.IsDescendantOf(MATCHES.HIERARCHYPATH) = 1))
    and (SPONSORSHIPLOCATION.STATUSCODE = 0 or
         (@INCLUDEINACTIVE = 1 and SPONSORSHIPLOCATION.STATUSCODE = 1) or
         (@INCLUDECLOSED = 1 and SPONSORSHIPLOCATION.STATUSCODE = 2))
    and (@EXCLUDELOCATION is null or SPONSORSHIPLOCATION.HIERARCHYPATH.IsDescendantOf((select HIERARCHYPATH from dbo.SPONSORSHIPLOCATION where ID=@EXCLUDELOCATION)) = 0)
    order by 
        FULLSTRING