November 24th, 2011, 12:55 AM
Convert string to set
is there any possibility to use a STRING as SET in Firebird SP?
here's an example:
select * from bills where id in (:bill_list);
November 24th, 2011, 02:37 AM
One solution is to use EXECUTE STATEMENT
EXECUTE STATEMENT 'select * from bills where id in (' ||:bill_list ||')';
Another and more complicated is to use global temporary tables. You can insert in one temporary table the ID's you want and execute INNER JOIN with this table:
select bills.* from bills inner join tmp_id on tmp_id.id = bills.id
Just make sure that you've deleted the old values before inserting the new ones if you are going to execute this select many times with different set of IDs in one transaction.
+ With this method you can filter SELECTs with more than 255 values, because IN is limited to accept up to 255 values.
- Firebird creates files on the disk with the data for the temporal table and this can slow the execution of the queries. But you can make a RAM drive for Firebird to use it for its temp files. This also helps for other things that Firebird do if you have enough free RAM.
Last edited by mIRCata; November 24th, 2011 at 02:51 AM.
November 24th, 2011, 04:06 AM
thanks a lot for your reply. This global temporary thing aroused my interest.
How can i create global temporary tables? Can i create them inside of an SP? or in the declaration part of an SP?
Originally Posted by mIRCata