getscoresbyclassid.sql

来自「一个别人编写的学生管理系统,开以看下」· SQL 代码 · 共 29 行

IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'GetScoresByClassId')
	BEGIN
		DROP  Procedure  GetScoresByClassId
	END

GO

CREATE Procedure GetScoresByClassId
@ClassId int
AS

SELECT T_Student.StudentName AS 学生姓名, T_Class.ClassName AS 班级, 
      T_Subject.SubjectName AS 科目, T_Score.Score AS 成绩
FROM T_Score INNER JOIN
      T_Student ON T_Score.StudentId = T_Student.StudentId INNER JOIN
      T_Course ON T_Score.CourseId = T_Course.CourseId INNER JOIN
      T_Subject ON T_Course.SubjectId = T_Subject.SubjectId INNER JOIN
      T_Class ON T_Student.ClassId = T_Class.ClassId
WHERE (T_Class.ClassId = @ClassId) AND (T_Score.States = 1) AND 
      (T_Student.States = 1) AND (T_Course.States = 1) AND (T_Class.States = 1)
GO

/*
GRANT EXEC ON GetScoresByClassId TO PUBLIC

GO
*/