I am using FB 2.5
I am trying to create a stored procedure which has a number of input
parameters, each of which may or may not need to be used in the procedure
depending on the value passed. The procedure is basically this
CREATE PROCEDURE XX
A VARCHAR(8),
B INTEGER,
C INTEGER,
D CHAR(1)
)
RETURNS (
return_variables
) AS
internal_variables
BEGIN
FOR SELECT fields
FROM tables
WHERE conditions
INTO return_variables
DO BEGIN
statements
SUSPEND;
END
The input parameters may have their values set or not. If the values are set,
the WHERE statement needs to take account of this.
For example : A = XXX, B = 0, C = 0, D = empty string the WHERE clause would
need to be
WHERE conditions AND FIELD_A = :A
or : A = empty string, B = 99, C = 1, D = empty string
WHERE conditions AND FIELD_B = :B AND FIELD_C = :C
This procedure returns a large number of records and takes a noticable time to
run if none of the input parameters is set, but returns very few records if one
or more of the parameters is set. Because of this, changing the procedure so
that it does not use any input paramters then running the query
SELECT * FROM X WHERE A =...
to get the desired result set is not a sensible option, as the FOR SELECT
statement would always have to select all possible records then decide which to
return.
I could use FOR EXECUTE STATEMENT and build the query string taking account of
the values passed, or I could restructure the query so that it has a seperate
FOR SELECT statement for each possible set of input paramters (this does not
mean 16 statements as most combinations of input paramters are never used, e.g
if A is set, B and C will not be set). I am however wary about using FOR
EXECUTE STATEMENT as the instructions for its use warn that it may be slow, so
I may get no speed gain over using the SELECT.. WHERE contruct with no input
parameters.
My question is, which of these 2 methods would be the most efficient in terms
of the speed with which the result set is returned?
