createprocedure.sql

这是1个数据库

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

/****** Object:  Stored Procedure dbo.Statisitics_Get_TeacherSupport    Script Date: 2003-5-31 17:34:10 ******/


/*每门课每位候选老师得票率*/
CREATE PROCEDURE Statisitics_Get_TeacherSupport
AS

BEGIN


	SELECT  Number,ClassName as CourseName,Teacher.[Name] as Teacher,Class.[Name] as [Name]

	FROM (( Class inner join Teacher
	on Teacher.[ID]=TeacherID ) left join(
	Select Count(*) As Number ,ClassID
		
	from	Selected_Class

	Group By ClassID) as b
	on Class.[ID]=ClassID)left join Classes

	on  Classes.[ID]=CourseID

	Order By ClassName


END

GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

/****** Object:  Stored Procedure dbo.StudentClass_Add_Class    Script Date: 2003-5-31 17:34:10 ******/
/*添加课程*/
CREATE PROCEDURE StudentClass_Add_Class(
	@classname varchar(30),
	@id 	int output,
	@retval int output
)
AS
Begin
	select @retval = 0	
	select @id=max([ID])+1 from Classes
	if(@id is NULL) Select @id = 1
	insert into Classes([ID],ClassName,Type) 
	values (@id,@classname,2)
	if(@@ROWCOUNT = 1 )
	begin
		select @retval = 1
	end
End

GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

/****** Object:  Stored Procedure dbo.StudentClass_Delete_Class    Script Date: 2003-5-31 17:34:10 ******/

/*删除学生*/
create procedure StudentClass_Delete_Class(
	@classid int
)
as
begin
	declare @classname varchar(30)
	Select @classname = ClassName from Classes
		Where [ID] = @classid and Type = 2
	Delete from Classes
		where [ID] = @classid and Type = 2
	Delete from Login
		where [UserID]	= any (Select Student.[ID]  from Student
		 where BelongingClass= @classname)
	Delete from Selected_Class
		where StudentID = any (Select Student.[ID]  from Student
		 where BelongingClass= @classname)
	Delete  from Student
		 where BelongingClass= @classname
end

GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

/****** Object:  Stored Procedure dbo.StudentClass_List_Class    Script Date: 2003-5-31 17:34:10 ******/
/*列出课程*/
create procedure StudentClass_List_Class
as
begin
	Select Classes.[ID],ClassName
	from Classes
	Where Classes.Type=2
end

GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

/****** Object:  Stored Procedure dbo.StudentSystem_Add_Class    Script Date: 2003-5-31 17:34:10 ******/

/*添加学生的课程*/
create procedure StudentSystem_Add_Class(
	@studentid nvarchar(20), /* HttpContext.Current.User.Identity.Name */
	@classid int,		/* 1  StudentSystem_Get_CourseList ClassID*/
	@retval int output 	/* 0 数据库中已有记录，1 成功 , 2 人数过多,3 选课过多,4 过期*/
)
as
begin
	declare @num int
	Select @num = Count(*) from Selected_Class 
	Where StudentID = @studentid
	if(@num  >= 5) 
	begin 
	Select @retval = 3
	return
	end 

	if exists( Select * from Selected_Class 
	Where StudentID = @studentid and ClassID = @classid)
	begin	
		select @retval = 0
		return 
	end

	if exists(Select * from Class
	where Class.[ID] = @classid and CourseID =
	 any( Select CourseID from Selected_Class ,Class
	Where StudentID = @studentid
	and Class.[ID] = ClassID))
	begin	
		select @retval = 0
		return 
	end

	Select @num = Count(*) from Selected_Class 
	Where ClassID = @classid
	declare @capicity int
	declare @time smalldatetime
	select @time=EndTime,@capicity = Capicity 
	from Class
	where [ID] = @classid
	if(@num  >= @capicity) 
	begin 
	Select @retval = 2
	return
	end 
	if(GetDate()> @time) 
	begin 
	Select @retval = 4
	return
	end 
	insert into Selected_Class (StudentID,ClassID) 
	values (@studentid,@classid)
	select @retval = 1
end

GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

/****** Object:  Stored Procedure dbo.StudentSystem_Delete_Class    Script Date: 2003-5-31 17:34:10 ******/

/*删除学生的课程*/
create procedure StudentSystem_Delete_Class(
	@studentid nvarchar(20),
	@classid int,
	@retval int output/*0 成功，1 选课时间过期,2 选课不能少于两门*/
)
as
begin
	declare @time smalldatetime
	select @time = EndTime from Class Where [ID]=@classid
	if(GetDate()> @time)
	begin
		Select @retval = 1
		return
	end
	declare @num int
	Select @num=Count(*)from Selected_Class Where StudentID = @studentid
	if(@num<=2) 
	begin
		Select @retval = 2
		return
	end
	delete from Selected_Class 
		where ClassID = @classid and StudentID = @studentid
	Select @retval=0
end

GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

/****** Object:  Stored Procedure dbo.StudentSystem_Get_ClassList    Script Date: 2003-5-31 17:34:10 ******/

