第七章：存储过程

--上机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'