🚀 go-pugleaf

Thanks for the reply, Wayne.  I'm just having a bit of cognitive
dissonance between SQL dialects.  In MSSQL, I can construct a stored
procedure that returns a query result without declaring or populating
any return parameters.  IOW

CREATE PROCEDURE MyProc
AS
BEGIN
Select * from MyTable
END

is perfectly legitimate and returns a result set to the caller.

The IB procedure I'm writing will ultimately DELETE records from a
table, not SELECT them, so will the approach be any different?
Furthermore, where can I find decent online guidance for writing IB
SPs?  The docs at http://www.firebirdsql.org/index.php?op=doc leave a
lot to be desired.

Tks again,
PH
On May 10, 12:37 pm, "Wayne Niddery [TeamB]" <wnidd...@chaffaci.on.ca>
wrote:

> That's because, in the context of a stored procedure, the Select statement
> is incomplete. I assume you are expecting one or more rows to be returned
> from this procedure? The procedure must declare return variables, and the
> select statement must place values into those variables.
>
> CREATE PROCEDURE name
> returns (
>   fld1 integer,
>   fld2 varchar(10),
>   fld3 <whatever ...>
> )
> AS
>     [...]
>  SELECT fldA, fldB, fldC FROM Mytable WHERE CAST(datetime AS DATE) <
> :variable_name
>  INTO fld1, fld2, fld2;
>
> That will return *ONE* row (and if the select results in more than one, an
> error).
>
> If you need to allow for more than one row then it needs to be:
>
>  FOR
>     SELECT fldA, fldB, fldC FROM Mytable WHERE CAST(datetime AS DATE) <
> :variable_name
>     INTO fld1, fld2, fld2
>  DO BEGIN
>     SUSPEND;
>  END;
>
> And the calling application needs to select from this procedure rather than
> simply executing it:
>
>     select * from name;
>
> --
> Wayne Niddery - Winwright, Inc (www.winwright.ca)
> "If there is any principle of the Constitution that more imperatively
> calls for attachment than any other, it is the principle of free
> thought - not free thought for those who agree with us, but freedom for
> the thought that we hate." - Oliver Wendell Holmes