MySQL:creating cursor dynamically in procedure

Question

Below i have created one procedure, which took 2 parameter called company_name and SN_F,

I want to pass the table name to procedure as parameter for which i want to create cursor.

DELIMITER //
CREATE PROCEDURE transfer_t(IN company_name varchar(50), SN_f int)
BEGIN


DECLARE done BOOLEAN DEFAULT 0;
DECLARE dates_f date default null;
DECLARE high_f float default 0.0;
DECLARE low_f float default 0.0;
DECLARE open_f float default 0.0;
DECLARE close_f float default 0.0;
DECLARE volume_f int default 0;
DECLARE adj_close_f float default 0.0;



DECLARE Company_detail cursor for 
    select Date, high, low, open, close, volume, adj_close from company_name;
    Declare CONTINUE HANDLER FOR NOT FOUND SET done=1;
    OPEN Company_detail;
    REPEAT
        FETCH company_detail into dates_f, high_f, low_f, open_f, close_f, volume_f, adj_close_f;

        insert into historic_data(sn ,Date ,High ,low ,open ,close , volume , adj_close) values (SN_f,dates_f,high_f,low_f,open_f,close_f, volume_f, adj_close_f);

        until done END REPEAT;
        close company_detail;


END//
DELIMITER ;

above procedure gets created successfully, but whenever i called it like,

call transfer_t('tcs_temp', 1);

it gives the following error

Error Code: 1146. Table 'test_schema.company_name' doesn't exist

Please help me solve this...

Do you have one table per company? All with the same schema? — Paul Spiegel
– Paul Spiegel, Commented Jun 13, 2020 at 14:00

GMB · Accepted Answer · 2020-06-13 14:03:13Z

You seem to be looking to copy all records from a table name given as parameter to anther, fixed table. I don't see the need for a cursor here. SQL is a set-based language, which is built to natively perform such kind of operation. You would typically use the insert into ... select ... syntax.

On the other hand, you need dynamic SQL if you want to use the table name as variable - which your current code is missing.

The following code should do what you want:

delimiter //

create procedure transfer_t(in p_company_name varchar(50), p_sn_f int)
begin
    set @p_sn_f = p_sn_f;
    set @sql = concat(
        'insert into historic_data(sn ,Date ,High ,low ,open ,close , volume , adj_close)',
        ' select ?, date, high, low, open, close, volume, adj_close from ', p_company_name
    );

    prepare stmt from @sql;
    execute stmt using @p_sn_f;
    deallocate prepare stmt;
end//

delimiter ;

Collectives™ on Stack Overflow

MySQL:creating cursor dynamically in procedure

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related