Henk van der Meer wrote:

>Hello,
>
>Now and again I am trying to start using stored procedures. I am a
>professional software developer and have written software in the general
>languages (c, c++, pascal, basic,.), but the how and why of stored
>procedures still escapes me; especially the how.
>
>

In a nutshell, you can, in a procedure, perform all ordinary sql
statements + a bunch of additionnal elements(for...select, do...while,
when...do, if...then...else).
You can declare any number of input, output, or internal parameters,
which are accessed and used in the same way parameters are usually
accessed (i.e. using a colon). A stored procedure that returns values is
accessed like an ordinary table, and the output parameters are accessed
like fields. A stored procedure that doesn't return values is simple
executed, using the "execute procedure <proc_name>(<params>)" syntax.

>For example I am trying to write a stored procedure that takes two integer
>parameters A and B. If A isn't NULL it returns A else it returns B. I use
>the integers as id's.
>
>Suppose the stored procedure is called P_A_OR_B.
>
>I am able to use it like this: select * from P_A_OR_B(1,1). But not like
>this:
>
>Select * from table_x x
>Join table_y y on y.id = P_A_OR_B(x.id1,x.id2)
>
>The most frustrating thing is the error message: function unknown.
>
>I have lots of questions but will start with 2.
>
>1) If the stored procedure was written properly would the second usage (the
>join) be possible?
>
>

No. A selectable stored procedure (i.e., one that returns values), is
treated just like a table or a view. Therefore, it can only be accessed
with a select statement (or a subselect). Your second example should
have been:

Select * from table_x x, P_A_OR_B(x.id1,x.id2) p
Join table_y y on y.id = p.return_value

or

Select * from table_x x
Join table_y y on y.id = (select return_value from P_A_OR_B(x.id1,x.id2))

Naturally, by "return_value", I mean, the name of the return parameter you are trying to access.

The reason you get a "function unknown" message is because a "function"
is not the same thing as a "procedure" in FireBird. A procedure is
basically just a bit of SQL code stored in your database and accessed as
though it were a table (except of course, when it doesn't return any
values, but merely performs a certain task). A function is different. It
is an external piece of code written into a shared library, and declared
in your DB. When you call the function, FireBird simply calls the
external function of the library. As a result, functions are generaly
used for all-purpose data transformation/manipulation, that is not
dependant on your DB at all (I don't think a function can access the
data in your DB at all), whereas procedures are used for performing
tasks or viewing data, entirely related to your DB.

Procedures are very useful, and can allow to do data-intensive
operations sometimes much faster/easier, because, a) It runs at the
server-side, so you don't have to transfer the data you're working with
to the client, and b) Its simple, SQL-orientated language allows for
certain features which cannot be done in ordinary SQL, so doing them
would normally require loading all the data to the client-side,
processing it there, and then performing the necessary operations in the
DB. One other advantage/use of a procedure, is that it is selectable,
whereas an ordinary query is not. In other words, you can store a query
in your procedure, and then, select from the procedure, which is
sometimes necessary for making peculiar or diffcult result-sets.

One warning however: avoid putting a lot of buisness logic in
procedures, because they are hard to debug, and they can sometimes get
awefully complicated due to their lack of many elementary language
features which most of us have become accustomed to. Also, be even more
wary of triggers (which are basically, stored procedures without
parameters, that execute automatically when something happens to a table
(UPDATE / INSERT / DELETE)). While they are very useful in certain
cases, and allow to do things that would otherwise be impossible (e.g.
replication), they are not to be abused of. I have found that a DB with
too many triggers becomes hard to work with and hard to maintain,
because often, the triggers will fire when you weren't expecting them
and will cause bugs. Solving these bugs usually means using ugly
workarounds. Also, they often fire when they are not really needed, thus
causing unnecessary slow-down. So, what I'm saying is, don't put too
much buisness logic in procedures nor triggers, especially if it
involves performing operations on several different tables, which can
often lead to complicated situations.

>2) What can I read to start using and understanding stored procedures? The
>only thing I found so far is:
>Introduction to InterBase Stored Procedures and Triggers
>By Bill Todd, Borland Developers Conference San Diego 2000.
>
>

In the PDF manuals provided with FireBird, you should find all the
documentation necessary, under the sections about procedures. That's all
you need to understand how to use them. The syntax is explained in every
detail.

HTH,
Jonathan Neve.