I recently started learning stored procedures in MySQL and created a procedure like this:
CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_getAllDetails`(IN _student_no INT,
IN _class_id INT, IN _subject_name VARCHAR(50) )
BEGIN
IF _student_no IS NOT NULL AND _class_id IS NULL AND _subject_name IS NULL THEN
SELECT DISTINCT S.Student_No, S.Student_Name, C.Class_id, S.Class,
C.Class_desc, M.Eng_marks, M.Maths_marks, M.Science_marks FROM Student S
INNER JOIN Marks M ON S.Student_No = M.Student_No
INNER JOIN Class C ON M.Class_id = C.Class_id
WHERE S.Student_No = _student_no;
ELSE IF _class_id IS NOT NULL AND _student_no IS NULL AND _subject_name IS NULL THEN
SELECT DISTINCT S.Student_Name, C.Class_id, S.Class, C.Class_desc,
M.Eng_marks, M.Maths_marks, M.Science_marks FROM Student S
INNER JOIN Marks M ON S.Student_No = M.Student_No
INNER JOIN Class C ON M.Class_id = C.Class_id
WHERE C.Class_id = _class_id;
ELSE IF _subject_name IS NOT NULL AND _class_id IS NULL AND _student_no IS NULL THEN
SELECT DISTINCT S.Student_Name, S2.Student_No, S2.Class_id, S1.Subject_Name,
S1.Subject_id, S3.Eng_marks, S3.Maths_marks, S3.Science_marks
FROM Subject1 S1
INNER JOIN Subject2 S2 ON S1.Subject_id = S2.Subject_id
INNER JOIN Subject3 S3 ON S3.Student_No = S2.Student_No
INNER JOIN Student S ON S.Student_Name = S2.Student_Name
WHERE S1.Subject_Name = _subject_name;
END IF;
END IF;
END IF;
END
It is displaying the desired output only when I call the procedure like this in the workbench:
call school.sp_getAllDetails(401, NULL, NULL);
However, if I enter the input parameters in the dialog box from the shortcut in the left-hand menu, it is taking the same as:
call school.sp_getAllDetails(401, NULL, 'NULL');
This is not displaying any data as it is taking NULL as a string. Can I know where am I going wrong? It will be a great help. Many Thanks
Edit: Leaving the question here because anyone who's looking for the same may get some help here. Also, if anyone has a different answer can add to this.
