UFN_STUDENTPROGRESSION_INUSE

        CREATE function dbo.UFN_STUDENTPROGRESSION_INUSE(
            @STUDENTPROGRESSIONID uniqueidentifier
        )
        returns bit
        with execute as caller
        as begin
            declare @RETVAL bit = 0;
            --add any tables that use academic year dates


            --see if any classes use the student progression

            if (select 1
                from dbo.STUDENTPROGRESSION
                    inner join dbo.EDUCATIONALHISTORY on dbo.STUDENTPROGRESSION.ENROLLMENTID = EDUCATIONALHISTORY.ID
                    inner join dbo.CLASSMEETINGGROUP on
                        dbo.STUDENTPROGRESSION.STARTDATE <= CLASSMEETINGGROUP.STARTDATE and
                        dbo.STUDENTPROGRESSION.ENDDATE >= CLASSMEETINGGROUP.ENDDATE
                    inner join dbo.STUDENTCLASSMEETINGGROUP on CLASSMEETINGGROUP.ID = STUDENTCLASSMEETINGGROUP.CLASSMEETINGGROUPID
                    inner join dbo.STUDENTCOURSE on
                        STUDENTCLASSMEETINGGROUP.STUDENTCOURSEID = STUDENTCOURSE.ID and
                        EDUCATIONALHISTORY.CONSTITUENTID = STUDENTCOURSE.STUDENTID
                where
                    dbo.STUDENTPROGRESSION.ID = @STUDENTPROGRESSIONID) > 0
                set @RETVAL = 1;

            --see if there are any charges use the student progression

            if (exists (select dbo.STUDENTCHARGE.ID
                from dbo.STUDENTCHARGE
                where
                    dbo.STUDENTCHARGE.STUDENTPROGRESSIONID = @STUDENTPROGRESSIONID))
                set @RETVAL = 1;

            --see if this is the last student progression

            if @RETVAL = 0
             begin
                declare @STUDENTID as uniqueidentifier
                set @STUDENTID = (SELECT dbo.EDUCATIONALHISTORY.[CONSTITUENTID] FROM dbo.STUDENTPROGRESSION INNER JOIN dbo.EDUCATIONALHISTORY on dbo.STUDENTPROGRESSION.[ENROLLMENTID] = dbo.EDUCATIONALHISTORY.[ID]  WHERE dbo.STUDENTPROGRESSION.[ID] = @STUDENTPROGRESSIONID) 
                if (dbo.UFN_STUDENTPROGRESSION_RECORDCOUNT(@STUDENTID) <= 1)    
                    set @RETVAL = 1;
             end

            return @RETVAL;
        end