Subject Re: [IB-Architect] Select procedures
Author Paul Reeves
Phil Shrimpton wrote:
> > From: Greg Deatz [mailto:gdeatz@...]
> Hi,
> > You can't necessarily count on the SQL source to have anything....
> >
> > but could you look at the BLR of the stored proc, to determine if it has a
> > suspend in it? if so, then "select", if not, then "execute"?
> This is not necessarily so. In a number of recent threads on other
> 'support' newsgroups/mailing lists, there have been a number of people
> having problems getting return values from 'Execute' SP. The 'fix' has been
> to put a SUSPEND in the SP, and it is something that I have been doing for a
> number of years. I am not sure if this is a 'bug' with IB, or a
> documentation error, but it works and does not seam to cause any problems.
> But it means that you can't really count the 'SUSPEND's' to determine what
> sort of procedure it is.
> Surely if you just call 'SELECT a,b,c from MyProcedure(x,y,z)'
> - a 'Select' SP will give you a result set
> - an 'execute' SP with Suspend will return a 'row'
> - an 'execute' SP without Suspend will return nothing
> - an 'execute' SP without return params will return nothing

I just tried calling an execute SP thus:

select *
from calc_price(1,200);

and it generates no result set - it should return a scalar value. ( I tried
under WISQL, and my own SQL Profiler)

This statement runs fine, both in WISQL and my SQL Profiler:

execute procedure calc_price 1, 200;

The BDE based SQL Explorer wont accept the above statement and I tried similar
attempts with QuickDesk, and IB Workbench (based on IBO) and they didn't return
a value. IBAdmin got the right answer, based on FIB.

This is a tricky problem. Iterating through the two options is not really a
solution, but I can't think of a better one, for now.


Paul Reeves
Fleet River Software
* v1.0 of the InterBase Heartbeat - Database Monitor is now available. *
* Visit to download an evaluation copy. *