createprocedure.sql

这是1个数据库

	select @type=Type from Classes
	where  Classes.[ID]=@courseid
	if @type=3 
	update Classes Set Type=1 Where Classes.[ID]=@courseid
	else if @type=1
 	update Classes Set Type=3 Where Classes.[ID]=@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.CourseSystem_Change_StudentStatus    Script Date: 2003-5-31 17:34:09 ******/
/*更改学生选课状态*/
create procedure CourseSystem_Change_StudentStatus
as
begin
	declare @secondindex varchar(50)
	select @secondindex = SecondIndex from UserView Where Role = 'Student'
	if @secondindex = 'StudentSpecialTask'
	update UserView Set SecondIndex=null,Content = 'CourseSystem/StopSelecting.ascx' Where Role = 'Student' 
	else 
	update UserView Set SecondIndex='StudentSpecialTask',Content = 'Security/welcome.ascx' Where Role = 'Student'
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_TeacherStatus    Script Date: 2003-5-31 17:34:09 ******/
/*更改教师选课状态*/
create procedure CourseSystem_Change_TeacherStatus
as
begin
	declare @secondindex varchar(50)
	select @secondindex = SecondIndex from UserView Where Role = 'Teacher'
	if @secondindex = 'TeacherSpecialTask'
	update UserView Set SecondIndex=null,Content = 'CourseSystem/StopSelecting.ascx' Where Role = 'Teacher' 
	else 
	update UserView Set SecondIndex='TeacherSpecialTask',Content = 'Security/welcome.ascx' Where Role = 'Teacher'
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_Delete_Course    Script Date: 2003-5-31 17:34:09 ******/

/*删除学生*/
create procedure CourseSystem_Delete_Course(
	@courseid varchar(30)
)
as
begin
	declare @classid int
	select @classid=[ID] from Class 
	where CourseID=@courseid
	Delete from Course
		Where CourseID=@courseid
	Delete from Selected_Class
		Where ClassID=@classid
	Delete from Class
		where CourseID = @courseid
	Delete from Classes
		where [ID] = @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.CourseSystem_Delete_Teacher    Script Date: 2003-5-31 17:34:09 ******/
/*删除可教课教师*/
create procedure CourseSystem_Delete_Teacher(
	@courseid int,
	@teacherid nvarchar(20)
)
as
begin
	delete from Course
	where CourseID = @courseid and AvailableTeacher=@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_Get_CourseStatus    Script Date: 2003-5-31 17:34:09 ******/
/*得到选课状态*/
create procedure CourseSystem_Get_CourseStatus(
	@courseid int,
	@coursename varchar(30) output,
	@coursestatus varchar(50) output
)
as
begin
	select @coursestatus=Info,@coursename=ClassName from Enum,Classes
	where Classes.Type=Enum.Type And Classes.[ID]=@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.CourseSystem_Get_StudentStatus    Script Date: 2003-5-31 17:34:09 ******/
/*得到学生选课状态*/
create procedure CourseSystem_Get_StudentStatus(
	@message varchar(50) output
)
as
begin
	declare @secondindex varchar(50)
	select @secondindex = SecondIndex from UserView Where Role = 'Student'
	if @secondindex = 'StudentSpecialTask'
	select @message = '可以选课'
	else 
	select @message = '停止选课'
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_Get_TeacherList    Script Date: 2003-5-31 17:34:09 ******/
/*得到学生班级*/
create procedure CourseSystem_Get_TeacherList
as
begin
	select [ID],[Name] from Teacher 
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_Get_TeacherStatus    Script Date: 2003-5-31 17:34:09 ******/
/*得到教师选课状态*/
create procedure CourseSystem_Get_TeacherStatus(
	@message varchar(50) output
)
as
begin
	declare @secondindex varchar(50)
	select @secondindex = SecondIndex from UserView Where Role = 'Teacher'
	if @secondindex = 'TeacherSpecialTask'
	select @message = '可以选课'
	else 
	select @message = '停止选课'
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_List_Courses    Script Date: 2003-5-31 17:34:10 ******/
/*列出课程*/
create procedure CourseSystem_List_Courses
as
begin
	Select Classes.[ID],ClassName as CourseName,Info 
	from Classes,Enum
	Where Classes.Type=Enum.Type And (Classes.Type=1 or Classes.Type=3)
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_List_Teacher    Script Date: 2003-5-31 17:34:10 ******/
/*列出课程*/
create procedure CourseSystem_List_Teacher(
	@courseid int
)
as
begin
	Select Teacher.[Name],Email ,Prof,Teacher.[ID] as [ID] 
	from Course,Teacher
	Where Course.[CourseID]=@courseid And Teacher.[ID] = AvailableTeacher
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.Help_Get_HelpFile    Script Date: 2003-5-31 17:34:10 ******/

