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