Convert string to set

Hi,


is there any possibility to use a STRING as SET in Firebird SP?

here's an example:


bill_list='1,2,5,11,27'

select * from bills where id in (:bill_list);

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.

hi,

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?