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

Question

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

I am trying to do the same in Firebird, but it does not seem like 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 selective stored procedure”, this will not work. I need something that I can dynamically run INSERT and SELECT on.)

Answer 1

Firebird does not support table variables in the same way that SQL Server does.

A close thing you have at your disposal is Global Temporary Tables (requires Firebird 2.1 or higher)

(v.2.1) Global temporary tables (GTT) are tables that are stored in the system directory with constant metadata but with temporary data. Data from different connections (or transactions, depending on the scope) is isolated from each other, but GTT metadata is shared between all connections and transactions.

There are two types of GTT:

with data that is stored for the lifetime of the connection in which the specified GTT is indicated; and

with data that is stored only for the lifetime of the transaction referencing it.

You need to create a GTT in advance.

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