In Out Patameters In Dynamic Set DB2

Question

my table is aaa (a char(5))

      CREATE OR REPLACE PROCEDURE zzz ()
         DYNAMIC RESULT SETS 1
     P1: BEGIN
        Declare cID char(5) ;
        Declare cc char(5) ;
        Declare csql varchar(200) ;
        Declare s1 varchar(200) ;

         Set cID = 'a' ;

         SET Csql = 'SET ? = (select a from aaa where a = :cID)' ;

          PREPARE S1 FROM Csql  ;       
          EXECUTE S1 into cc using cID ;     {error this line}

           print (cc) ;
    END P1

I get error :

Deploy [sss]A.ZZZ Running A.ZZZ - Deploy started. Create stored procedure returns SQLCODE: -104, SQLSTATE: 42601. A.ZZZ: 14: An unexpected token "" was found following "". Expected tokens may include: "S1 into cc using cID".. SQLCODE=-104, SQLSTATE=42601, DRIVER=3.69.56 An unexpected token "" was found following "". Expected tokens may include: "S1 into cc using cID".. SQLCODE=-104, SQLSTATE=42601, DRIVER=3.69.56 A.ZZZ - Deploy failed. A.ZZZ - Roll back completed successfully.

Learn how to write routines by studing working examples here ibm.com/docs/en/db2/11.5?topic=api-sql-pl-samples — mao

Mark Barinstein Mark Barinstein · Accepted Answer · 2021-05-29T14:37:38

Multiple problems.

S1 must either have the STATEMENT type or not be declared at all, but not be declared as VARCHAR.
print (cc) is not a valid statement at all. If you want to have some debug output, read about DBMS_OUTPUT.PUT_LINE. The tool you call your routine from must have an ability to display contents of the message buffer.
Your routine doesn't return any cursors to caller / client. There is no need to use the DYNAMIC RESULT SETS 1 clause. This problem is not critical.

In Out Patameters In Dynamic Set DB2

1 Answers