At 09:42 AM 11/12/2008, you wrote:

>In mysql there is something like a selectable stored procedure, eg.
>
>Begin
>Select * from table x
>where x.ID = @PARAM;
>End

Maybe; but this example is not of a selectable stored procedure, but of a query whose output is wrapped to behave like a table. Standard SQL has derived tables for that behaviour, implemented in Firebird from v.2.0 forward.

Firebird has genuine selectable stored procedures, i.e., procedures that you code in such a way as to create entirely virtual data a row at a time and pass out to a client buffer as a set. The form of this is:

create procedure blah (inputa sometype, inputb, sometype,....)
returns (
field1 sometype,
field2 someothertype,
.....)

as
declare vara.....;
declare....;
begin
for select ......
.....
where xx = :inputa and yy = :inputb
into :vara, :varb, :varc, :field1, ...
do
begin
<mess around with the current set of variables>
.....
field2 = <some expression> -- if you like...
suspend;
...
end
...
end

That's very rough, but there is documentation available...Firebird's PSQL is a pretty useful language.

>You can call this procedure via DOTNET, pass paremeters en fill a
>dataset with it easily
>
>What's the best option to choose for in an firebird 2.0 environment
>
>1. Query with :PARAM?
> But can pass parameters to a stored query via DOTNET?
>2. Procedure
>3. View (with parameters??? didn't find this)
>
>I especially want to avoid the SELECT into statement which i found in
>many blogs as i really need the select all (*) option

You seem quite confused about what does what in SQL. You can always query with parameterised search clauses: this is the recommended way to work in SQL. Your application "prepares" such a statement once and can execute it many times, assigning different values to parameters each time.

The Firebird API expects parameters in a predescribed order and will receive a statement similar to yours in the form
Select * from table x where x.ID = ? and y = ?

API interpretation layers, such as your .NET driver, have their own ways of setting and tracking these parameters and the values that are subsequently assigned to them. I don't do .NET myself - if you don't know the answer, then you need to ask on the firebird-net-provider list.

I use Delphi. The usual convention for Delphi interpretation layers is like this:

Select * from table x where x.ID = :param1 and y = :param2

That is, Delphi deals with *named* parameters at the client and the access layer itself keeps track of the associated position of each parameter in the statement.

In all cases, you have to understand clearly that parameters are not variables. So, even if the *values* of param1 and param2 are always going to be the same, you can't issue a statement like:

Select * from table x where x.ID = :param1 and y = :param1

For 2), selectable stored procedures (proper Firebird ones, that is) can be invoked with either constants or placeholders for constants. These are usually referred to as "input parameters", although strictly speaking, they are arguments. The form is

SELECT * FROM MYPROC(?,?,?,..) -- one for each defined argument.

Again, your interface layer (.NET) will have its own convention for expressing the placeholders for these arguments.

You can't use SELECT with SPs that have not been written with SUSPEND statement[s] to pass the defined output results to the buffer as rows of a set. EXECUTE PROCEDURE is used to call such "executable procedures".

For 3), you can't define views with parameters. A view is a way of pre-defining an output set. At DML time, as with tables, view parameters are used to define search criteria (in your WHERE clause), so usage of parameters with a view is just the same as with tables.

Derived tables (taking you back to the original question) are really quite similar to views, except that the sets they define are constructed at run-time, in subquery expressions. The usage of parameters in subquery expressions is the same as with tables or views.

Hth,
./heLen