createprocedure.sql

这是1个数据库

as
begin
	select @retval = 0	
	insert into Login(UserID,[Password],Role) 
	values (@id,'','Teacher')
	if(@@ROWCOUNT = 1 )
	begin
	insert into Teacher([ID],[Name],Email,Prof) 
	values (@id ,@name ,@email ,@prof)
	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_Delete_Student    Script Date: 2003-5-31 17:34:10 ******/

/*删除学生*/
create procedure Member_Delete_Student(
	@id nvarchar(20)
)
as
begin
	Delete from Selected_Class
		Where StudentID = @id
	Delete from Student
		where [ID] = @id
	Delete from Login
		where [UserID] = @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.Member_Delete_Teacher    Script Date: 2003-5-31 17:34:10 ******/

/*删除教师信息*/
create procedure Member_Delete_Teacher(
	@id nvarchar(20)
)
as
begin
	Delete from Selected_Class 
		where ClassID = any (Select Class.[ID] 
					from Class,Teacher
					where Class.TeacherID=Teacher.[ID] and Teacher.[ID] = @id)
	Delete from Class
		where [ID] = any (Select Class.[ID] 
					from Class,Teacher
					where Class.TeacherID=Teacher.[ID] and Teacher.[ID] = @id)
	Delete from Course 
		where AvailableTeacher = @id
	Delete from Teacher
		where [ID] = @id
	Delete from Login
		where [UserID] = @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.Member_List_Student    Script Date: 2003-5-31 17:34:10 ******/
