Db2 - how to assign value to a variable with EXECUTE statement in db2
Solution 1
The problem is the way you are setting the result from the execution:
EXECUTE stmt1 into VAR_COD_TIPO_ARQU ;
This is the complete code that is executed succefuly
CREATE OR REPLACE PROCEDURE TEST (IN indbnm VARCHAR(30), IN intblnm VARCHAR(30))
LANGUAGE SQL
BEGIN
DECLARE statmnt2 VARCHAR(1000);
DECLARE VAR_COD_TIPO_ARQU CHAR(1);
DECLARE stmt1 STATEMENT;
SET statmnt2 = 'SELECT COD_TIPO_ARQU FROM '||indbnm||'.'||intblnm||' FETCH FIRST 1 ROWS ONLY';
PREPARE stmt1 FROM statmnt2;
EXECUTE stmt1 into VAR_COD_TIPO_ARQU ;
END@
Solution 2
Hi is it correct solutuion:
SET statmnt = 'SELECT COD_TIPO_ARQU FROM '||indbnm||'.'||intblnm||' FETCH FIRST 1 ROWS ONLY';
PREPARE stmt1 FROM statmnt;
BEGIN
DECLARE c1 CURSOR FOR stmt1;
OPEN c1;
FETCH c1 into sttmresult;
CLOSE c1;
END;
TY.
Solution 3
I know it's been a while since this question was asked, but I found that none of the given answers worked.
@AngocA's solution was close but, as @Mani_Swetha pointed out, the EXECUTE
statement fails due to the SELECT
bit.
After searching and combining solutions around the web, this is what finally worked for me:
CREATE OR REPLACE PROCEDURE TEST (IN indbnm VARCHAR(30), IN intblnm VARCHAR(30))
LANGUAGE SQL
BEGIN
DECLARE statmnt2 VARCHAR(1000);
DECLARE VAR_COD_TIPO_ARQU CHAR(1);
DECLARE stmt1 STATEMENT;
SET statmnt2 = 'set ? = (SELECT COD_TIPO_ARQU FROM '||indbnm||'.'||intblnm||' FETCH FIRST 1 ROWS ONLY)';
PREPARE stmt1 FROM statmnt2;
EXECUTE stmt1 into VAR_COD_TIPO_ARQU ;
END@
Note that now the executed command is a set
statement with a SELECT
inside, rather than a pure SELECT
statement. This is what makes the trick.
Mani Swetha
Updated on September 06, 2022Comments
-
Mani Swetha over 1 year
I'm tring to execute query in a db2 procedure:
CREATE OR REPLACE PROCEDURE TEST (IN indbnm VARCHAR(30), IN intblnm VARCHAR(30)) LANGUAGE SQL BEGIN DECLARE statmnt2 VARCHAR(1000); DECLARE VAR_COD_TIPO_ARQU CHAR(1); DECLARE stmt1 STATEMENT; SET statmnt2 = 'SELECT COD_TIPO_ARQU FROM '||indbnm||'.'||intblnm||' FETCH FIRST 1 ROWS ONLY'; PREPARE stmt1 FROM statmnt2; SET VAR_COD_TIPO_ARQU = EXECUTE (stmt1); END@
This gives following error:
DB21034E The command was processed as an SQL statement because it was not a valid Command Line Processor command. During SQL processing it returned: SQL0206N "STMT1" is not valid in the context where it is used. LINE NUMBER=33. SQLSTATE=42703
What's the right way to set
VAR_COD_TIPO_ARQU
withCOD_TIPO_ARQU
value dynamically?ThankYou.