Is it possible to define table type input parameter of stored procedure in Firebird?

Question

I need to define stored procedure with table type input parameter, so for example, if I am calling stored procedure from C# and pass datatable as argument to stored procedure, automatically will be mapped to the columns and fields?

I read about creating a global temporary table, but how to use as input parameter in Firebird? I don't know if it is even possible.

Mark Rotteveel · Answer 1 · 2021-01-13T17:02:55.383

Firebird stored procedures only accept scalar values, so you cannot pass a table type input parameter. The workaround is to define a global temporary table (GTT) as a companion to the stored procedure, and fill that global temporary table before calling the stored procedure. The stored procedure then reads from the global temporary table.

To be clear, global temporary tables are persistent definitions (you create them once, and they can then be reused by all connections and users (assuming the user has been given the right privileges)), while the data in a GTT is visible only to the current transaction (on commit delete rows; the default), or the current connection (on commit preserve rows). They are not like (local) temporary tables like in SQL Server.

In other words, the definition of this type of temporary table is global and persistent, but the data is specific to a transaction or connection.

So, what you do is define a global temporary table:

create global temporary table input_sp_example (
  col1 integer,
  col2 varchar(100)
) on commit delete rows

and an example stored procedure

create procedure sp_example
  returns (result1 integer, result2 varchar(8192))
as
declare col1_value integer;
declare col2_value varchar(100);
begin
  for select col1, col2 from input_sp_example order by col1, col2 into col1_value, col2_value do
  begin
    if (col1_value is distinct from result1) then
    begin
      result1 = col1_value;
      result2 = col2_value;
    end
    else
    begin
      result2 = result2 || col2_value;
    end
    suspend;
  end
end

You can then call it like:

insert into input_sp_example (col1, col2) values (1, 'a');
insert into input_sp_example (col1, col2) values (1, 'b');
insert into input_sp_example (col1, col2) values (1, 'c');
insert into input_sp_example (col1, col2) values (2, 'a');

select * from sp_example;

This basic example will produce a result of

RESULT1	RESULT2
1	a
1	ab
1	abc
2	a

If you execute the stored procedure once in a transaction, you don't need to do anything else (the data is automatically deleted on commit). If there is a chance that you need to execute the stored procedure multiple times in a single transaction, you will need to empty the table before populating with fresh data using:

delete from input_sp_example

Is it possible to define table type input parameter of stored procedure in Firebird?

1 Answers1