Hi Thomas

Thanks for the quick response.

Unfortunately this doesn't work. I now get "Token unknown - line 37, column 1" which is the INTO line.

Neil

--- In firebird-support@yahoogroups.com, Thomas Steinmaurer <ts@...> wrote:
>
> Hi,
>
> > I am trying to create the following stored procedure in Firebird but I
> > must be getting the syntax wrong as it is complaining as follows:
> > Statement #3:
> > SQL error code = -104
> > Token unknown - line 23, column 46
> > ;
> >
> > The stored procedure itself is as follows - what am I doing wrong?
> > SET AUTODDL OFF;
> > SET TERM ^ ;
> >
> > CREATE PROCEDURE SP_KPI
> > (
> > STAFFID INTEGER,
> > FROM_DATE DATE,
> > TO_DATE DATE
> > )
> > RETURNS
> > (
> > FORENAME VARCHAR(50),
> > SURNAME VARCHAR(50),
> > STATUSNAME VARCHAR(50),
> > STATUS_COUNT INTEGER
> > )
> > AS
> > BEGIN
> > SELECT s.forename, s.surname, v2.statusname, count(v2.statusname) as
> > STATUS_COUNT
> > FROM Vacconthist v
> > JOIN Staff s ON v.staffid=s.staffid
> > JOIN Vacstatus v2 ON v2.statusid=v.status
> > WHERE (s.staffid = 867) AND (v.LASTUPDATE BETWEEN '01-Jan-2011' AND
> > '08-Mar-2011') AND (v.status> 9 AND v.status< 99999)
> > GROUP BY s.forename, s.surname, v2.statusname;
> > END
> > ^
> >
> > SET TERM ; ^
> > COMMIT WORK;
>
> You have to extend the select statement with an INTO part assigning the
> output parameters the field values of the result set. If the select
> statement returns multiple rows, then you have to wrap that into a FOR
> SELECT ... statement. You also need SUSPEND to actually return something
> to the caller.
>
> For example:
>
> SET AUTODDL OFF;
> SET TERM ^ ;
>
> CREATE PROCEDURE SP_KPI
> (
> STAFFID INTEGER,
> FROM_DATE DATE,
> TO_DATE DATE
> )
> RETURNS
> (
> FORENAME VARCHAR(50),
> SURNAME VARCHAR(50),
> STATUSNAME VARCHAR(50),
> STATUS_COUNT INTEGER
> )
> AS
> BEGIN
> FOR
> SELECT
> s.forename
> , s.surname
> , v2.statusname
> , count(v2.statusname)
> FROM
> Vacconthist v
> JOIN Staff s ON v.staffid=s.staffid
> JOIN Vacstatus v2 ON v2.statusid=v.status
> WHERE
> (s.staffid = 867)
> AND (v.LASTUPDATE BETWEEN '01-Jan-2011' AND '08-Mar-2011')
> AND (v.status > 9 AND v.status < 99999)
> GROUP BY
> s.forename
> , s.surname
> , v2.statusname
> INTO
> :FORENAME
> , :SURNAME
> , :STATUSNAME
> , :STATUS_COUNT
> DO
> BEGIN
> SUSPEND;
> END
> END
> ^
>
> SET TERM ; ^
> COMMIT WORK;
>
>
> You then use the SP like a table.
>
>
> select ... from SP_KPI (...);
>
>
> HTH.
>
>
> --
> With regards,
>
> Thomas Steinmaurer
> Upscene Productions
> http://www.upscene.com
> http://blog.upscene.com/thomas/
>
> Download LogManager Series, FB TraceManager today!
> Continuous Database Monitoring Solutions supporting
> Firebird, InterBase, Advantage Database, MS SQL Server
> and NexusDB!
>