/*得到学生列表*/
create procedure Member_List_Student(
	@classid varchar(30)
)
as
begin
	Select [ID],[Name],BelongingClass from Student
	where BelongingClass=
	(select ClassName from Classes 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.Member_List_Teacher    Script Date: 2003-5-31 17:34:10 ******/
/*得到教师列表*/
create procedure Member_List_Teacher
as
begin
	Select [ID],[Name],Email,Prof 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.Member_Student_GetClasses    Script Date: 2003-5-31 17:34:10 ******/
/*得到学生班级*/
create procedure Member_Student_GetClasses
as
begin
	select [ID],ClassName from Classes where 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.Member_Teacher_GetProfList    Script Date: 2003-5-31 17:34:10 ******/

CREATE PROCEDURE Member_Teacher_GetProfList
AS
BEGIN
	Select Info
	from Enum
	where Type>10 and Type<20
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_Update_Student    Script Date: 2003-5-31 17:34:10 ******/

/* 更新学生信息 */
CREATE PROCEDURE Member_Update_Student(
	@id            nvarchar(20),
	@name          nvarchar(10),
	@classname     nvarchar(10),
	@retval        int output
)
AS
BEGIN
    IF EXISTS(SELECT * FROM [Student] WHERE [ID] = @id)
    BEGIN
        UPDATE [Student] 
        SET    [Name] = @name, [BelongingClass] = @classname
        WHERE  [ID] = @id
        SET @retval = 1
    END
    ELSE
    BEGIN
        SET @retval = 0
    END
	 
    RETURN 
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_Update_Teacher    Script Date: 2003-5-31 17:34:10 ******/

/* 更新教师信息 */
CREATE PROCEDURE Member_Update_Teacher(
	@id      nvarchar(20),
	@name    nvarchar(10),
	@email   nvarchar(50),
	@prof    nvarchar(10),
	@retval  int output
)
AS
BEGIN
    IF EXISTS(SELECT * FROM [Teacher] WHERE [ID] = @id)
    BEGIN
        UPDATE [Teacher] 
        SET    [Name] = @name, [Email] = @email, [Prof] = @prof
        WHERE  [ID] = @id
        SET @retval = 1
    END
    ELSE
    BEGIN
        SET @retval = 0
    END
	 
    RETURN 
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.Security_Set_Password    Script Date: 2003-5-31 17:34:10 ******/
/*设置密码*/
CREATE PROCEDURE Security_Set_Password
   @UserId       nvarchar(20) ,         
   @OldPassword  varchar (50) ,                                               
   @Password     varchar(50) ,                                      
   @Return smallint   output
AS


SET @Return = 0

IF EXISTS(SELECT * FROM Login WHERE UserID=@UserId AND Password=@OldPassword)
	BEGIN
	  SET @Return = 1
	  UPDATE Login SET Password=@Password
	    WHERE UserID=@UserId
	END
ELSE  
	BEGIN
	  SET @Return=0
	END
RETURN



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.Statisitics_Get_BelowTwoStudent    Script Date: 2003-5-31 17:34:10 ******/

CREATE PROCEDURE Statisitics_Get_BelowTwoStudent

AS
BEGIN

	SELECT Student.[ID],Student.[Name],Student.BelongingClass,Count(Selected_Class.ClassID) as Number
	FROM Student LEFT JOIN Selected_Class
	ON Student.[ID] = Selected_Class.StudentID
	GROUP BY Student.[ID],Student.[Name],Student.BelongingClass
	HAVING (COUNT(Selected_Class.ClassID)<2)
	Order by Number
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.Statisitics_Get_ClassByCourseID    Script Date: 2003-5-31 17:34:10 ******/


CREATE PROCEDURE  Statisitics_Get_ClassByCourseID
(
	@courseid varchar(30)
)

AS

BEGIN
	SELECT Classes.ClassName AS CourseName , Class.[Name] AS ClassName, Class.[ID] AS ClassID
	FROM  Class LEFT JOIN Classes
        ON    Class.CourseID = Classes.[ID]
	WHERE Class.CourseID = @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.Statisitics_Get_Course    Script Date: 2003-5-31 17:34:10 ******/

CREATE PROCEDURE Statisitics_Get_Course

AS

BEGIN
	SELECT [ID] as CourseID, ClassName as CourseName 
	FROM   Classes
	WHERE 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.Statisitics_Get_SelectedClassNumber    Script Date: 2003-5-31 17:34:10 ******/


/*获取每门课的选课人数*/
CREATE PROCEDURE Statisitics_Get_SelectedClassNumber
AS

select Number,ClassName as CourseName,Info
from (Classes inner join Enum
on Classes.Type=Enum.Type and Classes.Type in (1,3))
left join (
	SELECT COUNT(StudentID) AS Number,CourseID
	FROM ( Selected_Class right join Class as b 
	on Selected_Class.ClassID = b.[ID])
	GROUP BY b.CourseID
	) as c
on Classes.[ID]=c.CourseID 



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.Statisitics_Get_StudentByClassID    Script Date: 2003-5-31 17:34:10 ******/

CREATE PROCEDURE Statisitics_Get_StudentByClassID
(
	@classid int
)
AS

BEGIN     
        SELECT StudentID, [Name], BelongingClass 
        FROM   Selected_Class ,Student
	WHERE  Selected_Class.ClassID = @classid
		and Selected_Class.StudentID = Student.[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.Statisitics_Get_Teacher    Script Date: 2003-5-31 17:34:10 ******/

CREATE PROCEDURE Statisitics_Get_Teacher

AS

BEGIN

	SELECT Teacher.Name AS 	Teacher ,Teacher.ID AS TeacherID 
	FROM Class LEFT JOIN Teacher
	ON Class.TeacherID  = Teacher.ID
	GROUP BY Teacher.ID,Teacher.Name

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.Statisitics_Get_TeacherCourse    Script Date: 2003-5-31 17:34:10 ******/

CREATE PROCEDURE Statisitics_Get_TeacherCourse
(
	@teacherid nvarchar(20)
)
AS

BEGIN

	SELECT TeacherID ,  Teacher.[Name] as Teacher, Email, Prof, Classes.ClassName As CourseName, Class.[Name] As ClassName
	FROM  Class,Teacher,Classes
	Where Class.TeacherID = @teacherid 
	and Teacher.[ID] = @teacherid
	and Classes.[ID] = Class.CourseID

END


GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO