动态执行语句 给变量赋值

[lchy110]

以前没用过DB2 现在项目中有用到遇到棘手问题google到的方法都没解决问题
写了个简单的存储过程来模拟问题

CREATE PROCEDURE DB2JHB.PUBDATAPAGED
 (IN "PAGESIZ" INTEGER, 
  IN "PAGEINDEX" INTEGER，
  IN "TBNAME" VARCHAR(10)
 ) 
P1: begin
  	declare tmp_sql varchar(10000);
	declare count_sql varchar(200);
	declare tbname varchar(10);
  	declare tmp_conditions varchar(1000);
	declare param_pagecount INTEGER;
	declare v_Start INTEGER;
	declare v_End INTEGER;
	declare rcount integer;
	declare pcount integer;

  	declare cur_return cursor with return for s1;
        P2: begin
		set tbname='mt_task';
		set count_sql ='select count(*) into '||rcount||' from '||TBNAME;
    	        prepare s2 from count_sql;
		EXECUTE s2;
	end P2;--问题就出在这块，这个表名是带过来的参数 
        set  tmp_sql ='select w1.ecid,'||rcount||' from (select   rownumber() over() as row,w.*   from   ( select   *   from   mo_task ) w)   w1,SYSIBM.SYSDUMMY1  where   w1.row between '  || char(v_Start ) ||  ' and  '  || char(v_End );
  	prepare s1 from tmp_sql;  
  	open cur_return;    
  end P1;

我想要的效果是 取到一个值比如说表记录总数然后将它作为一列附加到另外一个查询结果集中 比如 select a.*,'aaa' from a 这样结果集就多了一个‘aaa’的列

[lchy110]

顶下。。继续等待解决方法

[WWWWA]

什么问题？

[wwwwb]

提示什么，检查一下tmp_sql、count_sql中的内容

[lchy110]

谢谢 楼上跟帖

错误提示：
SQL0087N 在不允许使用空值的上下文中指定了空值。 SQLSTATE=22004

SQL0087N 在不允许使用空值的上下文中指定了空值。

说明:

在变量不能为空值的上下文中使用了变量。例如，可以在 OPEN 或 FETCH 语句中
使用游标变量，但该游标变量的值不能为空值。无法处理该语句。

用户响应:

请确保该变量的值在不允许使用空值的上下文中不为空，然后再试该请求。

sqlcode：-87

sqlstate：22004

[haitaox]

初学者，使用的是

[WWWWA]

set count_sql ='select count(*) into rcount from '|| TBNAME;

[lchy110]

[Quote=引用 4 楼 wwwwa 的回复:]

set count_sql ='select count(*) into rcount from '|| TBNAME;
[/Quote] 还是不对
我把整个存储过程发来 各位高人们 你们只要带个你们本地又的表名进去就行了 麻烦帮我整下 谢谢了

DROP SPECIFIC PROCEDURE DB2JHB.SQL100317101702400;

#SYNC 10;

SET SCHEMA DB2JHB  ;

SET CURRENT PATH = "SYSIBM","SYSFUN","SYSPROC","SYSIBMADM","DB2JHB";

CREATE PROCEDURE DB2JHB.PROTEST
 (IN "TBNAME" CHARACTER(10)
 ) 
  SPECIFIC DB2JHB.SQL100317101702400
  LANGUAGE SQL
  NOT DETERMINISTIC
  CALLED ON NULL INPUT
  EXTERNAL ACTION
  OLD SAVEPOINT LEVEL
  MODIFIES SQL DATA
  INHERIT SPECIAL REGISTERS
  P1: BEGIN
      declare str_sql varchar(1000);
	  declare str_count varchar(500);
	  declare rcount char(3);
	  p2: begin
	  set str_count='select count(*) into rcount from '||TBNAME;
	  prepare s2 from str_count;
	  EXECUTE s2;
	  end p2;
	  p3: begin
	  declare cur_return cursor with return for s1;
	  --set rcount='100';
	  set str_sql='select m.*,'||rcount||' as count from '||TBNAME ;
	  prepare s1 from str_sql;  
  	  open cur_return; 
	  end p3;  
	  END P1;

#SYNC 20;