Subject Re: [firebird-support] Stored procedure with array parameters
Author Svein Erling Tysvær
Hi Adam & Robert!

Adam wrote:
> Robert martin wrote:
>>Should I
>>A) Have 10 params (allowing up to 10 Pks) and set unused to null
> I think your first option would get messy, with a lot of is not null
> tests going on.

In what sense is

SELECT <whatever>
WHERE MYPK IN (:P1, :P2, :P3, :P4, :P5, :P6, :P7, :P8, :P9, :P10)

messy? I agree that similar queries involving a random number of fields quickly make things messy, but when the only uncertain thing is the number of PKs of interest and that number never exceeds 10, I think the above solution is good (the plan produced seems OK, and trying it on a table with about 1 million rows gives instant results on Firebird 1.5.4). I didn't write any stored procedure to test, but I don't see how that would matter.

Seans solution with EXECUTE STATEMENT may be a valid option, but take note of the warnings in the release notes (amongst others, that user privileges are used rather than procedure privileges - at least in Firebird 1.5.4).


[Non-text portions of this message have been removed]