Search code examples
mysqlsqlstore

Why does my Stored Procedure return NULL in the below query?

I have created a table and add some columns and now I want it to output s_scourse as "B.Tech" when the subject is "Computer Science". But it is showing null. Why???

These are my sample data:

create table student(name varchar(50),age int,subject varchar(100));

insert into student values("Tanmay",18,"MAths"),("Rani",19,"Computer Science"),("Rose",20,"Computer Application");
delimiter

and this is my procedure:

create procedure get_students(in subject_name varchar(50),out s_scourse varchar(50))
begin
declare sub varchar(50);
select subject from student
where subject=subject_name;
if sub="Computer Science"
then set s_scourse="B.tech";
end if;
end//
delimiter ;
call get_students("Computer Science",@s_scourse);
select @s_scourse;
Solution

  • You forget to use INTO to assign value to variable:

    select subject 
INTO sub
from student
where subject=subject_name;