CODEWITHSUNDEEP
stored-proceduressyntaxfirebird
williamsdb
williamsdb

Reputation: 1064

Firebird Stored Procedure syntax - rejecting ;

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;

Upvotes: 0

Views: 673

Answers (1)

JustMe
JustMe

Reputation: 2383

You missed INTO clause! the select should look like: 

   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 INTO :FORENAME, :SURNAME, :STATUSNAME, :STATUS_COUNT;

Upvotes: 1

Related Questions