Hi Henrik,

> Hello, I get an error when trying to write a (test) stored procedure
> using Firebird Workbench. I don't know if this is the correct forum, but
> I'll give it a try.
>
> I have 3 different codes, all trying to accomplish the same, all giving
> different errors.
>
> Case 1:
>
> SET TERM ^! ;
> CREATE PROCEDURE TEST1 AS
> BEGIN
> SELECT COUNT(*)
> FROM LEVERANDOR
> SUSPEND;
> END ^!
> SET TERM ; ^!
>
> This is the error I get: "ISC ERROR MESSAGE: Dynamic SQL Error, SQL
> error code = -104, Token unknown - line 6, char 3 SUSPEND".

A SELECT statement in a procedure always needs an INTO clause. And
you need to finish the statement with a ";".

SUSPEND is only needed for procedures that need to return output data
when called via a SELECT ... FROM procedurename statement.

> If I comment out SUSPEND, I get another error: "ISC ERROR MESSAGE:
> Dynamic SQL Error, SQL error code = -104, Token unknown - line 7, char 1
> END".

See above.

> Case 2:
>
> SET TERM ^! ;
> CREATE PROCEDURE TEST1 returns (LEVERANDORCOUNT Integer) AS
> BEGIN
> SELECT COUNT(*)
> INTO :LEVERANDORCOUNT
> FROM LEVERANDOR
> SUSPEND;
> END ^!
> SET TERM ; ^!
>
> This is the error I get: "ISC ERROR MESSAGE: Dynamic SQL Error, SQL
> error code = -104, Token unknown - line 4, char 1 INTO".

SELECT COUNT(*)
FROM LEVERANDOR
INTO :LEVERANDORCOUNT;

Drop the SUSPEND unless you want to call it via a SELECT statement.

> Case 3:
>
> SET TERM ^! ;
> CREATE PROCEDURE TEST1 returns (LEVERANDORCOUNT Integer) AS
> BEGIN
> SELECT COUNT(*)
> FROM LEVERANDOR
> INTO :LEVERANDORCOUNT
> SUSPEND;
> END ^!
> SET TERM ; ^!
>
> This is the error I get: "ISC ERROR MESSAGE: Dynamic SQL Error, SQL
> error code = -104, Token unknown - line 7, char 3 SUSPEND".

Put a ";" after the INTO :var to end the SELECT statement.

> I am using Firebird Workbench v.1.4.1 (trial version) and Firebird
> v.1.0.0. I could upgrade to Firebird v.1.0.2 (or Alpha5), but there was
> another problem with that.
>
> I'm a bit stuck, and any help will be appreciated :)

With regards,

Martijn Tonies
InterBase Workbench - the developer tool for InterBase & Firebird
Firebird Workbench - the developer tool for Firebird
Upscene Productions
http://www.upscene.com

"This is an object-oriented system.
If we change anything, the users object."