Chuck,

At 08:08 PM 5/07/2005 -0700, you wrote:

>Well, after some back and forth on this topic, plus searching the archives, it
>looked like I need a Selectable SP with SUSPEND.
>
>This is my SP: (taken from IB_Expert)
>
>
>SET TERM ^ ;
>
>CREATE PROCEDURE GETALL_ML (
> K INTEGER)
>RETURNS (
> MLUPITEM VARCHAR(128),
> MLITEM VARCHAR(128),
> MLID INTEGER,
> REC_COUNT INTEGER,
> FETCH_COUNT INTEGER)
>AS
>begin
> /* Procedure Text */
>
>select Count(ml.ml_id) from Masterlibrary ml INTO Rec_Count;
>
>Fetch_Count = 0;
>
> for select m.ml_id, MyUDF(m.ml_item,:K) ,
> MyUDF(m.ml_upper_item,:K) as Upper_Item
> from MasterLibrary m
> Order by 3
> INTO
> :MLID,
> :MLITEM,
> :MLUPITEM
> do begin
> Fetch_COUNT = Fetch_COUNT + 1; //Helen felt I needed this here

Not really. I suggested you counted the rows as they arrived on the
client. I mentioned the in-procedure counting a propos another reference
you made, but in fact your mechanics here are such that you are preventing
this count being any use at all. See later comments.

> suspend;
> end
>end
>^
>
>
>SET TERM ; ^
>
>I'm using a IBOQuery for the Select SP as:
>SELECT * from MYSP(:K) where f_substr(UPPER(<word/phrase to check in string>),
>Upper_Item) > -1
>
>(for all these tests so far, it has been simply "SELECT * from MYSP")

Using a WHERE clause to limit the rows returned from a selectable stored
procedure, while possible, is not even slightly wise. A SSP is not a
table!! It is not even a view. It is completely virtual set, concocted
with smoke and mirrors. By doing this, you are causing the procedure to
generate every possible row and then, at the interface, to either return or
abandon each row in turn, according to the WHERE criteria. There is
absolutely no way that such a search can be optimized. Worse still, in
your current case, of course there is just no opportunity for a callback to
happen!

Use the procedure itself to construct the entire set. Move your search
criteria into a parameter and totally avoid putting WHERE conditions on SP
outputs.

You currently have:

SELECT * from MYSP(:K)
where f_substr(UPPER(<word/phrase to check in string>), Upper_Item) > -1

Take the stuff that's happening here in the WHERE clause and, instead, pass
an argument to the SP that the SP will process internally.

CREATE PROCEDURE GETALL_ML
(
K INTEGER,
SearchArgument VARCHAR(128) /* or length you need */
)
RETURNS (
MLUPITEM VARCHAR(128),
MLITEM VARCHAR(128),
MLID INTEGER,
/* REC_COUNT INTEGER, */
FETCH_COUNT INTEGER)
AS
begin

/* select Count(ml.ml_id) from Masterlibrary ml INTO Rec_Count; */
/* No use. The full record count doesn't belong here. If you MUST have the
full record count in the client, you should fetch it on the client. See
following note. */

/* DO PLEASE INITIALISE VARIABLES!!!!!!!!!!!)

mlupitem = '';
mlitem = '';
mlid = -1;

Fetch_Count = 0;
/* lose the table aliases. They don't belong in single-table queries. */
for select
ml_id,
MyUDF(ml_item,:K) as Item,
MyUDF(ml_upper_item,:K) as Upper_Item
from MasterLibrary
/* Order by 3 */
where SOMETHING containing :SearchArgument
order by ml_upper_item /* which one hopes is indexed */
INTO
:MLID,
:MLITEM,
:MLUPITEM
do begin
if (mlupitem = '' or mlitem = '' or mlid = -1) then
exit;
Fetch_COUNT = Fetch_COUNT + 1; //Helen felt I needed this here...HMMMM
suspend;
end
end

Trying to get a row count at the beginning doesn't work, since the number
of rows in the output won't be known until the client has fetched the last
row. Whatever you do to try to get this rowcount for use with your
progress bar, of course it must happen *before* you begin.

If you have no suitable, simple SELECT COUNT(*) DSQL query that you can run
beforehand, and you simply must have this rowcount, then you leave yourself
no option but to run the SP logic twice (effectively). The real answer is,
of course, to have TWO SPs. One (which you EXECUTE, not select from) will
give you the record count. You'll call this first, before you call the
SSP, to initialise your progress bar:

CREATE PROCEDURE COUNTALL_ML
(SearchArgument VARCHAR(128) /* or length you need */)

RETURNS (REC_COUNT INTEGER)
AS
declare variable mlid integer = -1;
begin
rec_count = 0;
for select ml_id from MasterLibrary
where SOMETHING containing :SearchArgument
INTO :MLID
do begin
if (mlid <> -1) then
rec_count = rec_count + 1;
end
end

Helen