1

I am a beginner to SQL and currently practicing the concept of the stored procedure. I have a table Fetch_Marks that contains 20 records with Student_No and Marks as columns. Now I would like to create a stored procedure for this in which the input parameter will be Student_No and when we enter the input (Let's say I gave the Student_No as 1), it should fetch the row of that particular student from the table Fetch_Marks. Is there any workaround for this? If yes, please help.

0

You basically don't need a procedure for that.

Procedures get interesting with CURSOR and LOOP when you need "normal" programming besides SQL to makes some thong much more complex

DROP procedure IF EXISTS `proc_get Student`;

DELIMITER $$
CREATE PROCEDURE `proc_get Student` ( IN _student_no BIGINT)
BEGIN
    SELECT * FROM Fetch_Marks WHERE Student_No  = _student_no;
END$$

DELIMITER ;

But I am guessing this is some kind of homework, the variable _student_no is as it is because you have so a optical marker that it is a locally defined variable

Here's another example: In this, we are adding a second IN variable and in which both are checked if they are NULL or NOT and then selects the wanted data

DROP procedure IF EXISTS `proc_get Student`;

DELIMITER $$
CREATE DEFINER=`root`@`%` PROCEDURE `proc_get Student`( IN _student_no BIGINT,IN _roll_no BIGINT)
BEGIN
    IF ( _student_no IS NOT NULL AND _roll_no IS NULL) THEN
        SELECT * FROM Fetch_Marks WHERE Student_No  = _student_no;
    END IF;
END$$

DELIMITER ;
4
  • yes you can but you have to check IS NULL
    – nbk
    Aug 14 '20 at 11:42
  • Thanks again, even this one worked but I'm curious why it is not fetching any data when I used <> (not equal to) null instead of not null Aug 14 '20 at 12:37
  • Mysql only allows NULL ckecks with IS NULL, as you notoved <> is not perogramed in. Please accept the answer if ot helps
    – nbk
    Aug 14 '20 at 13:08
  • Ooh yeah why not, I actually forgot about it. By the way, if you don't mind can you add a line explaining about the second procedure in your answer so that the one who's searching for this answer doesn't get confused. Aug 14 '20 at 13:15

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.