Having a stored procedure return a value to BTEQ.

[CapsuleCorpJX]

Thanks in advance for any insight.
I want to be able to return a value from a stored procedure to the KSH script calling it.

here is an example procedure:
REPLACE PROCEDURE testSP
(IN x VARCHAR(1000), OUT y INTEGER)
begin
...
end;

Would this work?

Example KSH Script:
v1=$1

bteq <<EOF

.run file ${RUN_DIR}/logonscript.sql
.echoreq on;
.set width 254
.pagelength 3000;
.null '~';

call testDB.testSP(${v1},${out});
SELECT * FROM testDB.testTable WHERE testField = ${out};

 

[dnoeth]

Untested:

.os rm xxx;
.export data file = xxx;
call testDB.testSP(${v1},y);
.import data file = xxx;
using y (int)
SELECT * FROM testDB.testTable WHERE testField = :y;

Dieter