At 09:20 AM 10/07/2003 +0000, you wrote:

>Thanks Helen.
>
>Am I right in thinking that the performance benefit that accrues to using
>parameterized queries in some external programming language will also
>accrue to using procedures in Firebird (since these are also paramterized)?

well...SPs can speed things up if you are performing tasks that would
otherwise involve a lot of server traffic. The *thing* about
parameterising the actual SQL statement (be it a direct DML or SELECT, or
an EXECUTE PROCEDURE) is that it enables you to keep the statement prepared
for multiple calls. Calling a stored procedure is no different to any
other DSQL statement in this respect. You need to "parameterise" the input
arguments (confusingly referred to in Borland IDEs as "input parameters")
to take advantage of the prepared statement.

This approach, in Delphi, for example, does not let you implement a
parameterised statement:

procedure ADataModule.ExecuteMyProc (Argument1: integer; Argument2: string);;
begin
....
MyDataset.SQL.Clear;
MyDataset.SQL.Add ('EXECUTE PROCEDURE MyProc(Argument1, Argument2)');
MyDataset.Prepare;
MyDataset.ExecSQL;
...
end;

whereas this does:
procedure ADataModule.SetUpMyProcSQL;
begin
MyDataset.SQL.Clear;
MyDataset.SQL.Add ('EXECUTE PROCEDURE MyProc(:Arg1, :Arg2)');
end;

procedure
procedure ADataModule.ExecuteMyProc (Argument1: integer; Argument2: string);
begin
if not MyDataset.Prepared then
begin
SetUpMyProcSQL;
MyDataset.Prepare;
end;
MyDataset.ParamByName('Arg1').AsInteger := Argument1;
MyDataset.ParamByName('Arg2').AsString := Argument2;
MyDataset.ExecSQL;
end;

The call to SetUpMyProcSQL only occurs once with this approach.

heLen