createprocedure.sql

这是1个数据库

use park 
go

/****** Object:  Stored Procedure dbo.Check_Content_Right    Script Date: 2003-5-31 17:34:09 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Check_Content_Right]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[Check_Content_Right]
GO

/****** Object:  Stored Procedure dbo.ClassSystem_Change_ClassStatus    Script Date: 2003-5-31 17:34:09 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[ClassSystem_Change_ClassStatus]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[ClassSystem_Change_ClassStatus]
GO

/****** Object:  Stored Procedure dbo.ClassSystem_Delete_Class    Script Date: 2003-5-31 17:34:09 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[ClassSystem_Delete_Class]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[ClassSystem_Delete_Class]
GO

/****** Object:  Stored Procedure dbo.ClassSystem_Get_ClassStatus    Script Date: 2003-5-31 17:34:09 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[ClassSystem_Get_ClassStatus]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[ClassSystem_Get_ClassStatus]
GO

/****** Object:  Stored Procedure dbo.ClassSystem_List_Classes    Script Date: 2003-5-31 17:34:09 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[ClassSystem_List_Classes]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[ClassSystem_List_Classes]
GO

/****** Object:  Stored Procedure dbo.CourseSystem_Add_Course    Script Date: 2003-5-31 17:34:09 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[CourseSystem_Add_Course]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[CourseSystem_Add_Course]
GO

/****** Object:  Stored Procedure dbo.CourseSystem_Add_Teacher    Script Date: 2003-5-31 17:34:09 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[CourseSystem_Add_Teacher]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[CourseSystem_Add_Teacher]
GO

/****** Object:  Stored Procedure dbo.CourseSystem_Change_CourseStatus    Script Date: 2003-5-31 17:34:09 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[CourseSystem_Change_CourseStatus]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[CourseSystem_Change_CourseStatus]
GO

/****** Object:  Stored Procedure dbo.CourseSystem_Change_StudentStatus    Script Date: 2003-5-31 17:34:09 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[CourseSystem_Change_StudentStatus]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[CourseSystem_Change_StudentStatus]
GO

/****** Object:  Stored Procedure dbo.CourseSystem_Change_TeacherStatus    Script Date: 2003-5-31 17:34:09 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[CourseSystem_Change_TeacherStatus]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[CourseSystem_Change_TeacherStatus]
GO

/****** Object:  Stored Procedure dbo.CourseSystem_Delete_Course    Script Date: 2003-5-31 17:34:09 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[CourseSystem_Delete_Course]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[CourseSystem_Delete_Course]
GO

/****** Object:  Stored Procedure dbo.CourseSystem_Delete_Teacher    Script Date: 2003-5-31 17:34:09 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[CourseSystem_Delete_Teacher]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[CourseSystem_Delete_Teacher]
GO

/****** Object:  Stored Procedure dbo.CourseSystem_Get_CourseStatus    Script Date: 2003-5-31 17:34:09 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[CourseSystem_Get_CourseStatus]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[CourseSystem_Get_CourseStatus]
GO

/****** Object:  Stored Procedure dbo.CourseSystem_Get_StudentStatus    Script Date: 2003-5-31 17:34:09 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[CourseSystem_Get_StudentStatus]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[CourseSystem_Get_StudentStatus]
GO

/****** Object:  Stored Procedure dbo.CourseSystem_Get_TeacherList    Script Date: 2003-5-31 17:34:09 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[CourseSystem_Get_TeacherList]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[CourseSystem_Get_TeacherList]
GO

/****** Object:  Stored Procedure dbo.CourseSystem_Get_TeacherStatus    Script Date: 2003-5-31 17:34:09 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[CourseSystem_Get_TeacherStatus]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[CourseSystem_Get_TeacherStatus]
GO

/****** Object:  Stored Procedure dbo.CourseSystem_List_Courses    Script Date: 2003-5-31 17:34:09 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[CourseSystem_List_Courses]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[CourseSystem_List_Courses]
GO

/****** Object:  Stored Procedure dbo.CourseSystem_List_Teacher    Script Date: 2003-5-31 17:34:09 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[CourseSystem_List_Teacher]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[CourseSystem_List_Teacher]
GO

/****** Object:  Stored Procedure dbo.Help_Get_HelpFile    Script Date: 2003-5-31 17:34:09 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Help_Get_HelpFile]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[Help_Get_HelpFile]
GO

/****** Object:  Stored Procedure dbo.Login_Get_Role    Script Date: 2003-5-31 17:34:09 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Login_Get_Role]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[Login_Get_Role]
GO

/****** Object:  Stored Procedure dbo.Login_Get_Roles    Script Date: 2003-5-31 17:34:09 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Login_Get_Roles]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[Login_Get_Roles]
GO

/****** Object:  Stored Procedure dbo.Login_Get_Task    Script Date: 2003-5-31 17:34:09 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Login_Get_Task]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[Login_Get_Task]
GO

/****** Object:  Stored Procedure dbo.Login_Get_UserView    Script Date: 2003-5-31 17:34:09 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Login_Get_UserView]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[Login_Get_UserView]
GO

/****** Object:  Stored Procedure dbo.Login_Verify_Password    Script Date: 2003-5-31 17:34:09 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Login_Verify_Password]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[Login_Verify_Password]
GO

/****** Object:  Stored Procedure dbo.Login_Verify_PasswordNull    Script Date: 2003-5-31 17:34:09 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Login_Verify_PasswordNull]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[Login_Verify_PasswordNull]
GO

/****** Object:  Stored Procedure dbo.Member_Add_Student    Script Date: 2003-5-31 17:34:09 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Member_Add_Student]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[Member_Add_Student]
GO

/****** Object:  Stored Procedure dbo.Member_Add_Teacher    Script Date: 2003-5-31 17:34:09 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Member_Add_Teacher]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[Member_Add_Teacher]
GO

/****** Object:  Stored Procedure dbo.Member_Delete_Student    Script Date: 2003-5-31 17:34:09 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Member_Delete_Student]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[Member_Delete_Student]
GO

/****** Object:  Stored Procedure dbo.Member_Delete_Teacher    Script Date: 2003-5-31 17:34:09 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Member_Delete_Teacher]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[Member_Delete_Teacher]
GO

/****** Object:  Stored Procedure dbo.Member_List_Student    Script Date: 2003-5-31 17:34:09 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Member_List_Student]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[Member_List_Student]
GO

/****** Object:  Stored Procedure dbo.Member_List_Teacher    Script Date: 2003-5-31 17:34:09 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Member_List_Teacher]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[Member_List_Teacher]
GO

/****** Object:  Stored Procedure dbo.Member_Student_GetClasses    Script Date: 2003-5-31 17:34:09 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Member_Student_GetClasses]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[Member_Student_GetClasses]
GO

/****** Object:  Stored Procedure dbo.Member_Teacher_GetProfList    Script Date: 2003-5-31 17:34:09 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Member_Teacher_GetProfList]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[Member_Teacher_GetProfList]
GO

/****** Object:  Stored Procedure dbo.Member_Update_Student    Script Date: 2003-5-31 17:34:09 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Member_Update_Student]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[Member_Update_Student]
GO

/****** Object:  Stored Procedure dbo.Member_Update_Teacher    Script Date: 2003-5-31 17:34:09 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Member_Update_Teacher]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[Member_Update_Teacher]
GO

/****** Object:  Stored Procedure dbo.Security_Set_Password    Script Date: 2003-5-31 17:34:09 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Security_Set_Password]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[Security_Set_Password]
GO

/****** Object:  Stored Procedure dbo.Statisitics_Get_BelowTwoStudent    Script Date: 2003-5-31 17:34:09 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Statisitics_Get_BelowTwoStudent]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[Statisitics_Get_BelowTwoStudent]
GO

/****** Object:  Stored Procedure dbo.Statisitics_Get_ClassByCourseID    Script Date: 2003-5-31 17:34:09 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Statisitics_Get_ClassByCourseID]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[Statisitics_Get_ClassByCourseID]
GO

/****** Object:  Stored Procedure dbo.Statisitics_Get_Course    Script Date: 2003-5-31 17:34:09 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Statisitics_Get_Course]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[Statisitics_Get_Course]
GO

/****** Object:  Stored Procedure dbo.Statisitics_Get_SelectedClassNumber    Script Date: 2003-5-31 17:34:09 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Statisitics_Get_SelectedClassNumber]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[Statisitics_Get_SelectedClassNumber]
GO

/****** Object:  Stored Procedure dbo.Statisitics_Get_StudentByClassID    Script Date: 2003-5-31 17:34:09 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Statisitics_Get_StudentByClassID]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[Statisitics_Get_StudentByClassID]
GO

/****** Object:  Stored Procedure dbo.Statisitics_Get_Teacher    Script Date: 2003-5-31 17:34:09 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Statisitics_Get_Teacher]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[Statisitics_Get_Teacher]
GO

/****** Object:  Stored Procedure dbo.Statisitics_Get_TeacherCourse    Script Date: 2003-5-31 17:34:09 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Statisitics_Get_TeacherCourse]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[Statisitics_Get_TeacherCourse]
GO

/****** Object:  Stored Procedure dbo.Statisitics_Get_TeacherSupport    Script Date: 2003-5-31 17:34:09 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Statisitics_Get_TeacherSupport]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[Statisitics_Get_TeacherSupport]
GO

/****** Object:  Stored Procedure dbo.StudentClass_Add_Class    Script Date: 2003-5-31 17:34:09 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[StudentClass_Add_Class]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[StudentClass_Add_Class]
GO

/****** Object:  Stored Procedure dbo.StudentClass_Delete_Class    Script Date: 2003-5-31 17:34:09 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[StudentClass_Delete_Class]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[StudentClass_Delete_Class]
GO

/****** Object:  Stored Procedure dbo.StudentClass_List_Class    Script Date: 2003-5-31 17:34:09 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[StudentClass_List_Class]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[StudentClass_List_Class]
GO

/****** Object:  Stored Procedure dbo.StudentSystem_Add_Class    Script Date: 2003-5-31 17:34:09 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[StudentSystem_Add_Class]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[StudentSystem_Add_Class]
GO

/****** Object:  Stored Procedure dbo.StudentSystem_Delete_Class    Script Date: 2003-5-31 17:34:09 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[StudentSystem_Delete_Class]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[StudentSystem_Delete_Class]
GO

/****** Object:  Stored Procedure dbo.StudentSystem_Get_ClassList    Script Date: 2003-5-31 17:34:09 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[StudentSystem_Get_ClassList]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[StudentSystem_Get_ClassList]
GO

/****** Object:  Stored Procedure dbo.StudentSystem_Get_StudentInfo    Script Date: 2003-5-31 17:34:09 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[StudentSystem_Get_StudentInfo]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[StudentSystem_Get_StudentInfo]
GO

/****** Object:  Stored Procedure dbo.StudentSystem_List_Classes    Script Date: 2003-5-31 17:34:09 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[StudentSystem_List_Classes]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[StudentSystem_List_Classes]
GO

/****** Object:  Stored Procedure dbo.TeacherSystem_Add_Course    Script Date: 2003-5-31 17:34:09 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TeacherSystem_Add_Course]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[TeacherSystem_Add_Course]
GO

/****** Object:  Stored Procedure dbo.TeacherSystem_Delete_Class    Script Date: 2003-5-31 17:34:09 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TeacherSystem_Delete_Class]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[TeacherSystem_Delete_Class]
GO

/****** Object:  Stored Procedure dbo.TeacherSystem_Get_CourseList    Script Date: 2003-5-31 17:34:09 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TeacherSystem_Get_CourseList]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[TeacherSystem_Get_CourseList]
GO

/****** Object:  Stored Procedure dbo.TeacherSystem_Get_TeacherInfo    Script Date: 2003-5-31 17:34:09 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TeacherSystem_Get_TeacherInfo]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[TeacherSystem_Get_TeacherInfo]
GO

/****** Object:  Stored Procedure dbo.TeacherSystem_List_Classes    Script Date: 2003-5-31 17:34:09 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TeacherSystem_List_Classes]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[TeacherSystem_List_Classes]
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

/****** Object:  Stored Procedure dbo.Check_Content_Right    Script Date: 2003-5-31 17:34:09 ******/
/*检查权限*/
create procedure Check_Content_Right(
	@role nvarchar(20),
	@content varchar(50)
)
as
begin
	select Content,[Help],Role,NextContent from Content 
	where Role = @role and Content=@content
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.ClassSystem_Change_ClassStatus    Script Date: 2003-5-31 17:34:09 ******/
/*删除可教课教师*/
create procedure ClassSystem_Change_ClassStatus(
	@id int,
	@classname char(10),
	@capicity int,
	@endtime smalldatetime
)
as
begin
	Update Class
	set [Name]= @classname, Capicity=@capicity,EndTime=@endtime
	where [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.ClassSystem_Delete_Class    Script Date: 2003-5-31 17:34:09 ******/
/*删除可教课教师*/
create procedure ClassSystem_Delete_Class(
	@id int
)
as
begin
	Delete from Selected_Class
		where ClassID = @id
	Delete from Class
		where [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.ClassSystem_Get_ClassStatus    Script Date: 2003-5-31 17:34:09 ******/
/*删除可教课教师*/
create procedure ClassSystem_Get_ClassStatus(
	@id int,
	@classname char(10) output,
	@coursename varchar(30) output,
	@teachername char(10) output,
	@capicity int output,
	@endtime smalldatetime output
)
as
begin
	select @classname = Class.[Name],@coursename=ClassName,
	@teachername = Teacher.[Name],@capicity=Capicity,@endtime=EndTime
	from Class,Teacher,Classes
	where Class.CourseID = Classes.[ID] and Class.TeacherID = Teacher.[ID]
		and Class.[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.ClassSystem_List_Classes    Script Date: 2003-5-31 17:34:09 ******/
/*删除可教课教师*/
create procedure ClassSystem_List_Classes
as
begin
	select Class.[ID],Class.[Name] as ClassName,ClassName as CourseName,
	Teacher.[Name] as TeacherName,Email,Capicity,EndTime
	from Class,Teacher,Classes
	where Class.CourseID = Classes.[ID] and Class.TeacherID = Teacher.[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.CourseSystem_Add_Course    Script Date: 2003-5-31 17:34:09 ******/


/*添加课程*/
CREATE PROCEDURE CourseSystem_Add_Course(
	@coursename 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,@coursename,1)
	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.CourseSystem_Add_Teacher    Script Date: 2003-5-31 17:34:09 ******/
/*添加可教课教师*/
create procedure CourseSystem_Add_Teacher(
	@courseid int,
	@teacherid nvarchar(20)
)
as
begin
	if not exists(select * from Course
	 where CourseID = @courseid and AvailableTeacher=@teacherid)
	insert into Course(CourseID,AvailableTeacher)values(@courseid,@teacherid)
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.CourseSystem_Change_CourseStatus    Script Date: 2003-5-31 17:34:09 ******/
/*更改选课状态*/
create procedure CourseSystem_Change_CourseStatus(
	@courseid int
)
as
begin
	declare @type int