CODEWITHSUNDEEP
variablesstored-proceduresdb2
Mani Swetha
Mani Swetha

Reputation: 41

Db2 - how to assign value to a variable with EXECUTE statement in db2

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 with COD_TIPO_ARQU value dynamically?

ThankYou.

Upvotes: 2

Views: 20197

Answers (4)

Îsh
Îsh

Reputation: 316

Thanks @Marcos EXECUTE... INTO ... worked.

Additionally, creating the stored proc with OUT parameter will let you access the value set by EXECUTE... INTO ... outside the stored proc.

CREATE OR REPLACE PROCEDURE ISH.SAMPLEPROC (OUT o_result VARCHAR(5))
specific proc_user_data_retrieval
dynamic result sets 1
reads sql data
language sql
BEGIN
DECLARE stmt varchar(5000) default null;
DECLARE VAR_X VARCHAR(5);
DECLARE stmt_final STATEMENT;
SET stmt = 'SET (?) = ('||'SELECT JOB_ROLE FROM ISH.USER_DATA LIMIT 1'||')';
PREPARE stmt_final FROM stmt;
EXECUTE stmt_final INTO VAR_X;
SET o_result = VAR_X;
END

Then call the stored proc using CALL ISH.SAMPLEPROC(?);

Upvotes: 0

Marcos
Marcos

Reputation: 450

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.

Upvotes: 2

Mani Swetha
Mani Swetha

Reputation: 41

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.

Upvotes: 2

AngocA
AngocA

Reputation: 7693

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@

Upvotes: 3

Related Questions