第七章上机

USE MySchool  
GO  
EXEC sp_columns Student  --查看表Student中列的信息  
EXEC sp_help Student  --查看表Student的所有信息  
EXEC sp_helpconstraint Student --查看表Student的约束  
  
  
  
  
/*---创建存储过程----*/  
CREATE PROCEDURE usp_grade_subject  
AS   
   SELECT GradeName,SubjectName,ClassHour FROM Grade   
   INNER JOIN Subject  
   ON Grade.GradeId=Subject.GradeId  
   ORDER BY Subject.GradeId,SubjectNo  
GO  
/*---调用执行存储过程---*/  
EXEC usp_grade_subject   
  
  
  
  
  
CREATE PROCEDURE usp_query_subject  
  @GradeName VARCHAR(50) = NULL  
AS   
  IF @GradeName IS NULL  
    SELECT GradeName,SubjectName,ClassHour FROM Grade   
    LEFT JOIN Subject   ON Grade.GradeId=Subject.GradeId   
    UNION   
    SELECT GradeName,' ',SUM(ClassHour)FROM Grade  
    LEFT JOIN Subject  ON Grade.GradeId=Subject.GradeId   
    GROUP BY GradeName  
  ELSE  
    SELECT GradeName,SubjectName,ClassHour FROM Grade  
    LEFT JOIN Subject  ON Grade.GradeId=Subject.GradeId   
    WHERE GradeName=@GradeName  
    UNION  
    SELECT GradeName,' ',SUM(ClassHour)FROM Grade  
    LEFT JOIN Subject  ON Grade.GradeId=Subject.GradeId   
    WHERE GradeName=@GradeName  
    GROUP BY GradeName  
GO  
  
  
EXEC usp_query_subject 's2'  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
CREATE PROCEDURE usp_query_subject  
   @CourseNum INT OUTPUT,    
   @HourNum INT OUTPUT,    
   @GradeName VARCHAR(50)  
AS   
    IF LEN(@GradeName) = 0  
       BEGIN  
          PRINT '学期名称不能为空'  
          RETURN  
       END  
    PRINT '---------学期课程信息如下------------'  
    SELECT GradeName,SubjectName,ClassHour FROM Grade   
    LEFT JOIN Subject ON Grade.GradeId=Subject.GradeId   
    WHERE GradeName=@GradeName  
  
    SELECT @CourseNum=COUNT(0), @HourNum=SUM(ClassHour)  
    FROM  Grade  
    INNER JOIN Subject ON Grade.GradeId=Subject.GradeId   
    WHERE GradeName=@GradeName  
GO