How to send a list of numbers as a parameter to stored procedure?

If you wish to pass a variable (unlimited) number of such values (integers, double, floats) it is best to use fill in a temporary table and use that. Temporary tables are available from Firebird 2.1 onward. If you need to use a lower version of Firebird, you can send the list of numbers as a string:

execute procedure sp1('1,2,3,4,5,9,11,23');

The stored procedure would accept this in some varchar(32000) variable, so there is an upper limit for a number of values you can use. Please note that this number might be lower than 32000 for character sets that use more than one byte per character. The number of values you can use is limited as some percentage of the string goes to commas, and you'll probably use 3 or 4 digit numbers. So, you can expect to be able to supply about 6000-8000 values in a typical application.

Once you get the values, you have two options:

1. Use string parsing functions to extract values from string. To get integer from string, you can use simple CAST, with optional exception handling if you wish to skip bad values. Here's an example when -1 used instead of invalid values:

declare variable i integer;
declare variable s varchar(32);
...
BEGIN
i = CAST(S as integer);
WHEN ANY DO i = -1;
END

2. If your query allows, you can supply values directly to the IN clause of a query using EXECUTE STATEMENT (Firebird 1.5 or higher). Please note that there is a limit of 1499 values per IN clause, so make sure you split your string into multiple IN clauses if this happens:

stmt = '...some SQL statement...'||
'WHERE something IN (first 1500 values) OR something IN (next 1500 values) ...etc.';
EXECUTE STATEMENT :stmt;

If you want to use those in a SELECT, you need to use SUSPEND, just like wiht regular code:

FOR EXECUTE STATEMENT :stmt INTO :output_var1, :output_var2, :output_varN
DO SUSPEND;