INTO CLAUSE IS NOT PERMITTED

Question

I created Dynamic DB2 Stored procedure for select. I'm going to use this generic query to execute multiple select statement, by passing the parameter.

SET V_SELECT =                   
  'SELECT ' || SELECT_FIELDS ||  
  ' INTO '  || INTO_FIELDS   ||  
  ' FROM '  || TABLE_NAME    ||  
  ' WHERE ' || WHERE_CLAUSE  ||  
  ' WITH UR';    
EXECUTE IMMEDIATE V_SELECT;     

Below is how my query will look a like.

   SELECT B.PROD_TYP  
   INTO HOST_VAR_PROD_TYP
   FROM TABLE_A   A
   INNER JOIN TABLE_B  B
   ON A.ROW_ID = B.ROW_ID
   WHERE A.PROD_CD = HOST_VAR_PROD_CD;

When I run this I'm getting

"INTO CLAUSE IS NOT PERMITTED".

Thanks in Advance.

Answer 1

I don't have DB2 for z/os at hand, but you should do something like this:

  DECLARE V_STMT VARCHAR(200);
  DECLARE V_NAME VARCHAR(128);
  DECLARE V_COLCOUNT INT;

  SET V_NAME = 'SYSTABLES';
  SET V_STMT = 'SET ? = (SELECT COLCOUNT FROM SYSIBM.SYSTABLES WHERE CREATOR = ''SYSIBM'' AND NAME = ?)';
  PREPARE S1 FROM V_STMT;
  EXECUTE S1 INTO V_COLCOUNT USING V_NAME;

You need SET statement. Construct V_STMT value according to your needs. The 1-st ? denotes the result you get into V_COLCOUNT from you select. The 2-nd ? denotes a parameter you pass with the V_NAME variable.