--- In firebird-support@yahoogroups.com, "killerion" <killerion@...>
wrote:

>
> I want to write a stored procedure to search among a several tables
> and diferent types of search paramenters but always one parameter at a
> time the reason I want to do this is to simplify a huge portion of the
> code in my aplication, what I've been thinking of is just sending the
> search paramenter and search type to the stored procedure and through
> a series of ifs decide whicch is the apropiate select query kind of
> like this:
>
> begin
> if ( SEARCH_TYPE = 1 ) then
> SELECT * FROM my_table WHERE row1 STARTING WITH :SEARCH_PARAM
> else if ( SEARCH_TYPE = 2 ) then
> SELECT * FROM my_table WHERE row2 STARTING WITH :SEARCH_PARAM
> else if ( SEARCH_TYPE = 3 ) then
> SELECT * FROM my_table WHERE row2 STARTING WITH :SEARCH_PARAM
> suspend;
> end
>
> now my problem is how do I return multiple rows with this procedure?

Every call to suspend returns what is currently in the output
parameters to the client.

Change your

select blah from table where ... into :blah;
.
.
.
suspend;

to

for select blah from table where ... into :blah do
begin
suspend;
end

(and remove the final suspend.)

> and is this a good idea or can you suggest something better?

It's not really the way I would do it unless the search type makes
sense in your design, it doesn't at a generic case. Providing it
doesn't give you a maintenance nightmare and the procedure doesn't
grow to the point of being unreadable, it seems OK to me.

Adam