Capturing OUT parameter value from SP, from with in BTEQ ???

Database
Enthusiast

Capturing OUT parameter value from SP, from with in BTEQ ???

Hi there,

could anyone help me out on this.

I jst am calling one procudeure "CALL myproc....." from with in BTEQ and it contains 2 OUT parameters.
How do I capture those two return variable's values in any variables.

My ultimate aim is to capture the value and insert them in to one table.

pls help me out.
4 REPLIES

Re: Capturing OUT parameter value from SP, from with in BTEQ ???

Hello,
The best think in my opinion is to create another dummy procedure where you call the procedure which has the output parameters and the insert into table and call this proc from the bteq. Something like this..

replace procedure db_name.test_proc
(
)
SPBEGIN:
BEGIN

DECLARE out1 varchar(500);
DECLARE out1 varchar(500);

CALL db_name.proc(var1,var2,:out1,:out2);

insert into db_name.test_table(:out1,:out2);

END SPBEGIN;

call db_name.test_proc();

hope this helps!!

Enthusiast

Re: Capturing OUT parameter value from SP, from with in BTEQ ???

Thanks Mate :-) well , that was a good idea, but, i ve been jst thinkin of, there must be a direct way to capture n process this values in BTEQ.

Forum Gurus,
I too am searching the TD reference materials, but in vain.
pls share some alternatives for this prob.
thx in adv.

Enthusiast

Re: Capturing OUT parameter value from SP, from with in BTEQ ???

I don't think BTEQ can process output parms from SP.

But here's what you could do, I think it was partly posted

As was suggested you will need to use an intermediate table, instead of outputing it.

Use a export from this intermediate table to file.

BTEQ can use this as variables. (Lookup for USING option in books online).

Easiest way to process it DBMS than exporting out.

Good luck w/ this.

Vinay

Enthusiast

Re: Capturing OUT parameter value from SP, from with in BTEQ ???

thanks Vinay, that was a good idea.
but, i have one more alternative of creating one anonymous procedure from bteq and calling the required procedure from the anonymous procedure and capture and insert the return values in to a volatile table.

As now, return values are available in volatile tables, i could easily manimupate them from BTEQ.

However, the prob i face now is, i m not able to create any anonymous procedures, as in Oracle.

Any idea of how to create a anonymous procedure from with in BTEQ?
i m also searching the TD referece materials, but in vain. they all talk about how to create and use named procedures only.

pls help.