ACCP7.0优化myschool数据库设计第七章

--上机1
exec sp_help student
exec sp_columns student
exec sp_helpconstraint student

--上机2
if exists(select * from sysobjects where name='usp_getsubject')
drop procedure usp_getsubject
go
create procedure usp_getsubject
as
select gradename,subjectname,classhour from Subject
inner join Grade on Grade.GradeId=Subject.GradeId
order by GradeName
go
exec usp_getsubject

--上机3
if exists(select * from sysobjects where name='usp_getsubject')
drop procedure usp_getsubject
go
create procedure usp_getsubject
@grade nvarchar(50)
as
select gradename,subjectname,classhour from Subject
inner join Grade on Grade.GradeId=Subject.GradeId
where GradeName=@grade
order by GradeName
go
exec usp_getsubject 'S1'
exec usp_getsubject 'S2'
exec usp_getsubject 'Y2'

--上机4
if exists(select * from sysobjects where name='usp_getsubject')
drop procedure usp_getsubject
go
create procedure usp_getsubject
@classnum int output,
@classhour int output,
@grade nvarchar(50)
as
select gradename,subjectname,classhour from Subject
inner join Grade on Grade.GradeId=Subject.GradeId
where Gradename=@grade
order by GradeName
print '学期名称 课程数目 总课时'
print @grade+''+cast(@classnum as nvarchar(50))+''+cast(@classhour as nvarchar(50))
go
declare @classnum int
declare @classhour int
exec usp_getsubject @classnum output,@classhour output,'S1'
exec usp_getsubject @classnum output,@classhour output,'S2'
exec usp_getsubject @classnum output,@classhour output,'Y2'