在开发IQ存储过程时,经常会用到动态执行sql语句的情况。本文向大家介绍在存储过程中执行有结果集返回的动态SQL时的调用方法和注意事项。我将以下面的例子进行说明:

–示例存储过程:根据参数提供的表名动态执行select语句,返回结果集的头10条记录

–假设我们的sql脚本文件叫做proc1.sql,其内容如下:

create procedure proc_test_dynamic(in p_tablename char(30))

begin

     declare sqlString char(100);

     set sqlString=’select top 10 * from ‘ ||  p_tablename;

    print ‘sql=%1!’, sqlString; 

    execute immediate sqlString;                 –W-SQL动态执行sql语句的语法

end;

使用dbisql工具执行proc1.sql 以在IQ数据库中创建示例存储过程:

     dbisqlc -c “uid=DBA;pwd=sql” -nogui proc1.sl

在创建存储过程后,我们执行它:

    execute proc_test_dynamic ‘Customers’

在执行时会报如下错误:

    Could not execute statement.

       Result set not permitted in ‘<batch statement>’

       SQLCODE=-946, ODBC 3 State=”07005″

       Line 1, column 1

       execute proc_test_dynamic ‘Customers’

        

怎么解决这个问题呢?办法是在存储过程中调用动态sql语句时加上with result set on字句,修改后的存储过程代码为:

alter procedure proc_test_dynamic(in p_tablename char(30))

begin

    declare sqlString char(100);

    set sqlString=’select top 10 * from ‘ ||  p_tablename;

   print ‘sql=%1!’, sqlString; 

   execute immediate with result set on sqlString;               

end;

再次执行存储过程,结果正确。

To report this post you need to login first.

Be the first to leave a comment

You must be Logged on to comment or reply to a post.

Leave a Reply