Combining queries in data set.

mohtech

Hi all,

In a dataset I need to call a stored procedure before
sql select query.

First I need to call a stored procedure, that insert ids in a temporary table
And after I need to execute a select query that needs to read ids from
the temporary table.

Is it possible to call the stored procedure and the select query in the same data set ?
or do I need to join two data sets ?

I spent some time on this,
and the only solution I've found was
to write a stored procedure that call the first stored procedure
and the select query. That stored procedure
is used in the dataset.

But I need to defined select query in birt dataset, instead of
writing a stored procedure every time.

Any ideas to call a stored procedure and a select query, both with parameters ?

Thanks in advance.

Hans_vd

Hi,

If you happen to be on an Oracle database:
you'll need to create a output parameter of type SYS_REFCURSOR in your stored procedure. The last step in the procedure would be to open the refcursor:
http://enterprisesmartapps.wordpress.com/2011/02/24/oracle-stored-procedure-data-set-in-birt/ for more info

Other databases work the same way, only not with SYS_REFCURSOR type.

Regards
Hans