CREATE PROCEDURE Help_Get_HelpFile(
	@content varchar(50),
	@helpfile varchar(50) output
)
AS
BEGIN
	Select @helpfile = [Help] 
	from Content
	where Content =  @content
	if @helpfile is NULL
	Select @helpfile = 'main.htm'
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.Login_Get_Role    Script Date: 2003-5-31 17:34:10 ******/

/* 登陆验证密码 */
create procedure Login_Get_Role(
	@userid nvarchar(20),
	@role char(20) output
)
as
begin 
	select @role=Role from Login where UserID=@userid
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.Login_Get_Roles    Script Date: 2003-5-31 17:34:10 ******/

/* 得到用户组 */
create procedure Login_Get_Roles
as 
begin
	Select Distinct Role,Title From UserView Where Role!='Anonymous '
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.Login_Get_Task    Script Date: 2003-5-31 17:34:10 ******/
/*得到用户任务*/
create procedure Login_Get_Task(
	@indexname char(20)
)
as
begin
	SELECT IndexName, Content, ChildIndexName, Icon, Title
	FROM Navigator
	WHERE IndexName=@indexname
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.Login_Get_UserView    Script Date: 2003-5-31 17:34:10 ******/

/* 得到用户视图信息 */
create procedure Login_Get_UserView(
	@role varchar(50),
	@childindex varchar(50)
)
as
begin
	declare @first char(20)
	declare	@second char(20)
	declare	@third char(20)

	Select Role,FirstIndex,SecondIndex,ThirdIndex,Content
	From UserView 
	Where Role=@role

	Select @first=FirstIndex,@second=SecondIndex,@third=ThirdIndex 
	From UserView 
	Where Role=@role

	exec Login_Get_Task @first
	exec Login_Get_Task @second
	if @childindex is NULL or @childindex = ''
	exec Login_Get_Task @third
	else
	exec Login_Get_Task @childindex
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.Login_Verify_Password    Script Date: 2003-5-31 17:34:10 ******/

create procedure Login_Verify_Password(
	@userid nvarchar(20),
	@password varchar(50)
)
as
begin
	Select UserID From Login 
	Where UserID=@userid and [Password]=@password
end

GO

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

create procedure Login_Verify_PasswordNull(
	@userid nvarchar(20)
)
as
begin
	Select UserID From Login 
	Where UserID=@userid and [Password]=''
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.Member_Add_Student    Script Date: 2003-5-31 17:34:10 ******/
/*添加学生*/
create procedure Member_Add_Student(
	@id nvarchar(20),
	@name char(10),
	@classname  char(10),
	@retval int output
)
as
begin
	select @retval = 0	
	insert into Login(UserID,[Password],Role) 
	values (@id,'','Student')
	if(@@ROWCOUNT = 1 )
	begin
	insert into Student([ID],[Name],BelongingClass) 
	values (@id ,@name , @classname)
	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.Member_Add_Teacher    Script Date: 2003-5-31 17:34:10 ******/
/*添加教师*/
create procedure Member_Add_Teacher(
	@id nvarchar(20),
	@name char(10),
	@email varchar(50),
	@prof  char(10),
	@retval int output
)