SQLize Online / PHPize Online  /  SQLtest Online

-- Log In into System CREATE OR ALTER PROC Log_In @Fname VARCHAR(50), @Lname VARCHAR(50), @Email VARCHAR(100), @Password VARCHAR(255), @UserName VARCHAR(100), @Type CHAR(1) AS IF @Type = 'S' BEGIN INSERT INTO Student VALUES (@Fname, @Lname, @Email, @Password, @UserName) END ELSE IF @Type = 'I' BEGIN INSERT INTO Instructor VALUES (@Fname, @Lname, @Email, @Password, @UserName) END ELSE BEGIN INSERT INTO Training_Manager VALUES (@Fname, @Lname, @Email, @Password, @UserName) END GO -- Manage Student by Training Manager CREATE OR ALTER PROC Manage_Student @St_ID INT, @TM_ID INT, @Type VARCHAR(50) AS IF @Type = 'Add' BEGIN IF EXISTS (SELECT 1 FROM St_Mgr WHERE St_ID = @St_ID AND TM_ID = @TM_ID) SELECT 'Already added' ELSE BEGIN IF EXISTS ( SELECT 1 FROM St_Br SB JOIN Branch B ON B.Br_ID = SB.Br_ID JOIN Training_Manager TM ON TM.TM_ID = B.TM_ID WHERE St_ID = @St_ID ) SELECT 'Training manager of branch need to delete student' ELSE BEGIN INSERT INTO St_Mgr VALUES (@St_ID, @TM_ID) DECLARE @Br_ID INT SELECT Br_ID FROM Branch WHERE TM_ID = @TM_ID IF EXISTS (SELECT 1 FROM St_Br WHERE St_ID = @St_ID) BEGIN UPDATE St_Br SET Br_ID = @Br_ID WHERE St_ID = @St_ID END ELSE BEGIN INSERT INTO St_Br Values (@St_ID, @Br_ID) END END END END ELSE BEGIN DELETE SB FROM St_Br SB JOIN Branch B ON B.Br_ID = SB.Br_ID JOIN Training_Manager TM ON TM.TM_ID = B.TM_ID WHERE SB.St_ID = @St_ID AND TM.TM_ID = @TM_ID END --Exam CREATE OR ALTER PROCEDURE CreateExam( @ExName varchar(50) , @ExMinDeg int , @ExMaxDeg int , @ExYear int , @InsID int , @CrsID int , @Time int , @Date datetime , @QuestID int , @QCnt int ) AS BEGIN SET NOCOUNT ON; DECLARE @ExamID INT; --SET @ExamID = SCOPE_IDENTITY(); -- Create a new Exam INSERT INTO Exam ( Ex_Name, Ex_MinDegree,Ex_MaxDegree,Ex_Year, Ins_ID , Crs_ID,Time , Date ) VALUES ( @ExName, @ExMinDeg, @ExMaxDeg , @ExYear, @InsID , @CrsID, @Time , @Date ); INSERT INTO Quest_Ex(Quest_ID, Ex_ID ) SELECT TOP (@QCnt) Quest_ID , @ExamID FROM Question_Pool ORDER BY NEWID(); --INSERT INTO Quest_Ex(Quest_ID, Ex_ID ) --values (@QuestID,@ExamID); END; exec CreateExam 'test1' , 50 , 100, 2024 , 1 ,2 , 3 , '2024-11-01', 1 , 3