May 21, 2003 at 10:30 am
Hi all,
How can i pass muliple values to a stored procedure?
ex. i want to be able to do something like the follwing except through a stored procedure:
update employee_tbl set fired=1 where empid=1 or empid=2 ... or empid=i
I will never know exactly how many ids (in the example above) i will have.
Can i pass an array of size determined at runtime? can i pass an array at all?
any suggestions are welcome
May 21, 2003 at 11:25 am
Options:
1) You can pass a delimited string as a single and split the string in the procedure, building a dynamic SQL statement and then executing.
2) Or you could create a temp table and insert each possible value one at a time, then JOIN on the temp table to achieve the filter.
3) Or you could handle this type of logic in your middle tier, building a recordset or dataset or array structure in your business object.
These are all valid options. Just depends where you focus your energies -- efficiency, performance, code maintenance, ease of use, competency in middle tier, etc. It's up to you.
May 21, 2003 at 11:54 am
thanks. I went with the split string option.
works like a charm.
🙂
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply