🚀 go-pugleaf

philhege wrote:
>
> CREATE PROCEDURE name
> AS
> DECLARE VARIABLE variable_name DATE ;
> BEGIN
> variable_name = EXTRACT(DAY FROM current_date) + 1 ;
> SELECT * FROM Mytable WHERE CAST(datetime AS DATE) < :variable_name ;
> END
>
> And am getting the following error:
> Token unknown - line 9, char 68
> ;

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