At 12:16 AM 7/09/2004 +0000, you wrote:

>Hellom
>
>I'm currently using Firebird 1.5.1 and am writing some stored procedures.
>
>My question is best explained by a quick example. I'm writing a
>procedure to generate a list of books. I wish to allow the client app
>to specify an order and order direction (ASC/DESC) as a parameter.
>
>The following doesn't (surprisingly) work:
>
>SELECT ...
>ORDERBY :order :orderdir;
>
>I take it is not possible to build parts of the query as variables in
>this manner? Thinking about it, this would negate the pre-compiled
>nature of the queries.

As a general rule, you can't pass metadata objects or attributes as
parameters to a SP. As you rightly observe, the structure of queries
inside SPs is precompiled. As an exception to the rule, you can pass
strings and construct a custom query string inside a SP using the EXECUTE
STATEMENT syntax (please read the release notes on this).

For an ordered set, I don't think EXECUTE STATEMENT can help you here.

You can pass parameters for an internal WHERE clause in a procedure call.

What you can do is call the selectable procedure with an ORDER BY clause
and a WHERE clause, if you don't want to do the WHERE via parameters. It's
slow on the server side, since the entire procedure will have to execute
before the sorting can begin, but it might be acceptable if the unmodified
output set is small.

>So... am I stuck with generating and performing the query in the
>client? I have a couple of instances where this sort of thing is
>necessary (e.g. generating a WHERE clause when certain parameters are
>passed).

For this, I'd rather create a view to which I pass the WHERE and ORDER BY
clause at run time. That way, indexes will be used.

./heLen