UFN_ACADEMICCATALOGDEPARTMENT_GETUSERIDFORDUPESCHOOL

Returns the ID of a department in a school, with the school name appended if the department ID is duplicated across schools.

Return

Return Type
nvarchar(155)

Parameters

Parameter	Parameter Type	Mode	Description
@ACADEMICCATALOGDEPARTMENTID	uniqueidentifier	IN

Definition

Copy



create function dbo.UFN_ACADEMICCATALOGDEPARTMENT_GETUSERIDFORDUPESCHOOL(@ACADEMICCATALOGDEPARTMENTID uniqueidentifier)
returns nvarchar(155)
with execute as caller
as begin
    return (
        select  
            case when COUNT(d2.USERID) = 1  -- if more than one dept with same ID, append School

                then d1.USERID
                else d1.USERID + ' - ' + CONSTITUENT.KEYNAME 
            end
        from 
            dbo.ACADEMICCATALOGDEPARTMENT d1
        join 
            dbo.CONSTITUENT on CONSTITUENT.ID = d1.SCHOOLID
        join 
            dbo.ACADEMICCATALOGDEPARTMENT d2 on d1.USERID = d2.USERID
        where d1.ID = @ACADEMICCATALOGDEPARTMENTID
        group by 
            d1.USERID, 
            CONSTITUENT.KEYNAME
   )
end