Subject | Re: [firebird-support] Firebird Stored Procedure syntax - rejecting ; |
---|---|
Author | Thomas Steinmaurer |
Post date | 2011-03-21T13:15:21Z |
Hi,
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!
> I am trying to create the following stored procedure in Firebird but IYou have to extend the select statement with an INTO part assigning the
> 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;
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!