/*得到学生可教的课程*/
create procedure StudentSystem_Get_ClassList(
	@studentid nvarchar(20)
)
as
begin
	Select Class.[ID] as ClassID,( ClassName + ' ' + RTRIM(Class.[Name])+':'+ Teacher.[Name] ) as ClassName
	from Class,Classes,Teacher
	Where Classes.[ID]=Class.CourseID
		and Teacher.[ID] = Class.TeacherID 
		and Classes.Type = 1
		and Capicity > (Select Count(*) from Selected_Class 
		Where ClassID = Class.[ID])
                and GetDate()<EndTime
-- 		and Class.[ID] != any(Select ClassID
-- 			 from Selected_Class 
-- 			where StudentID = @studentid)
	Order by ClassName
end

GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

/****** Object:  Stored Procedure dbo.StudentSystem_Get_StudentInfo    Script Date: 2003-5-31 17:34:10 ******/

/*查询学生的基本信息*/
CREATE PROCEDURE StudentSystem_Get_StudentInfo(
	@id nvarchar(20)
)
AS
BEGIN
	SELECT [ID],[Name],BelongingClass 
	FROM Student
	WHERE Student.[ID]=@id
END

GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

/****** Object:  Stored Procedure dbo.StudentSystem_List_Classes    Script Date: 2003-5-31 17:34:10 ******/

/*列出学生所有的课程*/
create procedure StudentSystem_List_Classes(
	@studentid nvarchar(20)
)
as
begin
	Select ClassID,Classes.ClassName as CourseName , Class.[Name] as ClassName ,Teacher.[Name] as TeacherName ,Info
	from Selected_Class ,Class,Classes ,Teacher, Enum
	where StudentID = @studentid	
	and Selected_Class.ClassID = Class.[ID]
	and Class.CourseID = Classes.[ID]
	and Class.TeacherID = Teacher.[ID]
	and Classes.Type = Enum.Type
end

GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

/****** Object:  Stored Procedure dbo.TeacherSystem_Add_Course    Script Date: 2003-5-31 17:34:10 ******/

/*添加教师的课程*/
create procedure TeacherSystem_Add_Course(
	@teacherid nvarchar(20), /* HttpContext.Current.User.Identity.Name */
	@courseid int,		/* 4  TeacherSystem_Get_CourseList CourseID*/
	@classname char(10),	/*用户输入*/
	@retval int output 	/* 0 数据库中已有记录，1 成功,2 选课过多*/
)
as
begin
	select @retval = 0
	if not exists(
		Select * from Course
		 where AvailableTeacher = @teacherid
			and CourseID = @courseid
	)return
	if exists
		(Select CourseID,TeacherID from Class 
		Where CourseID = @courseid and TeacherID = @teacherid 
		and [Name] = @classname)
	begin
	return
	end
	declare @num int
	Select @num = Count(*) from Class 
	Where TeacherID = @teacherid
	if(@num  >= 3) 
	begin 
	Select @retval = 2
	return
	end 
	declare @id int
	select @id = max([ID]) + 1 from Class 
	if(@id is NULL) Select @id = 1
	Insert into Class ([ID],[Name],CourseID,TeacherID)
	 values(@id,@classname,@courseid,@teacherid)
	select @retval = 1
end

GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

/****** Object:  Stored Procedure dbo.TeacherSystem_Delete_Class    Script Date: 2003-5-31 17:34:10 ******/

/*更新教师的课程*/
create procedure TeacherSystem_Delete_Class(
	@teacherid nvarchar(20),
	@classid int
)
as
begin
	if not exists(Select * from Class
		Where TeacherID = @teacherid and [ID] = @classid)
	return
	delete from Selected_Class where ClassID = @classid
	delete from Class 
		where [ID] = @classid
end

GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

/****** Object:  Stored Procedure dbo.TeacherSystem_Get_CourseList    Script Date: 2003-5-31 17:34:10 ******/

/*得到教师可教的课程*/
create procedure TeacherSystem_Get_CourseList(
	@teacherid nvarchar(20)
)
as
begin
	Select CourseID,ClassName
	from Course,Classes
	Where Course.AvailableTeacher = @teacherid
		and Classes.[ID]=Course.CourseID

end

GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

/****** Object:  Stored Procedure dbo.TeacherSystem_Get_TeacherInfo    Script Date: 2003-5-31 17:34:10 ******/

/*获得单个老师的基本信息*/
CREATE PROCEDURE TeacherSystem_Get_TeacherInfo(
	@id nvarchar(20)
)
AS
BEGIN
	SELECT [ID],[Name],Email,Prof 
	FROM Teacher
	WHERE Teacher.[ID]=@id
END

GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

/****** Object:  Stored Procedure dbo.TeacherSystem_List_Classes    Script Date: 2003-5-31 17:34:10 ******/

/*列出教师所有的课程*/
create procedure TeacherSystem_List_Classes(
	@teacherid nvarchar(20)
)
as
begin
	select Class.[ID] ,ClassName as CourseName ,[Name] as ClassName ,Info
	from Class,Classes,Enum
	where Class.[CourseID] = Classes.[ID]
		and TeacherID = @teacherid
		and Classes.Type = Enum.Type
end

GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO