Is there any way to create a local table variable inside a Firebird stored proc?

Question

In MS SQL Server, you can declare local variables of any primitive type, or of a table type. This table is a normal table that you can run SELECT, INSERT, UPDATE and DELETE on, just like any other table, except that it's a local variable, not a part of the database itself.

I'm trying to do the same thing in Firebird, but it doesn't seem to like the syntax.

declare variable value int; --works fine
declare variable values table (value int); --Error: "Token unknown (table)"

Is there any way to do this? (And before anyone says "use a selectable stored procedure," that won't work. I need something I can dynamically run INSERT and SELECT on.)

Answer 1

Firebird doesn't support table variables the same way SQL Server does.

The close thing you have at your disposal is Global Temporary Tables (Requires Firebird 2.1 or greater)

(v.2.1) Global temporary tables (GTTs) are tables that are stored in the system catalogue with permanent metadata, but with temporary data. Data from different connections (or transactions, depending on the scope) are isolated from each other, but the metadata of the GTT are shared among all connections and transactions.

There are two kinds of GTT:

with data that persists for the lifetime of connection in which the specified GTT was referenced; and

with data that persists only for the lifetime of the referencing transaction.

You have to create the GTT beforehand.

CREATE GLOBAL TEMPORARY TABLE
  ...
  [ON COMMIT <DELETE | PRESERVE> ROWS]