SUPER MAIN

[sagn]

Hi,

My boss has decided he wants a SUPER MAIN procedure form
which everything we have can be run.
One complication is that different SP's require different
input....

I think we would be better served by a few MINI MAINS
that run certain jobs that have similar requirements but
I think my boss really wants ONE main.

This is what I have comeup with:

User starts SUPER MAIN and enters

list of procedures to be done and a list of variables
to be sent to those procedures

I parse out the procedures and the variables
and place them in a table that is built based on
the largest number of input variables I have.

for example:
SUPERMAIN 'A_SP:B_SP','A1,A2:B1,B2,B3'

The table will be

A_SP A1 A2
B_SP B1 B2 B3

then I do an

execute sp_executesql N'exec @err=a_sp a1,a2',n'@err int out',@err output

and the same when it comes time to do B_sp.

If an error is found in The SP_A and SP_B they must fill in
a table and return the err code.

The SUPERMAIN reads from the error table and decides what to do then

My question is this....

Is there any other way to do what I have done here?
Something more concise or elegant?
(This actually works by the way)

(I have other questions about this project that will be coming
but I do not want to overwhelm)

Thanks

ds

 

[ColinM]

Sounds like you need a front-end application to select which procedure to run and enter parameters, rather than trying to do everything in SQL.

Stored Procedure get their speed advantage from the execution plan, this works best if the stored procedure is simple and concentrates on one job. With a lot of branching logic the query optimizer will be less efficient.