Thanks a lot.

So, does this mean that I cannot (or it's not a good idea) to use the
IN predicate at all in a stored procedure? The example you gave using
2 stored procedures is helpful :-) but it still boils down to not
using the IN predicate.

Or... can i do something like:

create procedure usingInPredicate ( inList varchar(500))
returns (studentId integer)
as
declare variable someValue integer;
begin

for
select a.some_field
from a inner join
b on a.fielda = b.fielda inner join
c on c.fieldb = b.fieldc
where
c.studentId IN (
select studentId FROM secondStoredProcedure( inList
)
)
into :someValue
DO
begin
// process here....

// return values we want

studentId = :someProcessedValue
suspend;
end

end!!

where the secondStoredProcedure extracts the comma separated values of
inList and returns(suspends) after each value is extracted and then
'beheads' the list there, too....

winston

PS. i use ur book as well.. great stuff!



--- In firebird-support@yahoogroups.com, Helen Borrie <helebor@t...>
wrote:

> At 12:45 AM 13/05/2005 +0000, you wrote:
> >I have been creating stored procedures using the FOR - SELECT - DO
> >constructs but find it sometimes slow for large databases (for

schools).

>
> Poorly performing, looping SPs often display a lot of extraneous

logic.

> Looping cleanly through a cursor set on the server side should

never be

> slow. However, any SELECT calls inside a SP are no less prey to

poor

> indexing than those from clients! If you are looping through joined

sets,

> there may be avoidable agony in the choice of search criteria for

the joins.

>
>
> >What I discovered to be faster is incorporating the IN predicate in
> >joined queries when the key fields to select are known and limited

to

> >a few values ( e.g. -
> >SELECT a.* FROM studentTable a INNER JOIN ..... WHERE a.studentId

IN

> >(1009,2220,5678) AND ....
>
> What makes this specific syntax fast for small lists, in this case,

is the

> fact that the search is doing an OR-ed indexed search for each value

in the

> list. The longer the list, the more searches. This syntax

restricts you

> to 1000 list members.
>
> Don't use SELECT * in SPs, since, in SPs, you are always passing

column

> values to variables in a strict order.
>
>
> >I want to do the same thing this time in stored procedures. I

tried

> >to select the values in a first query but can't get the second

query

> >using the IN predicate to work. I've used a string variable to

store

> >the values selected in the first query and feed it into the IN

clause.

> >How can I implement this?
>
> You could implement a form of list-processing, by writing a second

(i.e.

> first!) SP that processes the string. At each iteration of the

loop, you

> call this SP, and have it return the leftmost value and then, in

your loop,

> "behead" the input string for the next iteration of the loop.
>
> Assuming your input string is presented as a comma-separated list

with no

> comma following the last list member....
>
> create procedure getlistitem (inlist varchar(500))
> returns (currentval integer)
> as
> declare currentstring varchar(9) = '';
> declare currentchar char = '';
> begin
> currentval = 0; /* or -99999 or whatever you want a non-value to

be */

> while (currentchar is not null) do
> begin
> currentchar = substring(inlist from 1 for 1);
> if (currentchar = '' or currentchar = ',') then
> currentchar = null;
> if (currentchar is not null) then
> currentstring = currentstring || currentchar;
> end
> if (currentstring <> '') then
> currentval = cast (currentstring as integer);
> end
>
> Then, in your main (second) procedure:
>
> create procedure blah (inlist varchar(500))
> returns (var1..... etc.) as
> declare AllDone smallint = 0;
> declare CurrentKey integer = 0;
> declare CurrentChar char = '';
> begin
> while (AllDone = 0) do
> begin
> execute procedure getlistitem(:instring)
> returning_values(:CurrentKey);
> if (CurrentKey > 0) then
> begin
> /* assuming here that your query returns only one row.

If not,

> you will have to use the FOR loop. */
> /* FOR */
> SELECT a.val1, etc. FROM studentTable a
> JOIN bTable b
> ON b.studentId = a.studentId
> WHERE a.studentId = :CurrentKey
> INTO :val1, ...........etc. ;
> /* DO and omit the terminator */
> SUSPEND;
> CurrentKey = 0;
> while (CurrentChar <> 'X') do
> begin
> CurrentChar = substring(inlist from 1 for 1);
> if (CurrentChar = ',') then CurrentChar = 'X';
> if (CurrentChar is not null and CurrentChar <>'')

then

> inlist = substring(inlist from 2);
> else
> CurrentChar = 'X');
> end
> CurrentChar = '';
> end
> if (inlist is null or inlist = '') then
> AllDone = 1;
> end
> end
>
> This is just to give the idea - if you do something similar, make

sure you

> test the logic (mine and yours!) thoroughly.
>
> ./heLen