Subject Re: Select procedures
Author Louis van Alphen
Someone (Dan??) wrote:
>I'm having some challenges with Crystal right now, so I ran some tests from
>SQL explorer using the Intersolv ODBC driver.

>The first test simply calls a select stored procedure:

>SELECT * FROM BANKBAL('06/07/2000')

>The trace is sel.log.

>The 2nd test calls an exec stored procedure:

>Execute Procedure PostJE('1/1/1980',0,0,0)

>The 4th param is a return param, but there's no way to do that in SQL
>explorer so I just loaded a dummy value instead.

The way I use Crystal to select or execute from SPs are as follows.

The reason you use Crystal to access the DB is to get info on a report.
Therefore my SPs _always_ contain suspends. The effect of hooking a Crystal
report to a SP is the same as 'SELECT * FROM SP'. If I want to execute a SP
with in and out params, I call this SP from a 'wrapper' SP that takes all
the arguments and contains suspens to return the results. E.g.

create procedure wrapper_sp(in_value date) returns (out_value integer)
execute procedure sp_to_be_executed :in_value returning_values :out_value;

create procedure sp_to_be_executed(in_value date) returns (out_value integer)
select sum(column) from table where date_column > :in_value into

This works very well............

Louis van Alphen