| Subject | Re: [firebird-support] Stored procedure with array parameters |
|---|---|
| Author | Svein Erling Tysvær |
| Post date | 2007-07-24T07:59:59Z |
Hi Adam & Robert!
Adam wrote:
SELECT <whatever>
FROM MYTABLE
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).
Set
[Non-text portions of this message have been removed]
Adam wrote:
> Robert martin wrote:In what sense is
>>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.
SELECT <whatever>
FROM MYTABLE
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).
Set
[Non-text portions of this message have been removed]