getscoresbystudentid.sql

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

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

GO

CREATE Procedure GetScoresByStudentId
@SubjectId 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_Subject.SubjectId = @SubjectId) AND (T_Score.States = 1) AND 
      (T_Student.States = 1) AND (T_Course.States = 1) AND (T_Subject.State = 1) AND 
      (T_Class.States = 1)

GO

/*
GRANT EXEC ON GetScoresByStudentId TO PUBLIC

GO
*/