1

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.

|improve this question
  • 2
    If you only want the procedure to portion one chunk of work per parameter, sounds to me like you need to change it around. Put each unit of work in its own procedure. Then instead of all kinds of conditional logic in procedure A, and calling it over and over again with different parameters, your controller just calls each procedure in turn. If you have cases where you don’t need that level of control, just create a wrapper procedure that just calls them all. Kind of subjective but I’d rather manage and tune a bunch of independent procedures instead of one over-complicated behemoth. – Aaron Bertrand♦ Feb 1 at 14:42
  • Some information that might add some clarity as to the reason behind this. I am not in control or the author of the dowork procedure, hence I cannot edit it. I know its parameters will not change. I just want to be able to call it in another procedure that I have passing on certain parameters. – WadeH Jun 5 at 15:45

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.