Re: Stored Procedure: Default Values For Parameters

Ryan Hunt wrote:

Thanks for the suggestions. I hadn't considered the overloading option.
However, in this case it won't work. I am building some custom apps against
ERP tables that are sometimes 100+ columns wide. I had planned on building
Insert/Update procedures for each table with default values for every column
so that when I was ready to insert records, I could only pass relevant
columns within the calling stored procedure instead of replicating the same
verbose INSERT INTO TABLE (columns list 100+ wide) VALUES (100+ wide)
statement in multiple procedures where most of the columns would not be
relevant.

One option is a single procedure with all the parameters, but also pass an initial parameter of N bytes long (N being the number of fields) which contains a map of all the fields you wish to update. Pass a '1' if the field is to be passed in from the procedure, '0' if not. This would also work on updates; only the fields to be updated would need to be passed in.

You could also have one flag field per user field, but that would double the number of parameters to the procedure.

Joe