第十二章

create procedure UP_TEACHER_INFO
as
select * from Teacher where Teacher_Sex='男'
go

create procedure UP_course_info
@scname varchar(30)
as
select Student.Student_No,Student_Name,Course_Name,SelectCourse_Score
from Student,SelectCourse,Course
where Student.Student_No=SelectCourse.SelectCourse_StudentNo
and SelectCourse.SelectCourse_CourseNo=Course.Course_No
and Course_Name=@scname
go

create procedure UP_course_count
@scname varchar(30),@ccount int output
as
select @ccount=count(*)
from SelectCourse,Course
where SelectCourse.SelectCourse_CourseNo=Course.Course_No
and Course_Name=@scname
go

exec UP_TEACHER_INFO
go

declare @ccount int
exec UP_course_count @scname='数据结构',@ccount=@ccount output
select '选修数据结构课程的人数：',@ccount

exec UP_course_info'数据结构'
go
 
declare @ccount int
exec UP_course_count'数据结构',@ccount output
select '选修数据结构课程的人数：',@ccount
go

exec sp_helptext up_course_info
exec sp_help up_course_info
exec sp_depends up_course_info
exec sp_stored_procedures up_course_info
go

alter procedure UP_course_info
@s_no char(6)
as
select Student.Student_No,Student_Name,Course_Name,SelectCourse_Score
from Student,SelectCourse,Course
where Student.Student_No=SelectCourse.SelectCourse_StudentNo
and SelectCourse.SelectCourse_CourseNo=Course.Course_No
and Student.Student_No=@s_no
go

drop procedure UP_TEACHER_INFO
go




alter procedure UP_course_info
@s_no char(6)=null
as
if @s_no is null
    begin
        select Student.Student_No,Student_Name,Course_Name,SelectCourse_Score
        from Student,SelectCourse,Course
        where Student.Student_No=SelectCourse.SelectCourse_StudentNo
        and SelectCourse.SelectCourse_CourseNo=Course.Course_No
    end
else
    begin
        select Student.Student_No,Student_Name,Course_Name,SelectCourse_Score
        from Student,SelectCourse,Course
        where Student.Student_No=SelectCourse.SelectCourse_StudentNo
        and SelectCourse.SelectCourse_CourseNo=Course.Course_No
        and Student.Student_No=@s_no
    end
go


exec UP_course_info
exec UP_course_info'201901'