CODEWITHSUNDEEP
stored-procedurescursormysqlmysql-workbench
Benny
Benny

Reputation: 432

insert into the table from one to another using cursor in store procedure

i created two table student and grade. the student table contains the column of id(PK),name,mark,address. and i inserted 10 values to it. in the grade table there are two coulmns stud_id(foreign key)and stud_status. and in grade i have to write a cursor inside store procedure, to insert into the grade table from the student table. the condition will be like if the student mark is above 50 inthe grade table it should be store as 'G' in stud_status and the stud_id too for it. if the mark =50 it should store 'E' and else are'L' i use the below code. and iam using MySQL Workbench 6.0.

    use test;
    delimiter $$
    drop procedure if exists `p_status` $$
    create procedure `p_status`()
       begin
      declare s_stud_mark int(111);
       declare s_stud_id int(111);
       declare cur_stud cursor For Select stud_id,stud_mark from  student where stud_id                is not null;
         open cur_stud;
          fetch cur_stud into s_stud_mark,s_stud_id;
         if(stud_mark > 50) then
         set s_stud_mark='G';
            insert into grade(`stud_id`,`stud_staus`)values(s_stud_id,s_stud_mark);
          else if(stud_mark = 50) then
         set s_stud_mark='E';
          insert into grade(`stud_id`,`stud_status`) values(s_stud_id,s_stud_mark);
           else
            set s_stud_mark='L';
            insert into grade(`stud_id`,`stud_status`)values(s_stud_id,s_stud_mark);
           end if ;
          end if ;
         close cur_stud;
         end $$
         delimiter ;

but it shows error as "Error Code: 1054. Unknown column 'stud_mark' in 'field list'"

anyone reply

Upvotes: 2

Views: 8325

Answers (1)

Ravinder Reddy
Ravinder Reddy

Reputation: 24002

Error is in the lines: 

if(stud_mark > 50) then
...
else if(stud_mark = 50) then

Change them to: 

if(s_stud_mark > 50) then
...
else if(s_stud_mark = 50) then

Update 1:

but another error is showing "Error Code: 1366. Incorrect integer value: 'G' for column 's_stud_mark' at row 11

It is because, you defined stud_mark as int in your table but you are assigning a char into it in the routine. You should actually have defined a variable s_stud_status in the routine and assigned value to it like set s_stud_status='G';.
Similarly for other grade values in the routine.

And change the code as below, wherever required. 

if(s_stud_mark > 50) then
  set s_stud_status='G';
  insert into grade(`stud_id`,`stud_status`) values(s_stud_id,s_stud_status);
else ...
  set s_stud_status='E';
  ...

Upvotes: 2

Related Questions