CREATE   PROC SP_SHYONG_SYEAR
  ( @SYEAR   CHAR(2)
  , @SEME    CHAR(1)
  , @SNO     CHAR(7)
) AS BEGIN

  DECLARE @SQL CHAR(255)

  SET NOCOUNT ON

  -- new create by sarah.
  CREATE TABLE #T
    (
        STDNO   	CHAR (8)	NOT NULL
      , SUBJNO 		CHAR(4)		NULL
      , SUBJNAME 	VARCHAR(20) 	NULL
      , CREDIT1		FLOAT		NULL
      , SCORE1		CHAR(5)		NULL
      , SCORE1_EXAM		CHAR(5)		NULL
      , CREDIT2		FLOAT		NULL
      , SCORE2		CHAR(5)		NULL
      , SCORE2_EXAM		CHAR(5)		NULL
      , SCORE3		CHAR(5)		NULL
      , SCORE3_EXAM		CHAR(5)		NULL
      , SUBJ3       	CHAR(3)     	NULL
      , CRED_TOT	SMALLINT	NULL
      , SCO_6		REAL		NULL
   )
 

  IF @SEME="1" BEGIN
    INSERT INTO #T ( STDNO ,SUBJ3) 
      SELECT DISTINCT STDNO , SUBSTRING(SUBJNO,1,3)
       FROM BSEME_SCORE WHERE 0 = 0
      AND SYEAR = @SYEAR 
      AND SEME = '1'
      AND STDNO=@SNO
    
    INSERT INTO #T (STDNO,SUBJNO,CRED_TOT,SCO_6)
      SELECT STDNO, 'ZZZ'+SEME , CRED_TOT, ROUND(SCO_6,2)
       FROM BSTD_SEME WHERE 0 = 0
       AND SYEAR = @SYEAR
       AND SEME = '1'
       AND STDNO = @SNO
  END

  IF @SEME="2" BEGIN

    INSERT INTO #T (STDNO,SUBJ3)
    SELECT DISTINCT STDNO,SUBSTRING(SUBJNO,1,3)
    FROM BSEME_SCORE
    WHERE SYEAR=@SYEAR
        AND STDNO=@SNO

    INSERT INTO #T (STDNO,SUBJNO,CRED_TOT,SCO_6)
       SELECT STDNO, 'ZZZ'+SEME , CRED_TOT, ROUND(SCO_6,2)
       FROM BSTD_SEME WHERE 0 = 0
       AND SYEAR = @SYEAR
       AND STDNO = @SNO

    INSERT INTO #T (STDNO,SUBJNO,CRED_TOT,SCO_6)
       SELECT STDNO, 'ZZZ3' , CRED_TOT, ROUND(SCO_6,2)
       FROM BSTD_SYEAR WHERE 0 = 0
       AND SYEAR = @SYEAR
       AND STDNO = @SNO
    

-- SELECT * FROM #T
  END
 
  UPDATE #T SET CREDIT1 = A.CREDIT , SCORE1 = A.SCORE_P , SCORE1_EXAM=A.SCORE_EXAM
    , #T.SUBJNO=A.SUBJNO
    FROM BSEME_SCORE A WHERE 0 = 0
    AND A.SYEAR = @SYEAR 
    AND SEME='1' 
    AND A.STDNO=#T.STDNO 
    AND SUBSTRING(A.SUBJNO,1,3)=#T.SUBJ3

  IF @SEME = "2" BEGIN
    UPDATE #T SET CREDIT2=A.CREDIT,SCORE2=A.SCORE_P, SCORE2_EXAM=A.SCORE_EXAM
    FROM BSEME_SCORE A WHERE 0 = 0
    AND SYEAR = @SYEAR 
    AND SEME='2'
    AND A.STDNO=#T.STDNO
    AND SUBSTRING(A.SUBJNO,1,3) = #T.SUBJ3

    UPDATE #T SET CREDIT2=A.CREDIT,SCORE2=A.SCORE_P, SCORE2_EXAM=A.SCORE_EXAM
    , #T.SUBJNO=A.SUBJNO
    FROM BSEME_SCORE A WHERE 0 = 0
    AND SYEAR = @SYEAR 
    AND SEME='2'
    AND A.STDNO=#T.STDNO
    AND #T.SUBJNO IS NULL
    AND SUBSTRING(A.SUBJNO,1,3) = #T.SUBJ3

  END

  UPDATE #T SET SCORE3 = A.SCORE_P, SCORE3_EXAM=A.SCORE_EXAM
    FROM BSYEAR_SCORE A WHERE 0 = 0
    AND SYEAR = @SYEAR 
    AND A.STDNO=#T.STDNO
    AND SUBSTRING(A.SUBJNO,1,3)= #T.SUBJ3

  UPDATE #T SET SUBJNAME=A.ABBR_NAME+''
    FROM BSUBJECT A WHERE 0 = 0
    AND A.SUBJNO=#T.SUBJNO

  SELECT * FROM #T  ORDER BY SUBJNO
END
GO
