存储过程动态取值和赋值问题

[gongjieqi]

代码如下，运行时 显示我变量b_column_value未定义
ERROR 1327 : Undeclared variable: b_column_value

t_id w_id bname 是从外面传入的

begin
  /*基础变量声明*/
DECLARE u_id int;
DECLARE r_id int;
DECLARE t_year int;

DECLARE b_name varchar(255);
DECLARE b_baifen decimal(10,2);
DECLARE b_quan decimal(10,6);
DECLARE b_value int;
DECLARE b_min decimal(10,4);
DECLARE b_max decimal(10,4);
DECLARE b_column_name varchar(255);
DECLARE b_table_name varchar(255);
DECLARE b_column_value decimal(10,4);

DECLARE result decimal(10,6) default 0.000000;
DECLARE done int(10);
DECLARE cursor_name CURSOR FOR select bin_baifen,bin_quan,bin_value,min_value,max_value,bin_column_name,bin_table_name  from jbfx_yinsu where bin_name=bname;
DECLARE continue handler for not found set done=1;
open cursor_name;
 loop_label:LOOP
  		fetch cursor_name into b_baifen,b_quan,b_value,b_min,b_max,b_column_name,b_table_name;
  			if done =1 then
       				 leave loop_label;
  			else
  				set @str = concat('select ',b_column_name,' into b_column_value from ',b_table_name,' where wenjuan_id =',w_id,' and tijian_id = ',t_id);
				prepare stmt1 from @str;
				execute stmt1;
				deallocate prepare stmt1;
				if b_value != null then
				  if b_value = b_column_value then
				     set result = result+b_baifen*b_quan;
                                 end if;
                                elseif (b_min != null) AND (b_max != null) then
                                   if (b_column_value>= b_min) AND (b_column_value < b_max) then
                                         set result = result+b_baifen*b_quan;
				  end if;
                             end if;
 		            end if;
  END LOOP;
close cursor_name;
select result;
end

[gongjieqi]

自行解决了 - -

[ACMAIN_CHM]

改所有的 b_column_value 为 @b_column_value set @str = concat('select ',b_column_name,' into @b_column_value from ',b_table_name,' where wenjuan_id =',w_id,' and tijian_id = ',t_id); if b_value = @b_column_value then .... 因为你的PREPARE执行的SQL语句并不是在存储过程的变量空间内。