Ignoring Null (optional) Variables in Stored Procedure

Question

I am looking for what is hopefully something easily achievable without a ton of coding.

I have a stored procedure that does some work and has about 20 optional parameters:

CREATE PROCEDURE dowork 
                 @param1_1=NULL, 
                 @param1_2=NULL
                 .....@Param1_30=NULL

I am implementing another procedure, that will call that one, and ideally, I want to offer all the parameters, but only use those that have been set:

CREATE PROCEDURE CallWork 
                 @param1=NULL, 
                 @param2=NULL
                 .....@Param30=NULL
AS BEGIN
--Do some stuff
EXEC dowork 
     @param1_1=@param1, 
     @param1_2=@param2
     .....@Param1_30=@Param30

Will the above example work as is, or do I need to create a variable of some sort to hold all the 'set' parameters for example?

The parameters are static. I have used number 1 to 30 as an example, but they are all static. Examples: @StartTime, @EndTime, @Type, @Location etc.

Currently dowork, processes a batches based on the input from the parameters. For certain workloads, I need it to process each batch, one at a time, instead of all at the same time.

By creating an abstracting procedure, I am planning to create a table variable to hold a row for each batch, and then pass them through one by one, allowing more control over each batch that we require in certain circumstances. This will then leave the original procedure to function as it did when required for mass batch jobs.

Answer 1

If you set the value of a given parameter to it's default value (when it has one), you'd get the same functional equivalent as omitting it. Therefore, I'd suggest doing something like the following:

EXEC dowork 
    @param1 = ISNULL(@param1, SELECT default_value FROM sys.parameters p WHERE object_Id = OBJECT_ID('dowork') AND name = '@param1' AND has_default_value = 1),
    @param2 = ISNULL(@param2, SELECT default_value FROM sys.parameters p WHERE object_Id = OBJECT_ID('dowork') AND name = '@param2' AND has_default_value = 1),
    ...@param30 = ISNULL(@param30, SELECT default_value FROM sys.parameters p WHERE object_Id = OBJECT_ID('dowork') AND name = '@param30' AND has_default_value = 1)

The only flaw being values that do not have a default value, which will result in passing a NULL vs causing the error that would occur when omitted. If that is a concern in your particular case, I would do some checking before hand and throw an error.

The only other alternative I could suggest would be dynamic SQL.