capturing the result from Execute Immediate

[Rahul Babbar]

Hi,

I have the following doubt.

Suppose I use the execute immediate statement and the statement to be
executed is a Select statement from the sysibm.sysdummy1 table which
will always return me one row.
Further, i need to capture the result of the execute Immediate into a
variable of some data type and use it.

How can i achieve it.

Eg in a procedure

Set str = 'Select current timestamp from sysibm.sysdummy1'
Execute Immediate str;

So, if i want to capture the value returned in a variable of
appropriate data type, what could i do...
Thanks a lot.

Rahul Babbar

[Ian]

Rahul Babbar wrote:

Hi,

I have the following doubt.

Suppose I use the execute immediate statement and the statement to be
executed is a Select statement from the sysibm.sysdummy1 table which
will always return me one row.
Further, i need to capture the result of the execute Immediate into a
variable of some data type and use it.

How can i achieve it.

Eg in a procedure

Set str = 'Select current timestamp from sysibm.sysdummy1'
Execute Immediate str;

So, if i want to capture the value returned in a variable of
appropriate data type, what could i do...

Use SELECT ... INTO:

select current timestamp into your_variable
from sysibm.sysdummy1

Please note, you don't need to do a query for this:

set your_variable = current timestamp;

[Serge Rielau]

In DB2 9.5 you can do SET ? = (SELECT .....)

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

[Rahul Babbar]

On Feb 7, 10:28 pm, Ian <[e-mail]:

Rahul Babbar wrote:

Hi,

I have the following doubt.

Suppose I use the execute immediate statement and the statement to be
executed is a Select statement from the sysibm.sysdummy1 table which
will always return me one row.

Further, i need to capture the result of the execute Immediate into a
variable of some data type and use it.

How can i achieve it.

Eg in a procedure

Set str = 'Select current timestamp from sysibm.sysdummy1'
Execute Immediate str;

So, if i want to capture the value returned in a variable of
appropriate data type, what could i do...

Use SELECT ... INTO:

select current timestamp into your_variable
from sysibm.sysdummy1

Please note, you don't need to do a query for this:

set your_variable = current timestamp;

Hi,

Select current timestamp from sysibm.sysdummy1 was a wrong example to
simplify the scenario.

I have to execute the query dynamically.

So, it's something like, I have to use execute immediate(or any other
command which executes it dynamically) and then capture the result
into a variable.

set var = Execute Immediate(str)

doesn't seem to work...Further, it seems that doing a select in
Execute Immediate is not allowed(i don't exactly remember the error
code for this), but this is my requirement.
I have to somehow do dynamic select, may be by using something other
than Execute Immediate.

What could i do?

Thanks a lot

Rahul