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;


Do you find this FAQ incorrect or incomplete? Please e-mail us what needs to be changed. To ensure quality, each change is checked by our editors (and often tested on live Firebird databases), before it enters the main FAQ database. If you desire so, the changes will be credited to your name. To learn more, visit our add content page.

If you are a commercial tool maker and your tool features a great way to handle the issue written about in this FAQ, please check out our advertisement page.



All contents are copyright © 2007-2017 FirebirdFAQ.org unless otherwise stated in the text.


Links   Firebird   News   FlameRobin   Home Inventory powered by FB  
Add content   Advertise   About  

Categories
 Newbies
 SQL
 Installation and setup
 Backup and restore
 Performance
 Security
 Connectivity and API
 HOWTOs
 Errors and error codes
 Miscellaneous