Design advice for stored procedure with high number of parameters.
this is my very first post in dbforums, I'd glad to read about your advice on this problem...
Target DBMS: DB2.
I've to develop a number of stored procedures that will work on tables with an high number of columns. So, if I will need to perform an INSERT or an UPDATE with these stored procs, I'll have to pass all values in stored proc's parameters.
Now I think that this approach may be confusing, and after a while may turn into a very mess. So I thought that I may introduce many "parameter tables" as needed: each param table should mirror the actual "target" table, plus at least one column RequestID to be passed as the only required parameter: using this RequestID, each stored may read actual values and threat it appropriately.
Filling these "parameter tables" with values it's quite trivial for the caller program.
For example, if I have a CUSTOMER table defined with a CustomerID, a CustomerDescription, a CustomerAddress and so on I'll have to create a CUSTOMER_P table with RequestID,CustomerID,CustomerDesciption,CustomerAd dress and so on columns.
What do you think about this approach ? I think that using it should be simpler than using structured data types; moreover, I may be able to use it even to return to the caller program cursors which record format may be hardly expressed as a join on different tables. In the example above, I may want to return to the caller an TotalOrderedAmount which may be calculated over single customer's orders by another stored procedure.
Thank you for your time and I hope to read your answers as soon as possible.