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!