CREATE PROCEDURE stu_AddBasicStudentInfo
(
   @studentNo char(6),
   @name varchar(20),
   @sex char(2),
   @departName varchar(20),
   @className varchar(50),
   @birthday datetime,
   @native varchar(50),
   @phone varchar(20),
   @address varchar(100),
   @zipCode varchar(8),
   @email varchar(50),
   @memo varchar(1000)
)
  
 AS
Declare @classNo int
Declare @departNo int
--無重復則添加
if not exists(select studentNo from Student where studentNo =@studentNo)
begin 
  -- 找出相應的departNO,classNo
select @departNO =(select code from Code_Depart where Name=@departName)
select @classNO=(select code from Code_Class where className=@className)
--if (@departNO!=NULL AND @classNO!=NULL)
insert into
  Student(StudentNo,name,sex,departNO,classNO,birthday,native,phone,address,zipCode,email,memo)
   VALUES
  (@studentNo,@name,@sex,@departNO,@classNO,@birthday,@native,@phone,@address,@zipCode,@email,@memo)
end

Go
CREATE PROCEDURE stu_AddChangeRecord
(   
     @studentNo char(6),
     @changeDes varchar(50),
     @memo varchar(1000)
    )
AS
Declare @changeCode char(1)
         select @changeCode=code from Code_Change
        where description=@changeDes
Declare @id int
      select @id=max(id) from Change

  insert Change(id,studentNo,changeNo,recordTime,memo)
    values(@id+1,@studentNo,@changeCode,getDate(),@memo)

Go
CREATE PROCEDURE stu_AddPunishRecord
(
@studentNo char(6),
@Des varchar(50),
@memo varchar(1000)
)

AS
DECLARE @Code char(1)
select @Code=code from Code_Punish
where description =@Des
declare @id int
select  @id=max(id) from Punishment
  insert Punishment(id,studentNo,levelNo,recordTime,memo)
values (@id+1,@studentNo,@Code,getDate(),@memo)

Go
CREATE PROCEDURE stu_AddRewardRecord
(
@studentNo char(6),
@rewardDes varchar(50),
@memo varchar(1000)
)
AS
DECLARE @rewardCode char(1)
select @rewardCode=code from Code_Reward
where description=@rewardDes
DECLARE @id int
select @id=max(id) from Reward
insert Reward(id,studentNo,levelNo,recordTime,memo)
values(@id+1,@studentNo,@rewardCode,getDate(),@memo)


Go
CREATE PROCEDURE stu_ChangeTypeAndDes
(
   @id char(1),
  @des varchar(50) output,
  @memo varchar(1000) output
)
AS
select @memo=memo,@des=Code_Change.description
from Change,Code_Change
where code=ChangeNo and id=@id


Go
CREATE PROCEDURE stu_DelStudent
(
    @stuNo char(6)
)

AS
begin
--先刪除Change
delete from Change
where studentNo=@stuNo
--刪除Reward
delete from Reward
where studentNo=@stuNo

--刪除Punish
delete from Punishment
where studentNo=@stuNo
delete from student
where studentNo=@stuNo
end

Go
CREATE PROCEDURE stu_GetAllStudent
 AS
--存在指定的學號
SElECT Student.studentNo AS 學號,
              Student.name as 姓名,
             Student.sex As 性別,
             Code_Depart.Name AS 所屬院系,
             Code_Class.className AS 班級,
             Student.birthday AS 出生日期,
             Student.native AS 籍貫,
             Student.phone AS 電話,
             Student.address AS 家庭住址,
             Student.zipCode AS 郵編,
             Student.email AS 電子郵件,
             Student.memo AS 備注
From Code_Depart INNER JOIN Student
ON Code_Depart.code=Student.departNo
INNER JOIN Code_Class
ON Student .classNO=code_class.code

Go
CREATE PROCEDURE stu_GetChange
(
@StuNo char(6)
)

 AS 
  SELECT Change.studentNo 學號,
                 Change.recordTime 記錄時間,
                Change.changeNo 記錄編號,
                Code_change.description 記錄描述
                From Change 
                        INNER JOIN Code_change
                          ON Change.changeNO=code_Change.code
                             where Change.studentNo=@StuNo


Go
CREATE PROCEDURE stu_GetChangeByNO 
(
@studentNo char(6)
)
AS 
--set nocount on
----------------------------------------------------------
SELECT id as 記錄編號,recordTime as 記錄時間
FROM Change 
 Where StudentNo=@studentNo

Go
CREATE PROCEDURE stu_GetChangeCode
AS
select *from Code_Change

Go
CREATE PROCEDURE stu_GetPunishmentByNO
(
@studentNo char(6)
)
 AS
------set nocount on
SELECT id as 記錄編號,recordTime as 記錄時間
From Punish
Where studentNo=@studentNo

Go

CREATE PROCEDURE stu_GetPunishCode  
AS
select *from Code_Punish
Go
CREATE PROCEDURE stu_GetRewardByNo
(@studentNo char(6)
)  
AS
---set noncount on
SELECT id as 記錄編號,recordTime as 記錄時間
FRom  Reward
Where studentNo=@studentNo

Go
CREATE PROCEDURE stu_GetRewardCode
 AS
select *from Code_Reward

Go
CREATE PROCEDURE stu_GetStuSummary
(
  @stuNo char(6),
  @stuName varchar(20) output,
  @departName varchar(50) output,
  @className varchar(50)output
)

 AS
DECLARE @departNo int
DECLARE @classNo char(4)
select @stuName=name,@departNo=departNo,@classNo=classNo
from Student
where StudentNo=@stuNo

select @departName=(select Name from Code_Depart where code=@departNo)
select @className=(select className from Code_Class where code=@classNo)

Go
CREATE PROCEDURE stu_GetStuSummary2
(
@stuNo char(6) ,
@Name varchar( 20) output,
@sex char (2) output,
@birthday datetime output,
@native varchar(50)output,
@departName varchar (50) output,
@className varchar (50) output
)

 AS
DeClare @departNo int
DeClare @classNo char(4)
select @Name=name,@sex=sex,@birthday=birthday,@native=native,@departNo=departNo,@classNo=classNo
from Student 
where studentNo=@stuNo

select @departName=(select Name from Code_Depart where code=@departNo)
select @className=(select className from Code_Class where code=@ClassNo)
Go
CREATE PROCEDURE stu_UpdateAllStudent 
(
 @stuNo char(6),
@name varchar(20),
@sex char(2) ,
@departName varchar(50),
@className varchar(50),
@birthday  datetime,
@native varchar(50),
@phone char(20),
@address varchar(100),
@zipCode char(8),
@email varchar(50),
@memo varchar(1000)
)

  
 AS
DeClare @departNo int
Declare @classNo char(4)

select @departNo=code
from Code_Depart
where Name=@departName
select @classNo=code
from code_class
where className=@className
Update Student set
name=@name,
sex=@sex,
departNo=@departNo,
classNo=@classNo,
birthday=@birthday,
native=@native,
phone=@phone,
address=@address,
zipCode=@zipCode,
email=@email,
memo=@memo
where studentNo=@stuNo
GO