USP_SEARCHLIST_FACULTY

Search for a faculty member.

Parameters

Parameter	Parameter Type	Mode	Description
@KEYNAME	nvarchar(100)	IN	Last name
@FIRSTNAME	nvarchar(100)	IN	First name
@LOOKUPID	nvarchar(100)	IN	Lookup ID
@CHECKNICKNAME	bit	IN	Check nickname
@CHECKALTERNATELOOKUPIDS	bit	IN	Check alternate lookup IDs
@INCLUDEDECEASED	bit	IN	Include deceased
@INCLUDEINACTIVE	bit	IN	Include inactive
@MAXROWS	smallint	IN	Input parameter indicating the maximum number of rows to return.
@ONLYADVISORS	bit	IN	Only Advisors
@ACADEMICCATALOGDEPARTMENTID	uniqueidentifier	IN	Department

Definition

Copy



            CREATE procedure dbo.USP_SEARCHLIST_FACULTY
            (
                @KEYNAME nvarchar(100) = null,
                @FIRSTNAME nvarchar(100) = null,
                @LOOKUPID nvarchar(100) = null,
                @CHECKNICKNAME bit = null,
                @CHECKALTERNATELOOKUPIDS bit = null,
                @INCLUDEDECEASED bit = null,
                @INCLUDEINACTIVE bit = null,
                @MAXROWS smallint = 500,
                @ONLYADVISORS bit = 0,
                @ACADEMICCATALOGDEPARTMENTID uniqueidentifier = null
            )
            as
                set nocount on;

                select distinct top(@MAXROWS) 
                    dbo.CONSTITUENT.ID,
                    dbo.CONSTITUENT.LOOKUPID,
                    dbo.CONSTITUENT.NAME,

                    -- list of departments

                   (select stuff((select '; ' + dbo.UFN_ACADEMICCATALOGDEPARTMENT_GETUSERIDFORDUPESCHOOL(ACADEMICCATALOGDEPARTMENT.ID)
                       from dbo.FACULTYACADEMICCATALOGDEPARTMENT
                       join dbo.ACADEMICCATALOGDEPARTMENT on ACADEMICCATALOGDEPARTMENT.ID = FACULTYACADEMICCATALOGDEPARTMENT.ACADEMICCATALOGDEPARTMENTID
                       where FACULTYACADEMICCATALOGDEPARTMENT.FACULTYID = CONSTITUENT.ID
                       order by ACADEMICCATALOGDEPARTMENT.USERID
                       for xml path(''), type).value('.', 'nvarchar(max)'), 1, 2, '')) AS DEPARTMENTS

                from dbo.CONSTITUENT with (nolock)

                inner join dbo.FACULTY on FACULTY.ID = CONSTITUENT.ID
                left join dbo.DECEASEDCONSTITUENT on CONSTITUENT.ID = DECEASEDCONSTITUENT.ID
                left join dbo.FACULTYACADEMICCATALOGDEPARTMENT on FACULTYACADEMICCATALOGDEPARTMENT.FACULTYID = FACULTY.ID

                where
                    (@KEYNAME is null or (CONSTITUENT.KEYNAME like @KEYNAME + '%')) and
                    (
                        (@FIRSTNAME is null or (CONSTITUENT.FIRSTNAME like @FIRSTNAME + '%')) or
                        (@CHECKNICKNAME <> 0 and CONSTITUENT.NICKNAME like @FIRSTNAME + '%')
                    ) and
                    (@LOOKUPID is null or (CONSTITUENT.LOOKUPID like @LOOKUPID + '%' or
                        (@CHECKALTERNATELOOKUPIDS <> 0 and exists (
                            select id from ALTERNATELOOKUPID with (nolock)
                            where CONSTITUENTID = CONSTITUENT.ID and
                                ALTERNATELOOKUPID like @LOOKUPID + '%'
                        ))
                    ))  and
                    ((@INCLUDEDECEASED <> 0) or (DECEASEDCONSTITUENT.ID is null)) and
                    ((@INCLUDEINACTIVE <> 0) or (CONSTITUENT.ISINACTIVE = 0)) and
                    ((@ONLYADVISORS = 0) or (ISADVISOR = 1)) and
                    ((@ACADEMICCATALOGDEPARTMENTID is null) or (@ACADEMICCATALOGDEPARTMENTID = FACULTYACADEMICCATALOGDEPARTMENT.ACADEMICCATALOGDEPARTMENTID))

                order by CONSTITUENT.NAME, CONSTITUENT.LOOKUPID