Mysql stored procedure giving error at runtime

Question

I have created a stored procedure in Mysql, as :

delimiter $$
drop procedure if exists test9$$
Create procedure test9(test_type varchar(20))
Reads sql data
begin

    Declare 1_id int;
    Declare 1_date varchar(20);
    Declare done int default 0;
    Declare cur1 cursor for 
        select id,name from buyers where ticket_type='test_type';
    Declare Continue handler for not found set done=1;

    Create temporary table if not exists ticketninja.history2(n_id int,n_date varchar(20));

    Open cur1;
        hist_loop:loop
            fetch cur1 into 1_id,1_date;

                if done=1 then
                    leave hist_loop;
                end if;

        insert into ticketninja.history2(n_id ,n_date) values(1_id,1_date);
        End loop hist_loop;
    close cur1;

    select * from history2;
    drop table history2;

End;

$$

delimiter ;

but when i call it using,

call test9('platinum');

it returns an error saying :

#1312 - PROCEDURE ticketninja.test1 can't return 
a result set in the given context

what i am doing wrong here?

Answer 1

I think you need an OUT variable (see first code sample here)