CODEWITHSUNDEEP
stored-proceduresfirebird
Marfix
Marfix

Reputation: 1

Firebird Stored Procedure Token Unknown -end

I am very new in writing stored procedures in Firebird and I wrote whatever I see in internet but I am getting Token unknown error. Here is my very simple code

CREATE or ALTER PROCEDURE TRIAL
 (
 IN_ID DCOUNTER
 )
  AS

begin
    SELECT * FROM table1
end

And the error is:

Dynamic SQL Error
SQL error code = -104
Token unknown - line 13, column 1
end

How can I fix this? I tried adding and deleting some semicolons, ends, begins.

Upvotes: 0

Views: 452

Answers (2)

Mark Rotteveel
Mark Rotteveel

Reputation: 108941

There are at least two problems with your code (as also mentioned by user13964273):

  1. Statements in Firebird PSQL (its procedural SQL dialect) need to be terminated with a semicolon.

  2. In PSQL, you cannot simply select, you need to select into variables or output parameters so you can use those in your stored procedure and/or return them to the caller of the procedure. See the INTO section of the SELECT documentation.

In addition, you also have the following things to consider

  1. A select statement in PSQL can produce at most one row (a "singleton select"), while I think it's likely your statement will produce multiple rows. You will probably need to use for select instead to iterate over the result of the query.

  2. If you need to return rows to the caller, you need to add a RETURNS clause with the desired columns (output parameters) and populate them with INTO or calculations in your procedural code.

  3. If you want to return multiple rows, you'll need to add a SUSPEND statement when a row needs to be returned to the called. This makes the procedure selectable instead of executable.

  4. Depending on how you execute the stored procedure DDL, you may need to configure your query tool to switch statement terminators (NOTE: this link is specific to Firebird ISQL, but third-party Firebird tools have similar options, sometimes even the same).

And as user13964273 said, it might be helpful to look at the examples in the PROCEDURE section of the DDL chapter and the Procedural SQL (PSQL) Statements chapter.

Upvotes: 2

user13964273
user13964273

Reputation: 1211

Your procedure has two syntax errors:

  1. Missing semicolon.
  2. Missing INTO clause of SELECT query which is mandatory in PSQL.

Firebird Language Reference has quite comprehensive examples: https://firebirdsql.org/en/firebird-rdbms/

Upvotes: 2

Related Questions