USE [SDS_DevSchoolDistrict]
GO


ALTER FUNCTION [dbo].[SchoolYearsByStudent]
(@StudentId RoleId)
RETURNS @OutputTable TABLE
(
        SchoolYearId            int,
        SchoolYear              varchar(100),
        SchoolYearIsCurrent     bit DEFAULT (0)
)
AS
BEGIN   -- =================================================================================



        DECLARE @CurrentSchoolYearId int
        SET @CurrentSchoolYearId = dbo.SchoolYearIdValue(DEFAULT)

        ----------------------------------------
        --
        ----------------------------------------
        INSERT  @OutputTable(SchoolYearId, SchoolYear)
        SELECT  Y.SchoolYearId,
                Y.SchoolYear
        FROM    StudentSchoolYears S
        JOIN    SchoolYears Y
          ON    S.SchoolYearId = Y.SchoolYearId
        WHERE   S.StudentId = @StudentId
        ----------------------------------------



        UPDATE  @OutputTable
        SET     SchoolYearIsCurrent = 1
        WHERE   SchoolYearId = @CurrentSchoolYearId





RETURN
END     -- =================================================================================