At 08:07 AM 14/11/2006, you 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?

Well, you won't do it with an arbitrary "select *" as your
specification. You need to define output arguments and then use a
named list of columns inside a FOR SELECT <list of column_names)...
INTO <list of output variables>.

Note, assuming that "row1" and "row2" are columns in the table
structure, as they would need to be for the statements to be valid
SQL, I cannot see any difference here between SEARCH_TYPE 2 and 3
....is it a typo and you really have a column named "row3"? Assuming
yes, let's consider an example that targets 3 possible WHERE search
keys, according to whether the search is on columns named suburb,
city or province:

create procedure GetMySet (
search_type smallint,
search_param varchar(20) character set blah)
returns (
column01 SomeDataType,
column02 SomeDataType,
column03 SomeDataType.
....)
as
begin
if ( SEARCH_TYPE = 1 ) then
FOR
SELECT column01, column02, column03, ..and the rest
FROM my_table
WHERE suburb STARTING WITH :SEARCH_PARAM
INTO :column01, column02, column03, ..etc
DO SUSPEND;
ELSE if ( SEARCH_TYPE = 2 ) then
FOR
SELECT column01, column02, column03, ..and the rest
FROM my_table
WHERE city STARTING WITH :SEARCH_PARAM
INTO :column01, column02, column03, ..etc
DO SUSPEND;
ELSE
if ( SEARCH_TYPE = 3 ) then
FOR
SELECT column01, column02, column03, ..and the rest
FROM my_table
WHERE province STARTING WITH :SEARCH_PARAM
INTO :column01, column02, column03, ..etc
DO SUSPEND;
end

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

It could work, provided you always want to return a set having the
same structural specification, i.e. you plan to select the same
columns regardless of the input switch (SEARCH_TYPE) you pass in.

If you were able to normalise your table, so that the columns row1,
row2 and row3 were merged into a single column carrying data, and you
added a switch column to the table that (using my example) stores 1
if the data is for suburb, 2 if it is for city and 3 if it is for
province, then you wouldn't need a SP, just an ordinary DSQL
statement using a CASE specification.

./heLen