UFN_CLASS_GETDEFAULTSECTION

CREATE function dbo.UFN_CLASS_GETDEFAULTSECTION
(
    @COURSEID uniqueidentifier,
    @SESSIONID uniqueidentifier,
    @TERMID uniqueidentifier
)
returns nvarchar(21)
with execute as caller
as begin
    declare @SECTION nvarchar(21)    

    declare @LENGTHINTERMS int
    select
        @LENGTHINTERMS = COURSERESTRICTION.LENGTHINTERMS
    from dbo.COURSE
        inner join dbo.V_COURSERESTRICTION as COURSERESTRICTION on COURSE.ID = COURSERESTRICTION.COURSEID
    where COURSE.ID = @COURSEID

    declare @STARTTERMDATE datetime
    select
        @STARTTERMDATE = STARTDATE
    from dbo.TERM
    where ID = @TERMID    

    declare @TERMS table (
        ID uniqueidentifier
    )    
    insert into @TERMS (ID)
    select top (@LENGTHINTERMS) TERM.ID
    from TERM
    where SESSIONID = @SESSIONID and STARTDATE >= @STARTTERMDATE
    order by TERM.STARTDATE

    select @SECTION = max(convert(numeric(21,0), CLASS.SECTION))
    from dbo.CLASS
        inner join dbo.CLASSMEETINGGROUP on CLASS.ID = CLASSMEETINGGROUP.CLASSID
        inner join dbo.COURSE on dbo.CLASS.COURSEID = dbo.COURSE.ID
        inner join dbo.ACADEMICYEAR on COURSE.SCHOOLID = ACADEMICYEAR.SCHOOLID
        inner join dbo.CONSTITUENT on COURSE.SCHOOLID = CONSTITUENT.ID
        inner join dbo.SESSION on ACADEMICYEAR.ID = SESSION.ACADEMICYEARID
        inner join dbo.TERM on SESSION.ID = TERM.SESSIONID
            and TERM.STARTDATE = CLASSMEETINGGROUP.STARTDATE
            and TERM.ENDDATE = CLASSMEETINGGROUP.ENDDATE
        inner join @TERMS CLASSTERMS on TERM.ID = CLASSTERMS.ID
    where CLASS.COURSEID = @COURSEID
        and SESSION.ID = @SESSIONID
        and isnumeric(CLASS.SECTION) <> 0

    if (@SECTION is not null and isnumeric(@SECTION) <> 0)
    begin
        select @SECTION = convert(numeric(21,0), @SECTION) + 1
        if convert(numeric(21,0), @SECTION) < 10
            select @SECTION = '0' + @SECTION
    end
    else
    begin
        select @SECTION = '01'
    end

    return @SECTION
end