CODEWITHSUNDEEP
phpmysqlcursor
MLemos
MLemos

Reputation: 21

MySQL Cursor with a variable in select statement

I've the following code in a stored procedure: 




DECLARE done INT DEFAULT FALSE;
declare v_degree int(11);
declare v_start_age smallint(6);
declare v_end_age smallint(6);
declare v_gender varchar(20);
declare v_calctable varchar(200);

SELECT calculationtable into v_calctable FROM wac.degrees where tablename = concat("cdb_" + arg_tablename);

declare cur CURSOR for select degree, start_age, end_age, belt, gender from v_calctable;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
Like this I'll get a syntax error in the line "SELECT calculationtable into v_calctable FROM wac.degrees where tablename = concat("cdb_" + arg_tablename);"

If I put it after the "Declare cursor" there's no syntax error, but I need the result to be used on de Cursor select statement.

How can I accomplish this?

Thanks.

Upvotes: 1

Views: 3140

Answers (1)

Man
Man

Reputation: 772

CREATE DEFINER=`root`@`localhost` PROCEDURE `my_procedure`()
BEGIN
DECLARE done INT DEFAULT FALSE;
declare v_degree int(11);
declare v_start_age smallint(6);
declare v_end_age smallint(6);
declare v_gender varchar(20);
declare v_calctable varchar(200);

SELECT calculationtable into v_calctable FROM wac.degrees where tablename = concat("cdb_" + arg_tablename);
begin                                                                   -- write here begin keyword
declare cur CURSOR for select degree, start_age, end_age, belt, gender from v_calctable;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
end;                                                                    -- end here inner block

END

when you use inner block by another begin and end keyword then it have no syntask error . but this procedure give you correct result or not i am not clear

Upvotes: 1

Related Questions